Hi, I just wanted to share a little clarification to a common misconception surrounding T-SQL (the SQL flavor of SQL Server. So this post only applies to SQL Server!), which is useful when it comes to paging, but is useful across the board, really.
Background: The reason why I'm posting this because all the tutorials make it look like only stored procedures can manage this. On top of that, I recently had a project manager and Microsoft certified solution architect with 10+ years in the field argue
with me about it, so I think it should be in the FAQ :)
Ok, here it goes: You do NOT need stored procedures to use advanced T-SQL features.
This includes, but is not limited to, declaring variables, using temporary tables, declaring cursors, using conditional statements, combining multiple queries, and so on.
For example, here is my little (very ghetto) T-SQL used to page through some demo table I made (it is not the most efficient way, but you could use anything!):
DECLARE @mytable TABLE
(
rownum int IDENTITY(1,1),
style NVARCHAR(50),
fabric NVARCHAR(50),
team NVARCHAR(50),
color NVARCHAR(50)
)
INSERT INTO @mytable (style, fabric, team, color)
SELECT style, fabric, team, color FROM MYSKUSTABLE
BEGIN
select style, fabric, team, color from @mytable where rownum >= @startRowIndex AND rownum <= (@startRowIndex + @maximumRows - 1);
END
"But, what about the stored procedures parameters" you say? Simple: Any variable that is not declared (like you can do using the DECLARE statement) will be seen as a parameter. Just use the usualy SqlCommand's parameter collection to add them. In the above
scenario, you would have to add @startRowIndex and @maximumRows
And yes, this will work with the Dataset wizard, here is how:
1) Make a new table adapter. to add a new query (not a stored procedure!) by picking the "Use SQL Statements" option. If you already have a tableadapter, just right click on it and select add query.
2) Of the next option, pick whichever applies. In this case "Select Which Returns Rows"
3)When you click next, simply write (or better yet, paste) your T-SQL like you would in a stored procedure. Obviously remove the "create..." statement and the parameter declaration (As stated aboves, any variable that is not declared with the declare statement
is basically a parameter)
4)Click next, leave the check box to generate the methods, next again
5)The Dataset wizard will complain that what you just did is not supported. Doesn't matter, it will work anyway: Hit finish.
6) Now we have a problem: The dataset wizard didn't recognise our parameters (undeclared variables). Lets fix that: In the dataset properties (on the right of the screen when the dataset is selected), click on the button to expand the Parameters collection
in design time. Add your parameters there. It is important that when you name them that you add the @ in front, so like above, @maximumRows as the parameter name.
7) Thats it. Everything will work fine, and you can now use this typed dataset method to do anything you would normally do (like Custom Paging) with your stored procedures, and tap into all the power of T-SQL, without having to rely on said stored procedures.
Now, of course, using the Typed Dataset wizard, it is a bit quirky and not so cool. However, there are many situations where Stored Procedures are not desirable, or one wants to keep their SQL in line (let say, in the case of an OR Mapper, or using a custom
Dynamic SQL engine), and often (like in my interview with that microsoft certified architect!) people brush that option off, because they feel they will lose the power of T-SQL (like cursors, temporary tables, server side paging). But as you can see, in the
scenarios where keeping SQL in your code is preferable, you do not lose any power, and you can still do Custom Paging, without stored procedures!
style NVARCHAR(50),
fabric NVARCHAR(50),
team NVARCHAR(50),
color NVARCHAR(50)
)
( rownum int IDENTITY(1,1),)
I did not know you can create such a crude cursor without CLOSE statement or DEALLOCATE statement. Your code is invalid by Microsoft documentation because IDENTITY is int in the BOL(books online) and ROW_Number cannot be used with int. The above rownum
is creating rows not associated to column because IDENTITY is a property by ANSI SQL definition. You don't have to use a database when you do it comes with the relational model.
The code below is valid implementation not because of stored proc but the relational ORDER BY verifies the equality of the different data types as required by the data structures sort and search algorithms
#2) This method can be seen as part of Microsoft practices document
#3) This simply uses tempdb, and is about 40% faster than cursors (thus showing there isn't any actual cursor declaration there, it is only a temp db )
#4) I used this on SQL Server 2000, thus rownum is not a keyword. This was a simple field name. You are correct however, in 2005 I beleive it would cause problems. In this case, it was nothing but a field name. Sorry for the confusion. My technique was
for a database where The RowNum method is not available, and it was just an example of a T-SQL statement. I was not meant to explain actual paging queries.
#5) The correct statement for SQL Server 2000 (since rownum is not available) for maximum performance would be the following, for anyone reading this:
SET ROWCOUNT @startIndex
DECLARE @mykeyfield int;
select @mykeyfield = mykeyfield from mytable where ......;
SET ROWCOUNT @pagesize;
BEGIN
select * from mytable where mykeyfield >= @mykeyfield;
END
Give or take for syntax errors and stuff. This is about 10 times faster than the methods using memory tables or nested queries. If you are on SQL Server 2005, obviously, use ROWNUM.
The point though, is that on SQL Server 2005 people are already used to the fact that you can do paging with client side SQL, so there was absolutely no point in talking about it.
Your code works because you are spinning rows powered by IDENTITY, you are running a flat file within a table as a local variable that cannot be dropped a # local temp table goes out of scope and cursors are closed and deallocated your code will persist.
By Microsoft's own documentation, table temporary variables are exactly the same thing as # local temp tables, except that their scope is the same as a variable (they are destroyed faster). For all practical purpose and performance, it is exactly the same
thing as a #temp table. For SQL Server 2000, the 2 methods I displayed are the fastest, most efficient ways of doing custom paging, as rownum is not available.
That is the crudest code I have read, a temp table uses its own database to store anything used with it, you are paging with a flat file in your local variable it works but for how long because tempdbs are very closely managed by DBA and other database experts
nobody will know about your crude code in their system.
However the last method using SET ROWCOUNT is by far the best, as it takes advantage of SQL Server's optimizer to greatly reduce the amount of rows scanned.
I have not seen the above in any of your references and I know it will not run in SQL Server 2005 because it is invalid. Your IDENTITY property is started with a row but IDENTITY is just a property not a column because there are no fields in a database
just rows and columns.
shados
Star
12285 Points
2229 Posts
Custom Paging without stored procedures
Nov 06, 2006 03:50 AM|LINK
Hi, I just wanted to share a little clarification to a common misconception surrounding T-SQL (the SQL flavor of SQL Server. So this post only applies to SQL Server!), which is useful when it comes to paging, but is useful across the board, really.
Background: The reason why I'm posting this because all the tutorials make it look like only stored procedures can manage this. On top of that, I recently had a project manager and Microsoft certified solution architect with 10+ years in the field argue with me about it, so I think it should be in the FAQ :)
Ok, here it goes: You do NOT need stored procedures to use advanced T-SQL features.
This includes, but is not limited to, declaring variables, using temporary tables, declaring cursors, using conditional statements, combining multiple queries, and so on.
For example, here is my little (very ghetto) T-SQL used to page through some demo table I made (it is not the most efficient way, but you could use anything!):
DECLARE @mytable TABLE
(
rownum int IDENTITY(1,1),
style NVARCHAR(50),
fabric NVARCHAR(50),
team NVARCHAR(50),
color NVARCHAR(50)
)
INSERT INTO @mytable (style, fabric, team, color)
SELECT style, fabric, team, color FROM MYSKUSTABLE
BEGIN
select style, fabric, team, color from @mytable where rownum >= @startRowIndex AND rownum <= (@startRowIndex + @maximumRows - 1);
END
"But, what about the stored procedures parameters" you say? Simple: Any variable that is not declared (like you can do using the DECLARE statement) will be seen as a parameter. Just use the usualy SqlCommand's parameter collection to add them. In the above scenario, you would have to add @startRowIndex and @maximumRows
And yes, this will work with the Dataset wizard, here is how:
1) Make a new table adapter. to add a new query (not a stored procedure!) by picking the "Use SQL Statements" option. If you already have a tableadapter, just right click on it and select add query.
2) Of the next option, pick whichever applies. In this case "Select Which Returns Rows"
3)When you click next, simply write (or better yet, paste) your T-SQL like you would in a stored procedure. Obviously remove the "create..." statement and the parameter declaration (As stated aboves, any variable that is not declared with the declare statement is basically a parameter)
4)Click next, leave the check box to generate the methods, next again
5)The Dataset wizard will complain that what you just did is not supported. Doesn't matter, it will work anyway: Hit finish.
6) Now we have a problem: The dataset wizard didn't recognise our parameters (undeclared variables). Lets fix that: In the dataset properties (on the right of the screen when the dataset is selected), click on the button to expand the Parameters collection in design time. Add your parameters there. It is important that when you name them that you add the @ in front, so like above, @maximumRows as the parameter name.
7) Thats it. Everything will work fine, and you can now use this typed dataset method to do anything you would normally do (like Custom Paging) with your stored procedures, and tap into all the power of T-SQL, without having to rely on said stored procedures.
Now, of course, using the Typed Dataset wizard, it is a bit quirky and not so cool. However, there are many situations where Stored Procedures are not desirable, or one wants to keep their SQL in line (let say, in the case of an OR Mapper, or using a custom Dynamic SQL engine), and often (like in my interview with that microsoft certified architect!) people brush that option off, because they feel they will lose the power of T-SQL (like cursors, temporary tables, server side paging). But as you can see, in the scenarios where keeping SQL in your code is preferable, you do not lose any power, and you can still do Custom Paging, without stored procedures!
Hope that helps and clear some stuff up :)
-Francois
Custom Paging Stored Procedures T-SQL
Caddre
All-Star
26581 Points
5308 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:08 PM|LINK
DECLARE @mytable TABLE
(
style NVARCHAR(50),
fabric NVARCHAR(50),
team NVARCHAR(50),
color NVARCHAR(50)
)
( rownum int IDENTITY(1,1),)
I did not know you can create such a crude cursor without CLOSE statement or DEALLOCATE statement. Your code is invalid by Microsoft documentation because IDENTITY is int in the BOL(books online) and ROW_Number cannot be used with int. The above rownum is creating rows not associated to column because IDENTITY is a property by ANSI SQL definition. You don't have to use a database when you do it comes with the relational model.
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
http://msdn2.microsoft.com/en-us/library/aa258839(SQL.80).aspx
The code below is valid implementation not because of stored proc but the relational ORDER BY verifies the equality of the different data types as required by the data structures sort and search algorithms
http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx
There are other options custom binary searched collections, a list must be compared with ICOMPARER and SORTED before Binary search.
"Those who practice without theory are like sailors without rudder and compass" Chris Dates relational algebra expert.
http://en.wikipedia.org/wiki/Relational_model
Gift Peddie
shados
Star
12285 Points
2229 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:25 PM|LINK
#1) There isn't a single cursor in my code.
#2) This method can be seen as part of Microsoft practices document
#3) This simply uses tempdb, and is about 40% faster than cursors (thus showing there isn't any actual cursor declaration there, it is only a temp db )
#4) I used this on SQL Server 2000, thus rownum is not a keyword. This was a simple field name. You are correct however, in 2005 I beleive it would cause problems. In this case, it was nothing but a field name. Sorry for the confusion. My technique was for a database where The RowNum method is not available, and it was just an example of a T-SQL statement. I was not meant to explain actual paging queries.
#5) The correct statement for SQL Server 2000 (since rownum is not available) for maximum performance would be the following, for anyone reading this:
SET ROWCOUNT @startIndex
DECLARE @mykeyfield int;
select @mykeyfield = mykeyfield from mytable where ......;
SET ROWCOUNT @pagesize;
BEGIN
select * from mytable where mykeyfield >= @mykeyfield;
END
Give or take for syntax errors and stuff. This is about 10 times faster than the methods using memory tables or nested queries. If you are on SQL Server 2005, obviously, use ROWNUM.
The point though, is that on SQL Server 2005 people are already used to the fact that you can do paging with client side SQL, so there was absolutely no point in talking about it.
shados
Star
12285 Points
2229 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:30 PM|LINK
Caddre
All-Star
26581 Points
5308 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:34 PM|LINK
Your code works because you are spinning rows powered by IDENTITY, you are running a flat file within a table as a local variable that cannot be dropped a # local temp table goes out of scope and cursors are closed and deallocated your code will persist.
Gift Peddie
shados
Star
12285 Points
2229 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:38 PM|LINK
By Microsoft's own documentation, table temporary variables are exactly the same thing as # local temp tables, except that their scope is the same as a variable (they are destroyed faster). For all practical purpose and performance, it is exactly the same thing as a #temp table. For SQL Server 2000, the 2 methods I displayed are the fastest, most efficient ways of doing custom paging, as rownum is not available.
shados
Star
12285 Points
2229 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:40 PM|LINK
http://www.4guysfromrolla.com/webtech/042606-1.shtml
Caddre
All-Star
26581 Points
5308 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 08:46 PM|LINK
Gift Peddie
shados
Star
12285 Points
2229 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 09:10 PM|LINK
Caddre. Table variables are only temp tables with a different scope, as can be seen here:
http://www.odetocode.com/Articles/365.aspx
And a proof that this is correct, a screenshot from my own computer, taken while the query is executing :
http://www.blackphoenix.net/images/temptable.png
============
However the last method using SET ROWCOUNT is by far the best, as it takes advantage of SQL Server's optimizer to greatly reduce the amount of rows scanned.
Caddre
All-Star
26581 Points
5308 Posts
Re: Custom Paging without stored procedures
Nov 10, 2006 09:22 PM|LINK
( rownum int IDENTITY(1,1),)
I have not seen the above in any of your references and I know it will not run in SQL Server 2005 because it is invalid. Your IDENTITY property is started with a row but IDENTITY is just a property not a column because there are no fields in a database just rows and columns.
Gift Peddie