Category Archive: SQL

Apr 04

Window Functions! RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(n)

Microsoft introduced ranking functions (also known as window functions) in SQL Server 2005. What are window functions exactly? First, they are awesome. Second, they are functions that can be applied to a group of rows as specified by the window descriptor (I. Ben-Gan, 2011). Itzik Ben-Gan has a great series on how to use these …

Continue reading »

Jan 18

Generating Consecutive Numbers Within a Given Range

A couple of months ago I was working on a project where we were revamping an existing system.  One piece of the system was a call to a stored procedure that generated a number in sequence and assigned it to a user that was accessing the application. It was very similar to taking a number …

Continue reading »

Jan 10

Capturing Data Modifications – The OUTPUT Clause

There are several reasons you may want to capture data that has been inserted, updated, deleted, or merged (2008). The OUTPUT clause that SQL Server provides is extremely helpful for these situations. I have been tasked on several occasions to “merge” data from a staging table to a production table. Whether or not it is …

Continue reading »

Dec 21

Assuming order of data without ORDER BY

Why are my results out of order? Many times I have seen posts in forums asking about why their data is not being returned in the order of the clustered index. Similar questions have been brought up questioning the order of data from a view when the view includes an ORDER BY clause (which it …

Continue reading »

» Newer posts