Oh man, working with M$ is hell. Yeah, they do very big things but they just forgot small but essential things.
M$ maybe the most stupid when not implementing paging functionality in T-SQL. Most of other DBMSs do that. Paging from server is a vital point, especially in web development. If you just do paging from client-side, you are wasting most of network traffics for unused records.
After searching thouroughly on Internet, I finally found out some ways to get arround this flaw. One uses dynamic SQL query to refine the query and add some ORDER BY + TOP clauses to get the result. Another way is to use a temporary table with an identity column used as a row number. I prefer the 2nd way. At least, it’s more secured.
alter procedure GetAuthors ( @orderby nvarchar(100), @recordsPerPage int, @pageNumber int ) AS -- the amount of rows actually needed declare @rowsNeeded int -- the position where we need to start retrieving records declare @startRecord int -- initialize @rowsNeeded set @rowsNeeded = (@pageNumber * @recordsPerPage) -- initalize @startRecord set @startRecord = ((@pageNumber-1) * @recordsPerPage) + 1 create table #temptable ( row int identity(1,1), au_id varchar(20), au_lname varchar(40), au_fname varchar(20), phone char(12), address varchar(40), city varchar(20), state char(2), zip char(5), contract bit ) -- set the rowcount to only the rows we need. set rowcount @rowsNeeded -- select data into a temp table, I choose to -- select all data, not just the key into the -- temp table to avoid another join if the table is large. insert into #temptable (au_id,au_lname,au_fname,phone,address,city,state,zip,contract) select au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM authors ORDER BY CASE WHEN @orderby=’au_lname’ THEN au_lname WHEN @orderby=’au_fname’ THEN au_fname WHEN @orderby=’au_id’ THEN au_id END ASC select row, au_id, au_lname, au_fname, phone, address, city, state, zip, contract from #temptable where row >=@startRecord drop table #temptable
If you use SQL Server 2005, the life must be easier. You can use ROW_NUMBER to do paging:
WITH OrderedOrders AS (select SalesOrderID, OrderDate, Row_Number() OVER (order by OrderDate)as RN from SalesOrderHeader ) SELECT * from OrderedOrders WHERE RN between 50 and 60
Well… At least, M$ have done something to improve the situation, but not all. It takes me all night to find out the way. I have even thought about switching to MySQL with a very simplier paging syntax, but this is my thesis :P, so I can’t.Thanks to the communities.
Okey, time to continue my work.