Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Mar 20, 2009 05:49 AM by vora_bhaumik
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),
INSERT INTO @mytable (style, fabric, team, color)
SELECT style, fabric, team, color FROM MYSKUSTABLE
select style, fabric, team, color from @mytable where rownum >= @startRowIndex AND rownum <= (@startRowIndex + @maximumRows - 1);
"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 :)
Nov 10, 2006 08:08 PM|LINK
DECLARE @mytable TABLE
( 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
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.
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;
select * from mytable where mykeyfield >= @mykeyfield;
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.
Nov 10, 2006 08:30 PM|LINK
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.
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.
Nov 10, 2006 08:40 PM|LINK
Nov 10, 2006 08:46 PM|LINK
Nov 10, 2006 09:10 PM|LINK
Caddre. Table variables are only temp tables with a different scope, as can be seen here:
And a proof that this is correct, a screenshot from my own computer, taken while the query is executing :
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.
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.