InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 05:21:58 GMT20http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]peter (1/10/2009)[/b][hr]My comments are focused on non-meaningful primary keys, which is what identity columns are. It is just a compact number (32 bit int in most cases) and [b]contrary to popular believe cannot always be assumed to be in time sequential order[/b] (identity inserts). Nor can it be assumed the total range is without gaps for that matter (failed insert transactions or explicit deletions). Thus sorting on identity is in fact likely to be meaningless unless special conditions are met (which are very hard to guarantee).[/quote] - You can achieve "time sequential order" by creating a "buffer" table for incoming data. And then move the blocks of sorted records from that buffer table into the main table;- Gaps in the range should not matter since you could use "TOP" clause to retreive the required number of records. [quote]... In your example it is safer to use some date field with in index on it to work with and have the user specify a datetime range to filter on in the first place (it is unlikely the user is just blindly paging without looking for something specific)[/quote] Yes.[quote] ...It is just that you should confront/combat problems where they originate.[/quote] Yes, it would be better to reuse proven technique on this or that side (in our case - database side). But what if the technique just don't fit the app requirements. [quote]An application can do more to make interactive (browsing in this case) more responsive and efficient. It can work with larger/multiple pages internally and only show one page to the user. Say you got a visible page size of 50 entries, you simply load 550 (11 pages) using a SQL paging mechanism (which is still efficient and from there you can go 5 pages back or forward without any database access.[/quote] Yes, but a user will wait for 11 pages to come even if he needs only one. In Ajax way it would be to supply a user with the requested page and then to send him and store left and right pages on the client when he is working with the requested page. On moving him to the left page later - feed his client buffer with the next left page.Mon, 12 Jan 2009 04:59:44 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/10/2009)[/b][hr][quote][b]peter (1/10/2009)[/b][hr]A primary key (identity or otherwise) cannot be used due to any sensible order by clause in the base query. Remember here that an order by on just the identity/primary key is meaningless, and not a case that is valid to consider for optimization. [/quote]This is a bit over-generalized statement that the order by identity is meaningless. Just one example: imagine a data-logger application. There is an incoming stream of measurements, and absolutely guaranted is that the later measurement (with later datetime) will be stored under the greater identity. Then a user needs to get a page corresponding to desired date and move left or right on the next pages. The number of pages is enormous and a user don't care about the number, he just wants to move left/right from the chosen timepoint.Many apps could be reduced to this model. Even that about contacts with billions of records, but that's another story. [quote]...Note that paging over a very large set is something you do not want as the results will be next to meaningless for the end user and end up not being used at all. In such a scenario you want the user to enter some filter restrictions so that you can use those in your base query to get the desired smaller set that can be page over efficiently. Often used filtering is to examine only one year at a time, filter by some category or require a minimum length for a search string (or any combination of those).[/quote]Filtering by, say, category won't break the idea. Still it will be the same initial Id and the page size, just WHERE clause will change. Different order would be an issue, that could be solved by creating sort of "custom index column" in adition to the identity. Maintaining such "custom index" will be a headache, but when the number of requests is very big it could help paging over the huge date. [quote]Thus beyond paging over a moderately sized set, it is no longer a database design/code issue, but an application design issue instead. You got to combat the problems where they are caused and not try to fix resulting symptoms in the database by making assumption based optimizations.[/quote]Why not "making assumption based optimizations" and why to treat an app designer like an enemy? :-)[/quote]My comments are focused on non-meaningful primary keys, which is what identity columns are. It is just a compact number (32 bit int in most cases) and [b]contrary to popular believe cannot always be assumed to be in time sequential order[/b] (identity inserts). Nor can it be assumed the total range is without gaps for that matter (failed insert transactions or explicit deletions). Thus sorting on identity is in fact likely to be meaningless unless special conditions are met (which are very hard to guarantee).Your case is quite simple and efficient, BUT it does not follow the same usage pattern as the generic paging solutions posted here that can handle any sorting required. Your case only works perfectly when you go 1 page forward or 1 page backward (with some special handling) or when there are no gaps in the whole identity range. If those conditions are not met, you cannot go from page 10 to 111 that easy. In your example it is safer to use some date field with in index on it to work with and have the user specify a datetime range to filter on in the first place (it is unlikely the user is just blindly paging without looking for something specific).Sure there are more special cases, there will always be some, and in each special case you want a special solution and that solution is more likely to be in the design of the application layer then in the database. And I am in no way hostile to designers, nor developers (I am one myself) mind you. It is just that you should confront/combat problems where they originate. If an user interface promotes inefficient actions (like paging trough 100 pages looking for a specific entry as it has no search), it generates unnecessary load on the database layer. Now you can optimize and special case all you want in the database, but be honest...that is not the best place to deal with the issue. If a redesign of that part of the application results to less browsing a user works more efficient and so does your application and database.PS.An application can do more to make interactive (browsing in this case) more responsive and efficient. It can work with larger/multiple pages internally and only show one page to the user. Say you got a visible page size of 50 entries, you simply load 550 (11 pages) using a SQL paging mechanism (which is still efficient and from there you can go 5 pages back or forward without any database access.Sat, 10 Jan 2009 14:33:33 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]peter (1/10/2009)[/b][hr]A primary key (identity or otherwise) cannot be used due to any sensible order by clause in the base query. Remember here that an order by on just the identity/primary key is meaningless, and not a case that is valid to consider for optimization. [/quote]This is a bit over-generalized statement that the order by identity is meaningless. Just one example: imagine a data-logger application. There is an incoming stream of measurements, and absolutely guaranted is that the later measurement (with later datetime) will be stored under the greater identity. Then a user needs to get a page corresponding to desired date and move left or right on the next pages. The number of pages is enormous and a user don't care about the number, he just wants to move left/right from the chosen timepoint.Many apps could be reduced to this model. Even that about contacts with billions of records, but that's another story. [quote]...Note that paging over a very large set is something you do not want as the results will be next to meaningless for the end user and end up not being used at all. In such a scenario you want the user to enter some filter restrictions so that you can use those in your base query to get the desired smaller set that can be page over efficiently. Often used filtering is to examine only one year at a time, filter by some category or require a minimum length for a search string (or any combination of those).[/quote]Filtering by, say, category won't break the idea. Still it will be the same initial Id and the page size, just WHERE clause will change. Different order would be an issue, that could be solved by creating sort of "custom index column" in adition to the identity. Maintaining such "custom index" will be a headache, but when the number of requests is very big it could help paging over the huge date. [quote]Thus beyond paging over a moderately sized set, it is no longer a database design/code issue, but an application design issue instead. You got to combat the problems where they are caused and not try to fix resulting symptoms in the database by making assumption based optimizations.[/quote]Why not "making assumption based optimizations" and why to treat an app designer like an enemy? :-)Sat, 10 Jan 2009 06:41:07 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/10/2009)[/b][hr][quote][b]Adam Haines (1/8/2009)[/b][hr]...First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through...[/quote] You are using CTE to build PagingCTE from which you then will extract the needed page. Hmm, on each request to the server you'll need to reconstruct the whole PagingCTE to retreive just a small fraction of it to the client.For the particular case when FirstName and LastName are NOT supplied, that is, Contacts are not filtered, from what I know about the optimizer, you may wish not using CTE at all. As far as I understand, you are trying to position the first record of the current page just after the last record of the page that was previosly retreived.Once you are using "ContactID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED", why not make use of it?[code]CREATE PROCEDURE [dbo].[usp_ContactPaging] ( @pageFirstRowIndex int, @pageRows int)ASSET ROWCOUNT @pageFirstRowIndexSELECT @FirstContactID= ContactId FROM Contacts ORDER BY ContactId[/code]At this point you have ContactId of the first row of the current page and can easily get next @pageRows rows.The nice thing about this trick, as far as I know about the optimizer, is that you are getting @FirstContactID not in @pageFirstRowIndex steps, but due to the binary search in much less steps. For a million records it should be sort of 20.[/quote]I am not sure if I understand you correctly. but here is some thoughts.A primary key (identity or otherwise) cannot be used due to any sensible order by clause in the base query. Remember here that an order by on just the identity/primary key is meaningless, and not a case that is valid to consider for optimization. Now you might suggest using other knowledge about the previous visited page to speed up subsequent ones, but here you have the issue of changing data making the assumption such an optimization relies on, invalid.As I see it:The best is to apply sensible filtering and indexes to speed up the base query. The resulting smaller set can be paged over with low cost and the results will always be accurate. Note that paging over a very large set is something you do not want as the results will be next to meaningless for the end user and end up not being used at all. In such a scenario you want the user to enter some filter restrictions so that you can use those in your base query to get the desired smaller set that can be page over efficiently. Often used filtering is to examine only one year at a time, filter by some category or require a minimum length for a search string (or any combination of those).Thus beyond paging over a moderately sized set, it is no longer a database design/code issue, but an application design issue instead. You got to combat the problems where they are caused and not try to fix resulting symptoms in the database by making assumption based optimizations. From this perspective it is even valid to set a maximum for the base set by using a top clause in the base query. This will keep performance in a desirable range and then have the application signal a warning that the result set is to large when the maximum number of rows is found. The user can then refine his search criteria without having to wait minutes for the first query to finish, I know of no user that is going to visit all 30000 pages anyway.Sat, 10 Jan 2009 05:22:41 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/8/2009)[/b][hr]...First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through...[/quote] You are using CTE to build PagingCTE from which you then will extract the needed page. Hmm, on each request to the server you'll need to reconstruct the whole PagingCTE to retreive just a small fraction of it to the client.For the particular case when FirstName and LastName are NOT supplied, that is, Contacts are not filtered, from what I know about the optimizer, you may wish not using CTE at all. As far as I understand, you are trying to position the first record of the current page just after the last record of the page that was previosly retreived.Once you are using "ContactID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED", why not make use of it?[code]CREATE PROCEDURE [dbo].[usp_ContactPaging] ( @pageFirstRowIndex int, @pageRows int)ASSET ROWCOUNT @pageFirstRowIndexSELECT @FirstContactID= ContactId FROM Contacts ORDER BY ContactId[/code]At this point you have ContactId of the first row of the current page and can easily get next @pageRows rows.The nice thing about this trick, as far as I know about the optimizer, is that you are getting @FirstContactID not in @pageFirstRowIndex steps, but due to the binary search in much less steps. For a million records it should be sort of 20.Sat, 10 Jan 2009 03:16:21 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/8/2009)[/b][hr]Peter,I love how you only return the totals in the first row of the output. this will definitely reduce the amount of data being returned to the application. I have a few things to note here about the functionality and you may have addressed them in your environment. First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through. Secondly, have you looked at using a stored procedure to handle the code, instead of the application executing ad-hoc SQL? The reason I ask is, the db engine will never be able to reuse a query plan unless an exact binary match already exists. This means you are not benefiting from query plan reuse or parameterization. Also, by introducing a stored procedure solution, you can remove the nested cte and use variables to maintain the totals. You can then apply a case statement to the final result set to only put the totals on the first row. For example, case when @PgFirstRecord = RowNbr then @TotalPages else null end.[/quote]Thanks for the compliment :)I simplified the (fictual) query as far as I could for demo purposes, thus no where clause. But you are right in making it clear that when a filter need to be used in the query, it should be done in the topmost CTE (this might not immediatly obvious for those new to CTE's).In my production situation some of the base queries span several screen pages with complex (partial generated) conditions. The true variable values in the code are all send to SQL sever separate from the base query, it is a feature of the language I use this in (ColdFusion). So as long as a second request comes in with the exact same structure (which is quite likely when working on a grid that has default settings) the query plans wil get reused without a problem. There will just be several for each permutation / filtering options that are in use.There are cases when stored procedures make more sense of course, but not in the environment I work in and the type/method of development we do. It is all too small scale, no dedicted DBA and every developer needs to make his own SQL code. Usualy that goes quite well as the average SQL knowledge level ain't too bad. If things to seem too slow (that happends sometimes), it's me who works on it to solve the issue (quite liking that part I have to say).Thu, 08 Jan 2009 09:11:05 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/8/2009)[/b][hr]Peter, I couldn't really follow the code you had. Was it supposed to be just separate batch files or was some/all of it supposed to be wrapped up in sprocs/functions/etc? Any chance you can attach it as a zip of appropriate file types?[/quote]If you concatenate all the code snipets you have a working example. It consist of two parts, the first code block created a demo table (I assume you got that working). The others are meant to be one, but to allow for some ceomments I put them in seperate code blocks.The forum smileys are irritating tho, usualy they mean ')', but i see they sometimes cover the text "as" as well (when assigning a table alias). But to be clear I will an attachement this round. Containing separate scripts for generating a demo table and the demo itself.Thu, 08 Jan 2009 08:42:43 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/8/2009)[/b][hr]Peter, I couldn't really follow the code you had. Was it supposed to be just separate batch files or was some/all of it supposed to be wrapped up in sprocs/functions/etc? Any chance you can attach it as a zip of appropriate file types?[/quote]You just have to paste each section together and it will build a nested cte.Thu, 08 Jan 2009 07:55:06 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxPeter,I love how you only return the totals in the first row of the output. this will definitely reduce the amount of data being returned to the application. I have a few things to note here about the functionality and you may have addressed them in your environment. First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through. Secondly, have you looked at using a stored procedure to handle the code, instead of the application executing ad-hoc SQL? The reason I ask is, the db engine will never be able to reuse a query plan unless an exact binary match already exists. This means you are not benefiting from query plan reuse or parameterization. Also, by introducing a stored procedure solution, you can remove the nested cte and use variables to maintain the totals. You can then apply a case statement to the final result set to only put the totals on the first row. For example, case when @PgFirstRecord = RowNbr then @TotalPages else null end.Thu, 08 Jan 2009 07:52:12 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxPeter, I couldn't really follow the code you had. Was it supposed to be just separate batch files or was some/all of it supposed to be wrapped up in sprocs/functions/etc? Any chance you can attach it as a zip of appropriate file types?Thu, 08 Jan 2009 07:33:24 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/7/2009)[/b] ...Unfortunately, this still should yield a index scan. The case expression is still evaluated for every row, as the optimizer doesnt know if the variable matches the case expression, until it evaluates it. For this reason the optimizer cannot seek and much evaluate each FirstName...[/quote] You seem to be right. I created a table in MS SQL Managament Studio[code]CREATE TABLE GUIDs( GUID uniqueidentifier)CREATE INDEX IX_GUID ON GUIDs(GUID) [/code]Populated it with 10 million records with unique GUID in each record. And then tried to fetch an existent/non-existent record from the table using - direct [code]WHERE GUID = @GUID[/code]- COALESCE [code]WHERE GUID = COALESCE(@GUID,GUID)[/code]- and CASE:[code]WHERE CASE WHEN @GUID IS NOT NULL THEN CASE WHEN GUID = @GUID THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]For example:[code] DECLARE @GUID uniqueidentifier SET @GUID = '093cd7ed-db22-40fb-9f77-5be0e33aaaaa' SELECT * FROM GUIDS WHERE CASE WHEN @GUID IS NOT NULL THEN CASE WHEN GUID = @GUID THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]Yes, direct "WHERE GUID = @GUID" fetched the record in no time"COALESCE" and "CASE" seemed to load the table in memory (~400 Mbyte) on the first run and then full-scanned the table. The loading took about 5 secs and the scan took ~1 sec. The PC was Core Duo 3.16 with 4G memory under Vista 64 Home.Yes, I did "Display estimated execution plan" in SQL Managament Studio and saw "table scan cost 100%". I just did not beleive my eyes the optimizer was that stupid (in my opinion).So, my apologies, and thanks for the oportunity to gain the knowledge.Thu, 08 Jan 2009 05:28:04 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxFirst I am back a day later then I expected and definatly had hoped for, sorry for that. And please someone FIX this forum (smileys in code, persistent extended page widths even after I reformatted the code to fit).Now my 'paging' code that I said would contribute. Bear in mind I work in an enviromment that is able to send parameterized queries from the application layer to the database. The SQL code itself originates from the application layer most of the time where queries are created dynamically with relativly ease.What I created a while back was a method of reducing the SQL complexity when dealing with paging on the application side. For it to work the main query needs to be written in a CTE and return a sequence number that can be sorded on in the actual query. It is also used by the paging logic in this query to exclude unwanted records among other things.At the end of the normal query (now wrapped in a CTE), an application function is called to generate the paging part of the query for the developer automatically. The part deals with limiting the amount of data is transmidded by SQL sever to the appliation sever. The code also takes care of out of bounds cases and adjusts the page number accordingly. On top of this an optional part generates a comma seperated list of primary key IDs of all the records in the result, including the ones not returned due to paging.First we need some demo data (10 000 records should do):[code]-- Create demo table with primary key--create table dbo.DemoTable( Id int not null, Name varchar(36) not null, SomeData varbinary(20) not null , constraint pk_DemoTable primary key clustered( ID ));go-- Insert 10.000 test records into the demo table--insert into dbo.DemoTable( Id, Name, SomeData ) select numbers.N , newID() , hashBytes( 'SHA1', cast( numbers.N as varchar ) ) from ( select top 10000 row_number() over ( order by t1.object_id ) from master.sys.All_Columns as t1 with( nolock ) cross join master.sys.All_Columns as t2 with( nolock ) ) as numbers( N )go-- Index the demo table--create index ix_DemoTable_Name on dbo.DemoTable ( Name );create index ix_DemoTable_SomeData on dbo.DemoTable ( SomeData );go-- Show the first few records to get a feel of what the data looks like--select top 10 * from dbo.DemoTable where Id <= 10go[/code]Next is a demo query itself that demonstrates the techniques used and makes clear how easy the results can be processed further:[code]-- For demo purposes only: these parameters are normaly injected into-- the paging secopn of the query as constants--declare @pgNum int;declare @pgSize int;select @pgNum = 300, @pgSize = 25;[/code][code];with /* This code is put into the page that shows the grid */ pgFromQ as ( select row_number() over ( -- Order by, to be generated by application code so grid sorting can change over time order by demo.Name desc , demo.Id asc -- ALWAYS include the primary key as the last field when sorting ) as 'pgRowNumber' , demo.Id , demo.Name , demo.SomeData from dbo.DemoTable demo )[/code]Then the developer calls a function that generates the rest of the query.It requires a few parameters: 1: Page to show (4) 2: Page size (25) 3: Optional field name ("Id" in this example). When specified it causes a comma seperated list to be generated of all the IDs. This can be used to 'browse' over the results in the any detail screen.It looks like (in whatever language you use):pagerSQL( 4, 25, "Id" )This code then generates the remainder of the query (2 parts). The first part is some more CTEs that handle page clipping and the like.[code], pgCountQ( recordCount, pageCount ) as ( -- determines the number of pages select top 1 count(*) , floor( (count(*) + @pgSize - 1) / @pgSize ) from pgFromQ ), pgPageQ( activePage, recordCount, pageCount ) as ( select top 1 case when @pgNum < 1 then 1 when @pgNum > pageCount then pageCount else @pgNum end , recordCount , pageCount from pgCountQ ), pgRangeQ( firstRecord, activePage, recordCount, pageCount ) as ( select top 1 case when activePage = 0 then 0 else ( @pgSize * ( activePage - 1 ) + 1 ) end , activePage , recordCount , pageCount from pgPageQ )-- this is the optional part that is generates the comma separated list of a key field ("Id" in this example), pgRecListQ( recIDs ) as ( select stuff ( ( select cast( ',' as varchar(max) ) + cast( i.Id as varchar ) from pgFromQ as i order by i.pgRowNumber for xml path('') ) , 1 , 1 , '' ) )-- end of optional part[/code]The second part of the generated code performs the actual query and makes sure the least amount of data is returned by SQL.[code]/*-- And finaly the select that returns the result set (only the first record of the resulting page-- contains extra such as number of first record, the active page number, the recordcount and the pagecount.-- And inf choosen also the optional comman separated list of IDs that represent the non-paged result.*/select case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.firstRecord else null end as 'pgFirstRecord', case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.activePage else null end as 'pgActivePage', case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.recordCount else null end as 'pgRecordCount', case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.pageCount else null end as 'pgPageCount'-- this is the optional part that is returns the comma separated list of a key field ("Id" in this example), case when pgFromQ.pgRowNumber = pgRangeQ.firstRecord then ( select top 1 recIDs from pgRecListQ ) else null end as 'pgRecordIDs'-- end of optional part, pgFromQ.*from pgRangeQ left join pgFromQ on pgFromQ.pgRowNumber between pgRangeQ.firstRecord and pgRangeQ.firstRecord + @pgSize - 1order by pgFromQ.pgRowNumber;[/code]The automaticaly generated code might seem complicated at first but once understood it is not that bad, nor is it expensive. It is nicely hidden from the functionality of the original query and it makes processing the results easy and efficient. The only times I experienced bad performance was when testing with generation of the optional comma separated list over say 100 000 records and when the original/main query was slow. It is always a matter of using the right design for the job, so that ain't a negative for the method itself. It certainly beats all the paging I ever seen before in applications on speed, transmission efficiency and usability.Thu, 08 Jan 2009 04:41:23 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxWhich is why I was saying it should be decompiled down to it's logical equivalent, which will do the right thing:[code]create table #t (fname varchar(20))insert #t (fname)select top 100 'asdf'from sysobjectsinsert #t (fname) values ('zzzz')create index a on #t (fname)declare @fname varchar(20)set @fname = 'asdf' --does index scanselect *from #tWHERE CASE WHEN fname = @fname THEN 1ELSE 0 END = 1 dbcc freeproccachegodeclare @fname varchar(20)set @fname = 'zzzz' --does index scan as well, even though only 1 rowselect *from #tWHERE CASE WHEN fname = @fname THEN 1ELSE 0 END = 1 dbcc freeproccachegodeclare @fname varchar(20)set @fname = 'asdf' --does index seek, since covering and orderedselect fnamefrom #tWHERE fname = @fname dbcc freeproccachegodeclare @fname varchar(20)set @fname = 'zzzz' --does index seek, since 1 rowselect fnamefrom #tWHERE fname = @fname [/code]Wed, 07 Jan 2009 14:30:12 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/7/2009)[/b][hr][quote][b]phystech (1/7/2009)[/b][hr][quote][b]TheSQLGuru (1/7/2009)[/b]...If you never passed in a NULL value then the CASE is surperfluous, right?.. [/quote] No, zero length string plays the role of NULL. That is if the parameter is zero length then grab all the records (sort of).[/quote]Your "sort of" is a critical statement! :)[code]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]The ELSE 1 will never be hit, thus the entire statement can be simplified to this (since you never have a NULL value passed in):[code]WHERE CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END = 1 [/code]and that, I believe, is no different logically than this:[code]WHERE FirstName = @FirstName [/code]in which case you will obviously do an index seek if the statistics estimate 1-2% of the rows will be hit based on the incoming value of @FirstName. And we are back to the parameter sniffing plan caching problem. :w00t:[/quote]Unfortunately, this still should yield a index scan. The case expression is still evaluated for every row, as the optimizer doesnt know if the variable matches the case expression, until it evaluates it. For this reason the optimizer cannot seek and much evaluate each FirstName.Here is a simple example.[code]select firstnamefrom contactsWHERE CASE WHEN FirstName = @FirstName THEN 1ELSE 0 END = 1 [/code]QueryPlan:[code] |--Index Scan(OBJECT:([tempdb].[dbo].[Contacts].[IXC_FirstName]), WHERE:(CASE WHEN [tempdb].[dbo].[Contacts].[FirstName]=[@firstname] THEN (1) ELSE (0) END=(1)))[/code]Wed, 07 Jan 2009 13:17:17 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/7/2009)[/b][hr][quote][b]phystech (1/7/2009)[/b][hr][quote][b]TheSQLGuru (1/7/2009)[/b]...If you never passed in a NULL value then the CASE is surperfluous, right?.. [/quote] No, zero length string plays the role of NULL. That is if the parameter is zero length then grab all the records (sort of).[/quote]Your "sort of" is a critical statement! :)[code]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]The ELSE 1 will never be hit, thus the entire statement can be simplified to this (since you never have a NULL value passed in):[code]WHERE CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END = 1 [/code]and that, I believe, is no different logically than this:[code]WHERE FirstName = @FirstName [/code]in which case you will obviously do an index seek if the statistics estimate 1-2% of the rows will be hit based on the incoming value of @FirstName. And we are back to the parameter sniffing plan caching problem. :w00t:[/quote]Please, don't take it too literally :-) I do not compare it to NULL:[code]WHERE CASE WHEN LEN(@FirstName) > 0 THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]Wed, 07 Jan 2009 13:07:23 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/7/2009)[/b][hr][quote][b]TheSQLGuru (1/7/2009)[/b]...If you never passed in a NULL value then the CASE is surperfluous, right?.. [/quote] No, zero length string plays the role of NULL. That is if the parameter is zero length then grab all the records (sort of).[/quote]Your "sort of" is a critical statement! :)[code]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]The ELSE 1 will never be hit, thus the entire statement can be simplified to this (since you never have a NULL value passed in):[code]WHERE CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END = 1 [/code]and that, I believe, is no different logically than this:[code]WHERE FirstName = @FirstName [/code]in which case you will obviously do an index seek if the statistics estimate 1-2% of the rows will be hit based on the incoming value of @FirstName. And we are back to the parameter sniffing plan caching problem. :w00t:Wed, 07 Jan 2009 12:53:53 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/7/2009)[/b]...Can you confirm that the code you are posting actually generates index seeks?...[/quote] I need a couple of days to prove/disprove it, right now I'm working on my current project. I'll post here then.Thanks.Wed, 07 Jan 2009 12:40:42 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/7/2009)[/b][hr][quote][b]TheSQLGuru (1/7/2009)[/b][hr][quote][b]phystech (1/6/2009)[/b][hr][quote][b]Adam Haines (1/6/2009)[/b][quote]The optimizer knows that "CASE WHEN @FirstName IS NOT NULL THEN" can be evaluated before the query is launched, since no column name is involved here and the resulting value could be either 0 or 1. So it can branch the plan into two subtrees. One subtree would result in logical "true" right at this point (no @FirstName was supplied) and the second subtree would go evaluating "FirstName = @FirstName " using the index, that is very quickly.[/quote]Lets assume this is true (I did not test it so won't state yea or nay on that). What you WILL get is a cached plan with whatever gets executed first. That guarantees that other calls that want the opposite plan will be completely suboptimal in performance. If you are on SQL 2005 you can use OPTION (RECOMPILE) on the statement to help out, but that still assumes that the optimizer can and does do the right thing with whatever inputs are given, which I question.[/quote]I've been using the approach within rather complex query for some months. And my strong impression is that the short-circuiting works. Though I don't short-circuit it on NULL value, I do it on zero string length, but this should not be different. I'll try to test the approach "stand-alone" within a week and publish on my blog.Thanks.[/quote]Phystech,Can you confirm that the code you are posting actually generates index seeks? I still do not see how it is possible. I wan to step through the code you posted and demonstrate how the optimizer will treat each section. [code]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 and[/code]The first (outer) case expression does exactly what you say it will do. If the variable is null, the filter is short circuited. I agree with this. It is the inner case expression that I have a problem with. The inner case expression compares each FirstName value in the table to the variable @FirstName. I am sure we can both agree on this; however, I believe that the optimizer is treating this differently than you are expecting. The inner case expression will have to be evaluated for every row returned by the query. The optimizer has no way of knowing whether or not the FirstName Column equals @FirstName until it checks each row returned. You can confirm this by using your code in my initial stored procedure, with your modification.[code]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 and CASE WHEN @LastName IS NOT NULL THEN CASE WHEN LastName = @LastName THEN 1 ELSE 0 END ELSE 1 END = 1 [/code]You will see no matter what combination of FirstName and LastName is used the end result is the same. The behavior is the same regardless of the case expression being in the select statement or the predicate. This behavior occurs because the optimizer has to look through each returned rows and evaluate FirstName to see if it matches the variable. The optimizer cannot seek a set of rows with the case expression and you cannot short circuit the inner case statement because you can potentially exclude rows. However, there is a small catch that may be covering up the actual behavior, in your environment. if you apply a differing filter that is always present, the case expressions behavior can be hidden away in another execution plan task. For example, you use your code in the where clause on firstname and use LastName like @LastName + '%', for lastName. You will see that the scan is gone. The scan is gone because the optimizer was able to use an index and then can simple apply the case express on each of the returned rows. The bottom line is you will always get an index scan using the logic you have posted, unless you use some other filter that gaurentees a seek. I would still recommend avoiding case expressions in the where clause, but to each is own.Wed, 07 Jan 2009 12:19:37 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/7/2009)[/b]...If you never passed in a NULL value then the CASE is surperfluous, right?.. [/quote] No, zero length string plays the role of NULL. That is if the parameter is zero length then grab all the records (sort of).Wed, 07 Jan 2009 11:28:23 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[code]I've been using the approach within rather complex query for some months. And my strong impression is that the short-circuiting works. Though I don't short-circuit it on NULL value, I do it on zero string length, but this should not be different. I'll try to test the approach "stand-alone" within a week and publish on my blog.Thanks.[/code]If you never passed in a NULL value then the CASE is surperfluous, right? You will never get (or need) a plan that executes the ELSE. I will say that it would be VERY helpful if this construct gives an index seek when appropriate and a table scan when it is not, but there will still be the cached plan issue to deal with.Wed, 07 Jan 2009 11:14:09 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxNice one.. The discussion also interesting..Wed, 07 Jan 2009 10:21:53 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/7/2009)[/b][hr][quote][b]phystech (1/6/2009)[/b][hr][quote][b]Adam Haines (1/6/2009)[/b][quote]The optimizer knows that "CASE WHEN @FirstName IS NOT NULL THEN" can be evaluated before the query is launched, since no column name is involved here and the resulting value could be either 0 or 1. So it can branch the plan into two subtrees. One subtree would result in logical "true" right at this point (no @FirstName was supplied) and the second subtree would go evaluating "FirstName = @FirstName " using the index, that is very quickly.[/quote]Lets assume this is true (I did not test it so won't state yea or nay on that). What you WILL get is a cached plan with whatever gets executed first. That guarantees that other calls that want the opposite plan will be completely suboptimal in performance. If you are on SQL 2005 you can use OPTION (RECOMPILE) on the statement to help out, but that still assumes that the optimizer can and does do the right thing with whatever inputs are given, which I question.[/quote]I've been using the approach within rather complex query for some months. And my strong impression is that the short-circuiting works. Though I don't short-circuit it on NULL value, I do it on zero string length, but this should not be different. I'll try to test the approach "stand-alone" within a week and publish on my blog.Thanks.Wed, 07 Jan 2009 10:16:05 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/6/2009)[/b][hr][quote][b]Adam Haines (1/6/2009)[/b][quote]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 ---instead of ---WHERE [FirstName] = COALESCE(@FirstName,FirstName) ---The difference is that column name "FirstName" is not involved in"CASE WHEN @FirstName IS NOT NULL THEN"Thus we have some sort of short circuiting preventing from the full table scan (At least I beleive so).[/quote]I believe this will still cause an index scan/table scan. The problem I see with this is the variable in the predicate, which means the optimizer will have to compare the variable with the column value for each row to determine if it should be given the value of 1.[/quote]The optimizer knows that "CASE WHEN @FirstName IS NOT NULL THEN" can be evaluated before the query is launched, since no column name is involved here and the resulting value could be either 0 or 1. So it can branch the plan into two subtrees. One subtree would result in logical "true" right at this point (no @FirstName was supplied) and the second subtree would go evaluating "FirstName = @FirstName " using the index, that is very quickly.[/quote]Lets assume this is true (I did not test it so won't state yea or nay on that). What you WILL get is a cached plan with whatever gets executed first. That guarantees that other calls that want the opposite plan will be completely suboptimal in performance. If you are on SQL 2005 you can use OPTION (RECOMPILE) on the statement to help out, but that still assumes that the optimizer can and does do the right thing with whatever inputs are given, which I question.Wed, 07 Jan 2009 09:09:49 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/6/2009)[/b][quote]WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 ---instead of ---WHERE [FirstName] = COALESCE(@FirstName,FirstName) ---The difference is that column name "FirstName" is not involved in"CASE WHEN @FirstName IS NOT NULL THEN"Thus we have some sort of short circuiting preventing from the full table scan (At least I beleive so).[/quote]I believe this will still cause an index scan/table scan. The problem I see with this is the variable in the predicate, which means the optimizer will have to compare the variable with the column value for each row to determine if it should be given the value of 1.[/quote]The optimizer knows that "CASE WHEN @FirstName IS NOT NULL THEN" can be evaluated before the query is launched, since no column name is involved here and the resulting value could be either 0 or 1. So it can branch the plan into two subtrees. One subtree would result in logical "true" right at this point (no @FirstName was supplied) and the second subtree would go evaluating "FirstName = @FirstName " using the index, that is very quickly.Tue, 06 Jan 2009 16:30:53 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxI remember reading a similar article on here back in 2007 ([url=http://www.sqlservercentral.com/articles/Advanced+Querying/3181/]http://www.sqlservercentral.com/articles/Advanced+Querying/3181/[/url])Always good for these things to pop up every now and again, keeps people thinking about ways to do things as well as letting newer members know that these things exist.And yes, these functions belong to that well know SQL saying of "it depends". If you only have a dataset with a few hundred or thousand records then server side paging is probably a waste of time and effort. However, if you have millions of rows that are going to be brought back then suddenly it all makes sense. Data manipulation is what SQL is good at, why not let it do it (when appropriate and regardless of whether you use a 2k or 2k5 method).the only thing to think about really is: Where is the best place to page my data?Think about: - How many rows will be returned?- How heavily used is the SQL server?- How heavily used is the web server? **for web applications, remember, the rows not being sent to the browser are still being stored on the web server)- How good is your network?- Is the Moon in the 8th house of Aquarius?And so on..Also don't be afraid to use these code samples and experiment, just because you don't have a use for this exact implementation, doesn't mean that a modified version won't solve another problem.-dTue, 06 Jan 2009 16:12:20 GMTDavid in .AUhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/6/2009)[/b][hr][quote][b]Adam Haines (1/6/2009)[/b][hr][quote][b]phystech (1/6/2009)[/b][hr][quote]What can we do to make this query index seek? Well, we really only have two options. One is to use dynamic sql to build the predicate dynamically. Two is to introduce control flow logic and have differing statements for each possibility[/quote]Third option is to use CASE expression in a smart way instead of using either the COALESCE function or (WHERE FirstName = @FirstName OR @FirstName IS NULL) trick.[/quote]This will still result in a table scan. If you look at the execution plan, you will see that COALESCE actually uses a case expression to evaluate each option in the function. This means using COALESCE and a case expression should result in the same execution plan. The reason you get a table scan with coalesce is because the predicate is always equal to itself, but the optimizer still has to check each row to see if it equals itself, thus resulting in a table scan.[/quote]No, I said "use CASE expression in a smart way":---WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 ---instead of ---WHERE [FirstName] = COALESCE(@FirstName,FirstName) ---The difference is that column name "FirstName" is not involved in"CASE WHEN @FirstName IS NOT NULL THEN"Thus we have some sort of short circuiting preventing from the full table scan (At least I beleive so).[/quote]I believe this will still cause an index scan/table scan. The problem I see with this is the variable in the predicate, which means the optimizer will have to compare the variable with the column value for each row to determine if it should be given the value of 1.Tue, 06 Jan 2009 15:57:27 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]noeld (1/6/2009)[/b][hr]Although the article is well written but I have a word of advice. For very large tables row_number() over().. has been inefficient for me when it comes to pagination![/quote]Noeld,I too have seen many pagination solutions hit the ceiling when they try to work off very large tables. The key things to note here are: -the where clause in the inner portion of the cte will make a large table relativley small with the proper indexing and filtering. - no reasonable person should ever need to return millions of rows of data on an application screen because that is simply too much data for them to evaluate. All-in-all, with the proper filteration and indexing this method should do fairly well in most scenarios even on large tables. Even in the examples provided, you get ms response times, in searching through a million records and that is pretty good.Tue, 06 Jan 2009 15:35:31 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Adam Haines (1/6/2009)[/b][hr][quote][b]phystech (1/6/2009)[/b][hr][quote]What can we do to make this query index seek? Well, we really only have two options. One is to use dynamic sql to build the predicate dynamically. Two is to introduce control flow logic and have differing statements for each possibility[/quote]Third option is to use CASE expression in a smart way instead of using either the COALESCE function or (WHERE FirstName = @FirstName OR @FirstName IS NULL) trick.[/quote]This will still result in a table scan. If you look at the execution plan, you will see that COALESCE actually uses a case expression to evaluate each option in the function. This means using COALESCE and a case expression should result in the same execution plan. The reason you get a table scan with coalesce is because the predicate is always equal to itself, but the optimizer still has to check each row to see if it equals itself, thus resulting in a table scan.[/quote]No, I said "use CASE expression in a smart way":---WHERE CASE WHEN @FirstName IS NOT NULL THEN CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END ELSE 1 END = 1 ---instead of ---WHERE [FirstName] = COALESCE(@FirstName,FirstName) ---The difference is that column name "FirstName" is not involved in"CASE WHEN @FirstName IS NOT NULL THEN"Thus we have some sort of short circuiting preventing from the full table scan (At least I beleive so).Tue, 06 Jan 2009 15:31:05 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Bryan Smith (1/6/2009)[/b][hr]Adam I agree with your statement on using Control Logic (and great article btw). I'm curious though, using this method have you run into problems with queries plans. What I mean is for example when the stored procedure is first ran it gets compiled with the query plan for the case where FirstName is null and LastName is what is index on; so subsequent times when FirstName is not null a unoptimal query plan is used. The solutions I know of are to have the procedure recompile each time it is used, thus a new query plan or use your control logic to call out other stored procedures that have just one query in them. I've prefered the 2nd option in the past, as I prefer the finite control and want to skip the hit of recompiling. But perhaps you know of a better solution?Thanks.[/quote]Well you are partially correct in your assumption, but the stored procedure should not recompile every time. What you should see is a query plan for each set of options, in this case there are three. A first name and no last name, no first name and a last name, and finally no first name and no last name. For example, if you supply first name and no last name a query plan will be created. The next go around you supply no first name and a lastname.. this will result in a new query plan. You will be able to reuse the query plan for first name and no last name every time you execute the stored procedure with those parameters and likeswise for no first name and a last name. This means you should be reusing query plans quite often.You can use the code below to watch the execution plan count increase for each run.[code]USE [tempdb]GO;WITH cteAS(SELECT qs.execution_count, SUBSTRING(qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN len(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text, qt.dbid, dbname=db_name(qt.dbid), qt.objectid FROM sys.dm_exec_query_stats qsCROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE db_name(qt.dbid) = 'tempdb')SELECT *FROM cteWHERE query_text LIKE ';WITH PagingCTE%'ORDER BY execution_count DESC[/code]Tue, 06 Jan 2009 15:20:23 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]Eric Inman (1/6/2009)[/b][hr]one flaw would be ORDER BY and TOP. Check this out:Check this outhttp://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx[/quote]The title is "TOP 100 Percent ORDER BY Considered Harmful". My query doesn't use TOP 100(N)Percent. Another aspect is that normally and recommended by good practices any table should have a primary key where in SQL server is by default clustered. Clustered means is that the physical order of the rows will be determined by the ascending/descending order(asc by default) of the primary clustered key. Now, I agree that this is not always the situation, unfortunately I might add, usually due to bad design but normally that would be the case and I also noted in my example that a unique key (i should have said primary, i agree) would help. Conclusion is that I can rest assured that given the normal conditions, a primary clustered key, the query will work.Just try it.Cheers.Tue, 06 Jan 2009 15:02:58 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxAlthough the article is well written but I have a word of advice. For very large tables row_number() over().. has been inefficient for me when it comes to pagination!Tue, 06 Jan 2009 14:57:01 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxAdam I agree with your statement on using Control Logic (and great article btw). I'm curious though, using this method have you run into problems with queries plans. What I mean is for example when the stored procedure is first ran it gets compiled with the query plan for the case where FirstName is null and LastName is what is index on; so subsequent times when FirstName is not null a unoptimal query plan is used. The solutions I know of are to have the procedure recompile each time it is used, thus a new query plan or use your control logic to call out other stored procedures that have just one query in them. I've prefered the 2nd option in the past, as I prefer the finite control and want to skip the hit of recompiling. But perhaps you know of a better solution?Thanks.Tue, 06 Jan 2009 14:50:30 GMTBryan Smith-461107http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]phystech (1/6/2009)[/b][hr][quote]What can we do to make this query index seek? Well, we really only have two options. One is to use dynamic sql to build the predicate dynamically. Two is to introduce control flow logic and have differing statements for each possibility[/quote]Third option is to use CASE expression in a smart way instead of using either the COALESCE function or (WHERE FirstName = @FirstName OR @FirstName IS NULL) trick.[/quote]This will still result in a table scan. If you look at the execution plan, you will see that COALESCE actually uses a case expression to evaluate each option in the function. This means using COALESCE and a case expression should result in the same execution plan. The reason you get a table scan with coalesce is because the predicate is always equal to itself, but the optimizer still has to check each row to see if it equals itself, thus resulting in a table scan.Tue, 06 Jan 2009 14:49:20 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote]What can we do to make this query index seek? Well, we really only have two options. One is to use dynamic sql to build the predicate dynamically. Two is to introduce control flow logic and have differing statements for each possibility[/quote]Third option is to use CASE expression in a smart way instead of using either the COALESCE function or (WHERE FirstName = @FirstName OR @FirstName IS NULL) trick.Tue, 06 Jan 2009 14:40:21 GMTphystechhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxone flaw would be ORDER BY and TOP. Check this out:Check this outhttp://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspxTue, 06 Jan 2009 14:28:38 GMTeinman33http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxWhat happened to a simple query like bellow? You only need to set the number of rows per page and the number of page you want to retrieve. I added the versions for both 2K and 2K5 because the query uses the TOP clause and it needs to be dynamic in 2K while in 2K5 and up you can use variables(expressions) in the TOP clause. The "numbers" table in this case was just that, numbers from 1 to 10000 but it can be any table. It helps if theres is a unique key on the table of course. If you change the page_number value you will retrieve the corresponding rows for that page (26 to 50 in this case). You can also change to a different number of rows per page.DECLARE @sql varchar(1000), @rows_per_page int, @page_number int-- set the number of rows per pageSELECT @rows_per_page=25-- set the page number you want to retrieveSELECT @page_number=2-- SQL 2000 version using dynamic querySELECT @sql='SELECT * FROM numbers act INNER JOIN ( SELECT TOP '+cast(@rows_per_page as varchar)+' number FROM ( SELECT TOP '+cast(@page_number*@rows_per_page as varchar)+' number FROM numbers ORDER BY number ASC ) AS Act1 ORDER BY number DESC ) AS Act2 ON Act2.number=act.numberORDER BY act.number ASC'EXEC (@sql)-- SQL 2005 version where variables can be used in TOP clausesSELECT * FROM numbers act INNER JOIN ( SELECT TOP (@rows_per_page) number FROM ( SELECT TOP (@page_number*@rows_per_page) number FROM numbers ORDER BY number ASC ) AS Act1 ORDER BY number DESC ) AS Act2 ON Act2.number=act.numberORDER BY act.number ASCTue, 06 Jan 2009 13:58:45 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxI agree with the comments about increasing resource utilization on the database server. This is an issue of architecture and how you are going to address pagination. Within any particular software package it should be done in the same method if possible and this will allow that but what is the true penalty for using this method? Is it better to distribute the pagination processing to the webserver? What are the costs interms of complex queries being passed around. And what about the reforming of this list? I doubt SQL is keeping everything locked down waiting for the next request in this method. So is there a possibility or probability of shifting data?Tue, 06 Jan 2009 12:17:22 GMTConan Whalen-McKain-370433http://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspxAll good for SQL 2005, but what for SQL 2000 ?In that case, you can have a look at the article I wrote : http://www.codeproject.com/KB/aspnet/paging_various_databases.aspxTue, 06 Jan 2009 11:25:46 GMTErwin@ODShttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx@GaryN,I am one of those .net developers as well. What holds me back using Adam's solution is mainly a maintenance thing. Having a few dozen tables and views within a application easily, keeping track of separate paging procedures is nasty.I am using this procedure in combination with a 'return only the recordcount' version. Which looks like SELECT COUNT(*) FROM {0} WHERE {1}, constructed from the same tablename/whereclause parameters. Recordcount/pagesize delivers me the number of pages.Personally I am not experiencing any strong-typing issues. Since I am using a code-generator to generate my data classes and CRUD procedures. These classes grant me access to column-name properties. When you use these, you can compile-error track easily in your code when column name changes require adjustments. A datareader then populates my strong typed class. Btw, this same datareader could just as easy fill a strong types dataset too. Regarding security, respecting the normal rules like minimal rights only and checking user input for malicious input, I think it's just as safe an anything else. Many times you end up constructing the clauses without user-input at all, by combining combobox selections etc.Regarding performance, it does what I need it to do, which is returning somewhere between 25 to 500 rows in a fraction of a second. Off course common sense is the main factor here, make sure your where clause addresses indexed fields. Pretty sure 'my way' is not the most performant.No doubt there are dozens of simular solutions for paging. I think Adam has provided us with a great insight into his solution. Stick with whatever works for you, would be my advice here.Tue, 06 Jan 2009 10:39:31 GMTsouplexhttp://www.sqlservercentral.com/Forums/Topic630336-1208-1.aspx[quote][b]TheSQLGuru (1/6/2009)[/b][hr]2 points to add:1) If you are going with dynamic sql, the go completely with it. Constructs such as this: CASE WHEN @Sort=''FirstName DESC'' THEN FirstName END DESC, CASE WHEN @Sort=''FirstName ASC'' THEN FirstName END ASC, CASE WHEN @Sort=''LastName ASC'' THEN LastName END ASC, CASE WHEN @Sort=''LastName DESC'' THEN LastName END DESC, CASE WHEN @Sort=''ContactID ASC'' THEN ContactId END ASC, CASE WHEN @Sort=''ContactID DESC'' THEN ContactId END DESCcan be boiled down to the explicit single statement based on the ACTUAL input value of @sort. 2) more importantly, consider situations where putting all of the output data into the CTE can actually hinder performance. I have seen it work much more efficiently (especially with larger numbers of columns/data types) to simply output the necessary key values for each table involved and then select out the fields from those tables hitting the tables a second time on what will be very efficient index seeks/bookmark lookups.[/quote]SQLGuru,Thanks for taking the time to read through the article. I have a few responses to your statements.1) I proposed the case expression in the order by for 1 reason, to parameterize the dynamic sql. This method does have limitations, but does a better job at allowing query plan reuse and makes the code a little more secure. I agree that it is easier to do this one statement. You just have to weigh security vs maintainability.2) I agree with this statement. The article depicted a core method for pagination, using SQL Server 2005. There are a lot of different ways to performance tune the supplied code. It is my hope that those who read this article will take away a fundamental approach to achieving a pagination solution, which does not depend on canned application code.Tue, 06 Jan 2009 10:25:32 GMTAdam Haines