Using Rowcount in T-SQL
Here's a tip for limiting the number of rows returned from a query. Now I know you think "that's easy - just use TOP." Ah... but what if TOP is not an option? Consider this example. You have a reporting stored procedure that you want to use to return multiple results sets to your Coldfusion Page. Easy - right?
First, here is our simple procedure.
CREATE PROCEDURE [dbo].[myReport]
WITH RECOMPILE AS
SET NOCOUNT ON
SELECT count(s.saleID) AS total, a.AuthorName
FROM authors a JOIN sales s ON
a.author_id = s.author_ID
GROUP BY a.AuthorName
ORDER BY total desc
SELECT sum(s.prive) AS totalRevenue, s.Title
FROM sales
GROUP BY s.Title
ORDER BY totalRevenue desc
SET NOCOUNT OFF
</cfquery>
<cfprocresult name="Authors" resultset="1">
<cfprocresult name="Revenue" resultset="2">
</cfstoredproc>
Your first attempt (like mine) might be to use "TOP". I altered my SP as follows:
CREATE PROCEDURE [dbo].[myReport]
(
@topNum int
)
WITH RECOMPILE AS
SET NOCOUNT ON
SELECT TOP @topNum count(s.saleID) AS total, a.AuthorName
FROM authors a JOIN sales s ON
a.author_id = s.author_ID
GROUP BY a.AuthorName
ORDER BY total desc
.....
</cfquery>
The Fix
The fix is to use the ROWCOUNT setting. The command is simply "SET ROWCOUNT N" where N is the number of rows you want to return. Setting it once at the top of a stored procedure causes any query within that procedure to stop processing as soon as it has a complete results sets with N rows that matches the WHERE and ORDER BY criteria. So my New SP code looked like this:
CREATE PROCEDURE [dbo].[myReport]
(
@topNum int
)
WITH RECOMPILE AS
SET NOCOUNT ON
SET ROWCOUNT @topNum
SELECT count(s.saleID) AS total, a.AuthorName
FROM authors a JOIN sales s ON
a.author_id = s.author_ID
GROUP BY a.AuthorName
ORDER BY total desc
.....
</cfquery>
<cfstoredproc datasource="myDSN" procedure="myReport" >
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="#url.topNum#" dbvarname="@topNum"/>
<cfprocresult name="Authors" resultset="1"/>
<cfprocresult name="Revenue" resultset="2"/>
</cfstoredproc>
I have one other clever use for the ROWCOUNT T-SQL setting. Sometimes when working with a schema I need to pull up a few rows from several tables. For example, when trying to create a complex JOIN query - or a view. I need to eyeball the data and column names. So instead of using a TOP keyword I simply add a rowcount command to the top of my pane in Query analyzer - setting it to 2 or 3 for example. Then I can write several queries and run them together in the lower pane - but each will be limited. It's probably not a huge time saver, but it makes cutting and pasting more reliable because I have less editing to do.
6 Comments
-
Great post! I had not thought about using the ROWCOUNT to limit records returned. I did some checking (asked our DBA) and did a quick search, and found that in SQL Server 2005, you can use a parameter with TOP. I found it here: http://www.databasejournal.com/features/mssql/arti...
One thing to add is that if you are using TOP, you might want to use WITH TIES. For example, using your example, if you bring back the TOP 10 authors based on sales and the 11th record has the same amount as the 10th, the 11th will get cut off. This will happen also if you use the SET ROWCOUNT option. If you use TOP n WITH TIES, it will bring back the 11th, giving the full "story". Which would be good, unless of course your report wanted 10 and only 10 records. -
don't forget that if you're returning multiple recordsets if you want to the first recordset limited to a row count but the ones after not limited you can simply set rowcount to 0 after your limited query to turn it off. -
So what if you have a query of 5000 names and what to select a range on names (for ajax purposes). For example i want to select rows 3000-4000 without having to read the first 2999 records and the last 1000 records.
I wouldn't want the query to spend the time reading those records that I would ultimately not need and not return. -
@Joseph Najar
May be you should create an Index on the column of table so that select is faster when there are 5000 records in the table.
Obviously this does not solve your problem of selecting from in between 3001-4000 records but Indexes really improve performance -
@Joseph: In SQL Server, check out the row_number() function. It'll get you a range of records in the middle of a larger record set. It works like LIMIT and OFFSET in MySQL.
http://msdn.microsoft.com/en-us/library/ms186734.a...
Mark, thanks for the article. I needed to return a 2nd record set from a SP and it only took my 2 minutes to update my code after reading this. -
Hi Mark,
Of course it's not impossible to use TOP so you can get it to work on MSSQL.
Here's how:
<cfquery>
CREATE PROCEDURE [dbo].[myReport]
(
@topNum int
)
WITH RECOMPILE AS
Declare @str varchar(5000)
select @str = 'SELECT TOP ' + rtrim(Convert(varchar(20),@topNum)) +
' count(s.saleID) AS total, a.AuthorName
FROM authors a JOIN sales s ON
a.author_id = s.author_ID
GROUP BY a.AuthorName
ORDER BY total desc'
SET NOCOUNT ON
execute(@str)
SET NOCOUNT OFF
<cfquery>
That way, you get to execute your prepared string without any issues.
God bless.
regards,
Chris Musasizi
Sites to Watch
Aggregators
RSS
