SQL Server Performance Forum – Threads Archive

Alternates for count(*) function

Just wanted to know if there is any alternate command(s) for getting the record count of a table other than select count(*) from MyTable Thanks

No you can access it from system table but not reliable if statistics are out of date.
For correct count always query select count(cols) on table
which has no alternative.

There is one, but it is not terribly reliable in a production system, as it reads a the rowcnt column from sysindexes – which is not necessarily up-to-date. Do a search on "rowcnt sysindexes" and you should find many posts.

After running DBCC UpdateUsage, run Select object_name(id) as table_name, rows
from sysindexes where indid<2 Madhivanan Failing to plan is Planning to fail

give select count(columnname) from mytable Thanks,
sunder.

quote:Originally posted by bugatha.hema give select count(columnname) from mytable Thanks,
sunder.

Not necessarily correct.
CREATE TABLE #t (c1 NVARCHAR(36) DEFAULT NEWID()) INSERT INTO #t DEFAULT VALUES
INSERT INTO #t DEFAULT VALUES
INSERT INTO #t SELECT NULL SELECT COUNT(c1) AS Incorrect, COUNT(*) AS Correct
FROM #t DROP TABLE #t
Incorrect Correct
———– ———–
2 3 (1 row(s) affected) Warning: Null value is eliminated by an aggregate or other SET operation. COUNT is "NULL-aware". Using COUNT(col) NULLs are ignored, using COUNT(*) NULLs are included. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

This may help. http://toponewithties.blogspot.com/2004/08/let-us-count-them.html
Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Not exactly accurate but you could also do sp_spaceused which is same as getting the rows from sysindexes table widh indid = 1 ***********************
Dinakar Nethi
SQL Server MVP
***********************

SELECT SUM(1) FROM [Table] David Yegidis

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by RecklessJedi SELECT SUM(1) FROM [Table] David Yegidis <hr height="1" noshade id="quote"></blockquote id="quote"> < grin > That’s a quite nice "visually" different solution. However, behind the scenes it is doing the same as COUNT() with just another aggregation. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

could be little slower than count(*) Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

]]>