How to partition your data set in MS SQL Server

When you have a huge data collection/set that can potentially take a bit of time to retrieve, it's better to retrieve the data as a partitioned result when you are displaying the sitemapindex results.

Why pull down 1,000,000 products over the wire when all we need to display in the sitemapindex are the first record for each partition/page?

So lets get smart with getting our partioned data..

CREATE PROCEDURE [dbo].[Products_SitemapPartitioned]
(
    @pageSize INTEGER - Eg. 25,0000 records per 'page'.
)
AS
BEGIN
	
    SET NOCOUNT ON;

    SELECT ProductId, LastModified
    FROM (
        SELECT ProductId, LastModified,
        ROW_NUMBER() OVER (ORDER BY LastModified DESC) AS RowNumber
        FROM [dbo].[Products] 
        WHERE StatusType = 1 -- Is for sale
        AND a.IsVisible = 1
    ) x
    WHERE x.RowNumber % @pageSize = 1  -- NOTE: yes, that's MOD 1, not MOD zero.
                                       --       We're after all rows that have 
                                       --       the remainder 1, by our page size. 
END

And the sql result will return data for the following rows.. (given a page size of 10 ..)

• 1st row (start of the 1st partition)
• 11th row (start of the 2nd partition)
• 21st row (start of the 3rd partition) .. etc..

Now use this data result to pass to your Sitemap Index.