SQL Server Window Functions Cheat Sheet (OVER-clause)

It all started with SQL Server 2005 which introduced the OVER-clause. It was (back then) possible to add a row number to a dataset by using something similair to:
ROW_NUMBER() OVER()

You were also able to use the Ranking functions, such as ROW_NUMBER, RANK, DENSE_RANK and NTILE.

Now with SQL Server 2012, Microsoft introduced a few more so called ‘Window functions’ (aggregate and analytic):

SQL Server Window Function OVER clause


Here’s an overview:
sql-server-2012-window-functions-over-clause-cheat-sheet-by-clint-huijbers


Download:
Download my cheat sheet here (in Word format)








Advertisements

SET DATEFORMAT, a thing to remember.

Today I faced with an issue that an older SQL 2000 server (source db) had different formatting settings (my guess is the OS’s regional settings) than my DWH server (target db) or my local machine. Googled a bit and found the ‘SET DATEFORMAT‘-statement:
SET DATEFORMAT { dym | dmy | mdy | myd | ydm | ymd }

An easy method to check which format setting the server is applying/using, is to execute this SQL query:
SELECT DatePart(Month, '11-7-2014') AS CurrentMonth


How it works

SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '07/11/14'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '14/11/07'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '14/07/11'
SELECT @datevar AS CurrentDate
GO

The results:

sql-set-dateformat-results








MDS: How to change the data type in an existing Entity (filled table)

Want to change the data type of an existing Entity’s attribute without reloading of re-creating that entity?

This is just a little trick you should remember, you’re unable to change the data type via the MDS web interface (GUI):

Unable to change the data type in MDS



However, this is possible in the MDS Excel Add-in! 🙂

MDS change attribute data type in excel unable via webinterface gui



Here’s how you could you could do it:

1) Open Excel, connect to MDS and retrieve the complete set of data from MDS for that specific Entity

2) Select a random cel in the column of the attribute of which you would like to change the attibute’s data type.

3) In the MDS-tab in Excel, click on ‘Attribute Properties‘ and simply change the data type into the type you want.
After that it will publish the update and give some kind of message that you need to update your subscription. Sorry my message was in Dutch so…but simply said you need to refresh/rec-create the View was made under ‘Integration Management‘ in MDS.

4) Go to ‘Integration Management‘ in MDS (web interface or GUI), click on ‘Create Views‘ and Edit that specific view. When in Edit-mode simply clicking on the Save-button will do the trick.

5) Verify in SSMS that that view now contains the changed data type (in the ‘MDS’ database on the SQL Server instance).

That’s it!
Now don’t forget that when you’re using SSIS to ETL the data…it requires a meta-data update 😉








How to create a view (with end-date) for an INSERT-ONLY SCD Type 2 table?

Well Albert Garcia Diaz wrote a great example for that.
He uses LEAD() combined with the OVER-clause to easily accomplish this:
“…LEAD provides access to a row at a given physical offset that follows the current row…”

More info about LEAD():
https://msdn.microsoft.com/en-us/library/hh213125.aspx


CREATE VIEW [hstg].[vw_Table_History]
AS
SELECT
ETL_EXECUTION_DT AS START_DATE,
ISNULL(LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT), '9999-12-31 00:00:00.0000000') AS END_DATE,
[CURRENT_RECORD] = CASE
WHEN LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT) IS NULL THEN 'Y'
ELSE 'N'
END,

[BusinessKey],
[Field1],
[Field2],

ETL_BATCH_DT AS ETL_ROW_START_DT,
S.ETL_BATCH_ROW_ID,
CONVERT (DATETIME, '99991231') AS ETL_ROW_END_DT,
ETL_ROW_HASH,
CONVERT (NVARCHAR (512), 'AdvWorks') AS ETL_ROW_SOURCE,
ETL_CDC_OPERATION,
ETL_EXECUTION_DT
FROM [hstg].[Table] S








SSIS BufferTempStoragePath: The buffer manager cannot write 8 bytes to file

Hi there!

This morning I received this nice little error during ETL:

"The buffer manager cannot write 8 bytes to file C:\....There was insufficient disk space or quota."

ssis-BufferTempStoragePath-buffer-manager-cannot-write-8-bytes-to-file-error

First thing Google told me was:

Check if you are experiencing any memory problems and errors ..


Okay…you’re right again Google, we do have a memory shortage (for heavy SSIS).
Then I found this blog post by BI Monkey explaining a few things:
http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath/

Two innocuous properties on every Data Flow in SSIS. You’ve likely never noticed them. But they can easily kill your jobs if not set properly.

They tell SSIS where to write to on disk under given circumstances. BufferTempStoragePath is where on disk it will write buffer contents in the event that there is no available memory. BLOBTempStoragePath is where on disk it will write any BLOB data in the buffer.


