Multiple ways to count the number of records in a table of SQL server:
1. select count(*) or select count(id) or select count (distinct id) : if we want the exact number but it is an additional overhead as it does table scan or index seek depending on the column chosen.
2. Sysindexes: The columns that need be taken into account are id, indid, rowcnt
indid can take various values as shown below:
0 – no clustered index
1 – CI
indid > 1 and indid < 255 – non CI
255 – text/image datatypes
Query to find the number of rows using sysindexes:
[cc lang=”SQL”]
SELECT so.[name] as [table name]
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = ‘U’
AND so.[name] != ‘dtproperties’
ORDER BY so.[name]
[/cc]
If the statistics are not updated, above number might not be exact. So update statistics using
[cc lang=”SQL”]
dbcc updateusage
[/cc]
0 Comments.