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,
quote:Originally posted by bugatha.hema give select count(columnname) from mytable Thanks,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
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. —
Microsoft SQL Server MVP
This may help. http://toponewithties.blogspot.com/2004/08/let-us-count-them.html
Roji. P. Thomas
Microsoft SQL Server MVP
Not exactly accurate but you could also do sp_spaceused which is same as getting the rows from sysindexes table widh indid = 1 ***********************
SQL Server MVP
SELECT SUM(1) FROM [Table] David Yegidis
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by RecklessJedi</i><br /><br />SELECT SUM(1) FROM [Table]<br /><br />David Yegidis<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />< 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=’‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />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…"