One of the tips from MSSQLTips about this:
http://www.mssqltips.com/sqlservertip/1867/sql-server-integration-services-ssis-performance-best-practices/

Best Practice #8 – BufferTempStoragePath and BLOBTempStoragePath

If there is a lack of memory resource i.e. Windows triggers a low memory notification event, memory overflow or memory pressure, the incoming records, except BLOBs, will be spooled to the file system by SSIS. The file system location is set by the BufferTempStoragePath of the data flow task. By default its value is blank, in that case the location will be based on the of value of the TEMP/TMP system variable.

Likewise SSIS may choose to write the BLOB data to the file system before sending it to the destination because BLOB data is typically large and cannot be stored in the SSIS buffer. Once again the file system location for the spooling BLOB data is set by the BLOBTempStoragePath property of the data flow task. By default its value is blank. In that case the location will be the value of the TEMP/TMP system variable. As I said, if you don’t specify the values for these properties, the values of TEMP and TMP system variables will be considered as locations for spooling. The same information is recorded in the log if you enable logging of the PipelineInitialization event of the data flow task as shown below.

User:PipelineInitialization,ARSHADALI-LAP,FAREAST\arali,Data Flow Task,{C80814F8-51A4-4149-8141-D840C9A81EE7},{D1496B27-9FC7-4760-821E-80285C33E74D},10/11/2009 1:38:10 AM,10/11/2009 1:38:10 AM,0,0x,No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.

So far so good. What is important here is to change this default values of the BufferTempStoragePath/BLOBTempStoragePath properties and specify locations where the user executing the package (if the package is being executed by SQL Server Job, then SQL Server Agent service account) has access to these locations. Preferably both locations should refer to separate fast drives (with separate spindles) to maximize I/O throughput and improve performance.


In the Dataflow properties:
ssis-BufferTempStoragePath-default-dataflow-properties


He advices to setup two package variables in every (template) SSIS package to configure these during deployment (which makes sense) or at least change the default C-drive (which is in our case the smallest disk) to a different one.

Conclusion
Put this one on your SQL Server / SSIS checklist!
Overwrite the default paths for BLOBTempStoragePath and BufferTempStoragePath if your C-drive is too little or too slow (SSD perhaps?).







FORMAT() function in SQL Server 2012/2014

The FORMAT() function is one to really remember, it saves you a lot of time.
Here are a few examples:


SELECT
FORMAT(12345678,'n') AS n
,FORMAT(12345678,'n0') AS n0
,FORMAT(12345678,'n0','nl-NL') AS n0NL
,FORMAT(0.8856,'p') AS p
,FORMAT(0.8856,'p1') AS p1
,FORMAT(12345678,'c') AS c
,FORMAT(12345678,'c1') AS c1
,FORMAT(12345678,'c1','nl-NL') AS c1NL

DECLARE @d DATETIME = '10/01/2011'

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';



sql-server-2012-format-function-examples-with-regional-code



For more examples, please visit:
http://www.codeproject.com/Articles/610089/SQL-Servers-FORMAT-function








Activate your free Office 365 subscription (Product Key) from your Microsoft Action Pack Subscription (MAPS)

Here’s a little thing you just need to know or stumbled upon.
Once you’ve purchased MAPS, you’ve automatically been giving 5 Office 365 ‘seats’ (or User Licenses)!

How to activate these licenses:

1) Go to the Microsoft Partner Network website and log in:
https://www.microsoft.com/msppdd/


2) Instead of clicking on ‘Software Download / View Keys’, click on ‘Microsoft Online Services

MAPS-office-365-product-keys-Microsoft-Online-Services


3) Now click on ‘View‘ on the right:
MAPS-office-365-product-keys-Cloud-Download-View


4) Expand the ‘Microsoft Action Pack‘-section to reveil the Product Key:

MAPS-office-365-product-keys-obtain-product-key


5) Click on the link stated just a little bit lower or use this one:
“…To redeem these tokens visit http://go.microsoft.com/fwlink/?LinkId=392523…”


6) Enable the checkbox ‘I understand. I’m the right person to be doing this for my organization.‘ and click on ‘Sign in‘ (on the right) under ‘Already an Office 365 customer?‘:

MAPS-office-365-product-keys-activate-product-key



OR: Login to your Office 365 Admin Center and go to ‘BILLING‘-> ‘Subscriptions‘, select the active subscription and click on ‘Renew or add user licenses‘:

MAPS-office-365-product-keys-activate-product-key-in-admin


7) Verify that you’ve been giving 5 extra licenses thanks to the Product Key of MAPS. Next step is to cancel your current paid subscription and get a little bit of money back 😉 You may want to contact the Office 365 supportdesk for that of cancel it in the Office 365 Admin Center under ‘Subscriptions’.