How can I count a row in a table can anyone out there help me?
I made a mistake in the previous message.

Regards,


CarlWalden

__________________
CarlWalden

If all you want is the number of rows in the table :

Select count(*) from TableA

If you want the number of a specific row, you're out of luck because SQLserver (or any Relational db) doesn't have the notion of row numbers

HTH

I would use

Select count(primarykeycolumnname) from TableA..

This gets rid of the performance hit you take when using a * in a query.

Hal Levy
Daddyshome, LLC

Actually, there is no performance hit here. All you are doing is asking the query engine to count rows, and return the answer as a singleton. using '*' in queries slows things down mainly because of the marshalling and bandwidth overheads incurred with moving all the extra data around.

Chris,

That goes against everything I have ever been told.

I was told that the query engine, when getting a * request, will go out and get a listing of all the columns first and then look at what has to be done. So, It would get the * and then go "oh, I need to count rows".. Rather than know it's counting rows and not do the lookup for the columns. Perhaps I was misinformed or something changed in later versions of SQL server.

EDIT: So I just ran the query in Query Analyzer and got an execution plan and it certainly seems like it knows it's counting and does not take the performance hit I was expecting. I was missinformed.

Hal Levy
Daddyshome, LLC

As you've seen, COUNT(*) is special cased by the optimizer. The asterisk in COUNT(*) doesn't mean 'all the columns' but rather 'all the rows'. It means 'all the columns' only when it is all by itself or prepended with a table name or alias.

COUNT(*) is actually quite fast, as the number of rows in a table is a statistic that is readily available to the optimizer.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

You can use
select rows from sysindexes where id = object_id('TableA') and indid<2


Note that TableA is enclosed within quotation marks ('TableA')


Preethi
(G.R.Preethiviraj Kualsingham)

Preethi
(G.R.Preethiviraj Kulasingham)