NTILE() distributes the result set into specified number of ordered partitions. For each row in result set NTILE() will returns a group number to which the row is associated.

This is very useful while distributing the result set into multiple groups in case you need to distribute the results into multiple tables.

For example,

-- © 2011 – Vishal (http://SqlAndMe.com)
 
SELECT      Products.ProductID, Products.Name, Products.Price,
            NTILE(3) OVER (ORDER BY Products.Price) AS [Partition]
FROM
(
      SELECT      804 AS [ProductID],
                  'HL Fork' AS [Name], 229.49 AS [Price] UNION ALL
      SELECT      871, 'Mountain Bottle Cage',  9.99     UNION ALL
      SELECT      845, 'Mountain Pump',         24.99    UNION ALL
      SELECT      921, 'Mountain Tire Tube',    4.99     UNION ALL
      SELECT      980, 'Mountain-400-W Silver', 769.49   UNION ALL
      SELECT      988, 'Mountain-500 Silver',   564.99   UNION ALL
      SELECT      793, 'Road-250 Black',        2443.35  UNION ALL
      SELECT      762, 'Road-650 Red',          782.99   UNION ALL
      SELECT      923, 'Touring Tire Tube',     4.99     UNION ALL
      SELECT      966, 'Touring-1000 Blue',     2384.07
) Products

The above query will distribute the result set in three (3) different groups by distributing the rows. In this case the rows cannot be distributed evenly, so it will be divided as 4 + 3 + 3. If the number of rows were 11, it will be divided as 4 + 4 + 3.

Result Set:

ProductID     Name                    Price         Partition
———–          ———————                 ——-           ———–
921           Mountain Tire Tube      4.99          1
923           Touring Tire Tube       4.99          1
871           Mountain Bottle Cage    9.99          1
845           Mountain Pump           24.99         1
804           HL Fork                 229.49        2
988           Mountain-500 Silver     564.99        2
980           Mountain-400-W Silver   769.49        2
762           Road-650 Red            782.99        3
966           Touring-1000 Blue       2384.07       3
793           Road-250 Black          2443.35       3
 
(10 row(s) affected)

Using <PARTITION BY> with NTILE():

PARTITION BY clause adds one more level of partitioning to the result set. In the above example, no PARTITION BY clause is specified, hence the rows are distributed as:

10 Rows –>  Partition 1 = 4 Rows,
            Partition 2 = 3 Rows,
            Partition 3 = 3 Rows

Now, let’s say we add another column to Result Set – Product Category. Our sample data will be:

ProductID   Name                  Category    Price
———–        ———————               ———–        ——–
804         HL Fork               Accessories 229.49
871         Mountain Bottle Cage  Accessories 9.99
845         Mountain Pump         Parts       24.99
921         Mountain Tire Tube    Parts       4.99
980         Mountain-400-W Silver Accessories 769.49
988         Mountain-500 Silver   Parts       564.99
793         Road-250 Black        Accessories 2443.35
762         Road-650 Red          Parts       782.99
923         Touring Tire Tube     Accessories 4.99
966         Touring-1000 Blue     Parts       2384.07

Now to divide the result set based on Category we can use PARTITION BY clause, the partitions will be as follows:

10 Rows –>  Group 1, Category = Accessories = 5 Rows -> Partition 1 = 2 Rows,
                                                        Partition 2 = 2 Rows,
                                                        Partition 3 = 1 Row,
            Group 2, Category = Parts = 5 Rows       -> Partition 1 = 2 Rows,
                                                        Partition 2 = 2 Rows,
                                                        Partition 3 = 1 Row

SELECT      Products.ProductID, Products.Name,
            Products.Category, Products.Price,
            NTILE(3) OVER
            (PARTITION BY Products.Category ORDER BY Products.Price)
            AS [Partition]
FROM
(
      SELECT 804 AS [ProductID],
      'HL Fork' AS [Name], 'Accessories' AS [Category],
      229.49 AS [Price] UNION ALL
      SELECT 871, 'Mountain Bottle Cage', 'Accessories', 9.99    UNION ALL
      SELECT 845, 'Mountain Pump',        'Parts',       24.99   UNION ALL
      SELECT 921, 'Mountain Tire Tube',   'Parts',       4.99    UNION ALL
      SELECT 980, 'Mountain-400-W Silver','Accessories', 769.49  UNION ALL
      SELECT 988, 'Mountain-500 Silver',  'Parts',       564.99  UNION ALL
      SELECT 793, 'Road-250 Black',       'Accessories', 2443.35 UNION ALL
      SELECT 762, 'Road-650 Red',         'Parts',       782.99  UNION ALL
      SELECT 923, 'Touring Tire Tube',    'Accessories', 4.99    UNION ALL
      SELECT 966, 'Touring-1000 Blue',    'Parts',       2384.07
) Products

Result Set:

ProductID   Name                  Category    Price        Partition
———–        ———————               ———–        ————         ————–
923         Touring Tire Tube     Accessories 4.99         1
871         Mountain Bottle Cage  Accessories 9.99         1
804         HL Fork               Accessories 229.49       2
980         Mountain-400-W Silver Accessories 769.49       2
793         Road-250 Black        Accessories 2443.35      3
921         Mountain Tire Tube    Parts       4.99         1
845         Mountain Pump         Parts       24.99        1
988         Mountain-500 Silver   Parts       564.99       2
762         Road-650 Red          Parts       782.99       2
966         Touring-1000 Blue     Parts       2384.07      3
 
(10 row(s) affected)

Hope This Helps! Cheers!