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.
SyntaxThe 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