Counting Rows without Identity

Question

Counting Rows without Identity

aureolin

Hall of Fame

Points: 3520
More actions
March 7, 2008 at 10:06 am

#69615

This is a situation that I'm faced with on a fairly regular basis: I need to have one of the fields in my query results contain a sequential count of the rows. I know (we all know) that I can do the following:
select
IDENTITY(int, 1, 1) as counter,
other.columns
into #temptable
from some.table
but I find having to use temp tables is often inconvenient for what I'm trying to do. The question is: Is there a way to count query result rows (say using a numbers table?) without using Identity and a temp table?
Steve G.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Yes... there is another way using a triangular join... but it's horribly slow and relies on a unique or primary key and an order by...

To generate a running count in SQL Server 2000, the use of a Temp table is absolutely the fastest and most effective way to accomplish the task.

Here's the link to an article about triangular joins... if you really want to use it (which I think is the worst thing you could do), sub a "1" for the amount in the running balance calculation...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aureolin Hall of Fame Points: 3520 More actions · Answer 2

So, then the answer is: "You can do it but it's not worth it." I'll take that as the final word on this and go back to using temp tables. They may be a pain but they are fast. 😀

Thanks for the answer and the link!

Steve G.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Correct... not worth it. And thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)