I’ve always felt it a shame that SQL Server lacks the limit clause like that in MySQL. The limit clause lets you decide which rows to get back and you can choose to have say rows 51 to 100. OK in SQL Server you can use top to choose the first 100 and then ignore 50 but it never felt that smart and when you want rows 1001 to 1051 it’s positively inefficient.
Using the AdventureWorksLT2008R2 database and with a little help from ROW_NUMBER I have this.
SELECT custdet.CustomerID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,CompanyName
,SalesPerson
,EmailAddress
,Phone
FROM (SELECT ROW_NUMBER() OVER (ORDER BY CompanyName) rownum
,CustomerID
FROM SalesLT.Customer) custlist
CROSS APPLY (SELECT CustomerID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,CompanyName
,SalesPerson
,EmailAddress
,Phone
FROM SalesLT.Customer
WHERE CustomerID = custlist.CustomerID) custdet
WHERE rownum BETWEEN 51 AND 100
I like this approach with derived tables and the CROSS APPLY because the right hand derived table query will only have 50 rows queried, which can be important if there is a NTEXT or NVARCHAR(MAX) column included with loads of data therein.
However the reason I’m wanting this is to produce paginated lists so it would be handy to know how many rows would be returned overall, so I can decide how many pages there will be. So the next step was to get this without running two queries (a count and the one above).
Using a common table expression and a union is the answer I came up with.
WITH custlist AS (SELECT ROW_NUMBER() OVER (ORDER BY CompanyName) rownum
,CustomerID
FROM SalesLT.Customer)
SELECT NULL, custdet.CustomerID
,NameStyle
,Title
,FirstName
,MiddleName
,LastName
,Suffix
,CompanyName
,SalesPerson
,EmailAddress
,Phone
,PasswordHash
,PasswordSalt
,rowguid
,ModifiedDate
FROM custlist
INNER JOIN SalesLT.Customer custdet
ON custdet.CustomerID = custlist.CustomerID
WHERE rownum BETWEEN 51 AND 100
UNION ALL SELECT MAX(rownum)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM custlist
So I still only pull 50 rows from the customer details, only hit the database once and get the number of rows returned (which will let me workout how many pages there will be). Using UNION ALL removes a SELECT DISTINCT from the plan but makes little difference to performance.
Tags: common table expression, CROSS APPLY, derived table, ROW_NUMBER
