ColdFusion Muse

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.

<cfquery>
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>
Notice I've left off some boiler plate for quoted_identifiers and ansi_nulls. This Stored Procedure returns 2 results sets. The first grabs the total number of books sold and totals them by author with the best selling authors first. The second totals the revenue per book and returns the highest revenue books first. To call this SP you will need code like this:

<cfstoredproc datasource="myDSN" procedure="myReport">
    <cfprocresult name="Authors" resultset="1">
    <cfprocresult name="Revenue" resultset="2">
</cfstoredproc>
When we run this code we end up with 2 results queries - "Authors" and "Revenue" - in the local scope. In fact, one of the main reasons to use the Cfstoredproc tag is the ability to return multiple results sets. But now we have a problem. You see our book store is really busy. So the results that come back from this query are going to be pretty large. I don't really want to know that I've sold 2 copies of "spitting in the wind" by Jaun A. Hockalugie. I would prefer to see the top 10 results. My manager on the other hand wants to see 25 results. How do I accomplish this in my SP?

Your first attempt (like mine) might be to use "TOP". I altered my SP as follows:

" </>c
f q u e ry>

CREATE PROC<E/DsUyRsEt e[md.bwoe]b.S[ermyvRere>p

ort]
    (
        @topNum <in/tc
o    n)
f
iWgIuTrHa tRiEoCnO>M

PILE 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>
You can see where I was going. I thought I'd throw in a "TOP" command and variablize the number. Clever as I am I could not get this to work. T-SQL will not take a variable after TOP.

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:

< c f q u e r y >
CREATE PROCEDURE <[addbdo ]v.a[lmuyeR=ep"oirntd]e
x    (
    .    h@ttmo"p
N u/m>

i n t
     )

W I T H R E C O M PILE AS

SET NOCOUN<Ta dOdN

vSaElTu ReO=W"CiOiUsNsTt a@rtto.phNtumm

"
S EL/E>

C T c o u n t ( s . s a l e I D ) AS total, a.<Aaudtdh ovraNlaumee
=F"RdOeMf    aauulttho.rass pax "J O/I>
N
s a l e s s O N
        a.author_i</files>
d = s.author<_I/dD
eGfRaOuUlPt DBocYu mae.nAtu>
t
h o r N a m e
ORDER BY total <ddeisrce
c.t.o.r.y.B
    r    
ows<e e/ncafbqlueedr=y">f
The beauty of it is that all of the subsequent queries or results sets returned from the Stored procedure are now limited in this fashion. Now I just have to include the input param in my Coldfusion code.
< C fparam name="ur<lf.itloepsN>
u m "
d e f a u l t = " 1 0 " />

<cfstoredpr<occl edarat a/s>o
u r c e = " m y D S N " p r ocedure="myRepor<tad"d >
v    al<ue=c"
fipnrdoecxp.acrfamm" cf/s>
q l t y p e = "
C F _ S Q L _ I NTEGER" value="#u<rald.dt ovpaNluume#="" idnbdveaxr.nhatmmel="" @t/o>p
N u m" / >

     < c f p r o c result name="Auth<oardsd" vraelsuuel=t"seDte=f"a1u"lt/.>
h    tm<"
/c>
f
p r o c r e s u l t n a m e = "Revenue" results<eatd=d "v2a"l/u>e
="D<efa/uclfts.taosrped"p r/o>c
>
So the code above returns 10 rows for each results set.

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.

Comments
johann's Gravatar 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.
# Posted By johann | 9/7/07 1:18 PM
john b's Gravatar 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.
# Posted By john b | 9/7/07 1:18 PM
Joseph Najar's Gravatar 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.
# Posted By Joseph Najar | 10/28/07 8:58 PM
Nirav Shah's Gravatar @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
# Posted By Nirav Shah | 6/24/08 1:05 PM
Adrian J. Moreno's Gravatar @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.
# Posted By Adrian J. Moreno | 10/15/08 10:48 AM
Chris Musasizi's Gravatar 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
# Posted By Chris Musasizi | 8/6/09 5:31 AM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.