Get Total Rows.
in [Programming SQL]
|
Prev: Row_Number() Sorting
Next: Remote Access Steps
From: tshad on 29 Mar 2010 02:16 "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:5D17E64E-2FD2-44B6-AFA9-9CFA25CADC51(a)microsoft.com... >> If you are doing paging you need to pass back the total number of rows of >> the query. > > Why? Because that is how .net does it when using SQL DataSources. You give it the SP that gives you the data and also one that gets the Total RowCount. The one that gives you the data, you also give it the page that you want and it passes back only what you need - which is what we use ROW_NUMBER() for. But the @@ROWCOUNT would be the number of the page not the total number of the result set without pageing. .Net uses that to set up the number of pages the total set contains (including next, last, previous...). Thanks, Tom > You can also do pagination using only First, Next, Previous and Last > functionality. Pagination using keys rather than row numbers will perform > better, assuming appropriate indexing. If you want to display the total > number of rows, you can run a separate COUNT query when the first page is > displayed. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "tshad" <t(a)dslextreme.com> wrote in message > news:OCPlq2TzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >> If you are doing paging you need to pass back the total number of rows of >> the query. >> >> What is the best way to do that? >> >> What one person does is do the SELECT/INTO a temp table to get the total >> number of rows. >> >> Then he does a "SET ROWCOUNT @RowNumber", then does a SELECT * ... >> >> The problem here is that he is using doing an insert into a temp table >> with 2 selects (one to insert and one to pass back the results). >> >> What I do is have 2 functions (could do it in one with a parameter) that >> have identical queries except >> 1) in one I have all the columns I want to return and in the other I have >> a SELECT using the ROW_NUMBER() function. I have to do it this way >> because the number of @@ROWCOUNT will be equal to the number of rows from >> your "WHERE ResultSetNumber BETWEEN @StartRow and @EndRow" clause not the >> number of the total result set. >> 2) in the other I have a "SELECT Count(*)" with the rest of the query the >> same as #1. >> >> Is there a better way to do this? >> >> Thanks, >> >> Tom >>
From: tshad on 29 Mar 2010 02:26 "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:hYGdndg2a5rr8TDWnZ2dnUVZ_gIAAAAA(a)speakeasy.net... > COUNT() OVER() was added in SQL Server 2005, together with the ranking > functions. The OVER clause supports the other aggregate functions too > (SUM, MIN, MAX, AVG). > > If you are talking about logical reads then it may not be an issue. See > more here: > http://www.sql-server-performance.com/articles/per/logical_reads_p1.aspx > In my case, I have a SP that is doing 340,000 reads to return 240 records. So I am looking at anything cut this down. It is doing a few JOINs and in a view it is doing 3 Left JOINs. So I am looking to see if there is a way to change those to INNER JOINS. Thanks, Tom > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Erland Sommarskog on 18 Apr 2010 18:05
tshad (tfs(a)dslextreme.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message >> I wouldn't say that 340400, 339900 or 339110 are all the same to me. >> > ??? > They aren't the same. Not sure what you mean here. Damn! One of my lost "not" reappeared in the wrong place. The sentence should read "I would say that 340400, 339900 or 339110 are all the same to me.". That is, it's not a difference worth bothering about. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |