SQL Server 2005/2008 what's new, part 6

SQL Saturday 53, takes place in 42 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

NTILE()

The NTILE function is the fourth of four windowing functions introduced in SQL Server 2005. NTILE takes a different approach to paritioning data. ROW_NUMBER, RANK and DENSE_RANK will generate variable sized buckets of data based on the partition key(s). NTILE attempts to split the data into equal, fixed size buckets. BOL has a comprehensive page comparing the ranking functions if you want a quick visual reference on their effects.

Syntax

The syntax for NTILE differs slightly from the other window functions. It's NTILE(@BUCKET_COUNT) OVER ([PARTITION BY _] ORDER BY _) , where @BUCKET_COUNT is a positive integer or bigint value. Using a fast number generator calendar, I added a call to NTILE() based on event date to introduce a rank column.

-- Example of using NTILE to split 30 numbers
-- into 10 and 4 equal sized buckets
SELECT
    GN.number
,   NTILE(10) OVER (ORDER BY GN.number) AS thirds
,   NTILE(4) OVER (ORDER BY GN.number) AS quartile
FROM 
    dbo.GenerateNumbers(30) GN

number	thirds	quartile
1	1	1
2	1	1
3	1	1
4	2	1
5	2	1
6	2	1
7	3	1
8	3	1
9	3	2
10	4	2
11	4	2
12	4	2
13	5	2
14	5	2
15	5	2
16	6	2
17	6	3
18	6	3
19	7	3
20	7	3
21	7	3
22	8	3
23	8	3
24	8	4
25	9	4
26	9	4
27	9	4
28	10	4
29	10	4
30	10	4

What to notice

The, backwardsly named, thirds column was evenly partitioned with the NTILE function. As expected, ten buckets were created and each hold 3 sets of numbers (3 * 10 = 30). Quartile column as expected was 4 buckets of 8, or is it 4 buckets of 7? 4 * 8 = 32; 4 * 7 = 28. Neither is the correct answer. In cases where the rows can't be broken out into even buckets, NTILE will fill from the top down so there is at most a 1 count difference between the first and last bucket.

Availability

SQL Server 2005+