Monday, 18 January 2010

TSQL : Using NTILE to divide a results set up.

NTILE is a ranking function which can be used to divide up results into groups of equal volume.

To demonstrate I use NTILE (4) to distribute the Adventureworks Contact table into 4 groups.
, [NameStyle]
, [Title]
, [FirstName]
, [MiddleName]
, [LastName]
, NTILE (4)  OVER ( ORDER BY [ContactID] )  As Process
FROM [AdventureWorks].[Person].[Contact]

In reality I'm using it to divide a job queue between different paths in a SSIS package, to achieve a level of parallel execution.
I'm sure there are better ways, which I'll blog when I find them.

No comments: