Hi all,

again, the source code listing does not show up well in IE. The lines are wrapped at places where they are not supposed to be.

The code shows up correctly in Mozilla.

If you are using IE, i would suggst to click on the link 'code' and read the source code from there.

Hi noggin,

I had found 3 points that I must add to the above article after playing with a few real-life assignments.

1. Find the total number of records affected by the current filters
2. Support for sorting ascending and descending (usually, as a result of clicking on the header of a grid/table at the front end)
3. Some ASP.NET code which shows how this code would work with an ASP.NET grid control (includes how to display custom page numbers at the front end  etc..)

I have a follow-up article coming up, which shows some sample code which may be helpful to clear those 3 points.

thanks

Jacob

Jacob,

I read your article what I found very useful.
Now I have a question related to joins. Please consider this two examples:

WITH EmployeesOrders AS (
 SELECT 
   Employees.EmployeeID,
   Employees.FirstName,
   Employees.LastName,
   Orders.OrderDate,
   Orders.ShippedDate,
   ROW_NUMBER()OVER (ORDER BY LastName) AS RecNum
 FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)

SELECT * FROM EmployeesOrders
WHERE RecNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
ORDER BY RecNum

and

WITH EmployeesOrders AS (
 SELECT 
   Employees.EmployeeID,
   ROW_NUMBER()OVER (ORDER BY LastName) AS RecNum
 FROM Employees)

SELECT
  Employees.EmployeeID,
  Employees.FirstName,
  Employees.LastName,
  Orders.OrderDate,
  Orders.ShippedDate

FROM EmployeesOrders
  INNER JOIN Employees ON EmployeesOrders.EmployeeID=Employees.EmployeeID
  INNER JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE RecNum BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
ORDER BY RecNum

In the first query all the join and select stuff is done in the CTE (joining maybe 100000 records from a foreign table)
but in the second query the CTE returns only the ID needed to perform the selecting and the joining.
In this case the joins will have to deal only with the number of records given from the @Pagesize (maybe 15 or 20) and I think that this would be much more efficient in a 100000 records scenario.

Having to implement this in my current project which option should I choose ?

Many thanks

Franco Pizzinini

Hi

I quite like the exposition of the rownumber and over stuff.  Interesting.

What I would ask is whether the performance issues relating to the and(x is null or x = y) that were experienced in SQL2000 (ie knocking it down to a scan) have been resolved on 2005?

Cheers

;WITH emp AS (SELECT TOP (@PageNumber * @PageSize)
/* retrieve only a limited result set 
if you need records 31 -> 60, you retrieve in the first step only 60 rows
*/
	CASE 
		WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) 
		WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) 
		WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) 
		-- In all other cases, assume that @SortOrder = 'LastName'
		ELSE ROW_NUMBER()OVER (ORDER BY LastName) 
	END AS RecID,
	LastName, 
	FirstName,	
	Title,
	HireDate,
	City,
	Country,
	PostalCode
FROM employees
WHERE 
	(@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
	AND
	(@Title IS NULL OR Title LIKE '%' + @Title + '%')
	AND
	(@City IS NULL OR City LIKE '%' + @City + '%')

/*specify order */
ORDER BY 
	CASE 
		WHEN @SortOrder = 'Title' THEN Title
		WHEN @SortOrder = 'HireDate' THEN HireDate
		WHEN @SortOrder = 'City' THEN City 
		ELSE LastName
	END
)
SELECT 
	RecID, -- This column is only for debugging/testing purpose.
	LastName,
	Title,
	HireDate,
	City
FROM emp
/*
	you need only the last @PageSize rows from your result set
*/
WHERE RecID > (@PageNumber - 1) * @PageSize)
ORDER BY RecID