All Tips & Questions
Tip of the day
Retrieve accurate row count for table
|
1
|
SELECT COUNT(*) FROM Table_Name
|
Performs a full table scan. Slow on large tables.
|
|
2
|
SELECT CONVERT(bigint, rows)
FROM sysindexes
WHERE id = OBJECT_ID('Table_Name')
AND indid < 2
|
Fast way to retrieve row count. Depends on statistics and is inaccurate.
Run DBCC UPDATEUSAGE(Database) WITH COUNT_ROWS, which can take significant time for large tables.
|
|
3
|
SELECT CAST(p.rows AS float)
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
AND p.index_id=idx.index_id
WHERE ((tbl.name=N'Table_Name'
AND SCHEMA_NAME(tbl.schema_id)='dbo'))
|
The way the SQL management studio counts rows (look at table properties, storage, row count). Very fast, but still an approximate number of rows.
|
|
4
|
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Table_Name')
AND (index_id=0 or index_id=1);
|
Quick (although not as fast as method 2) operation and equally important, reliable.
|
- by
Question of the day
What is the difference between select count(*) and count(1) in sql server?
There is no difference.
Select Count(*) from Table_Name
Select Count(1) from Table_Name
It is very common perception that the Count(1) perform better compared to Count(*), however it is not the case. If you test by looking at the execution plan, you will see same action being performed by both the commands and same number of rows being scanned. The time taken may be slightly different interms of CPU usage for count(*) , but is almost same as count(1).
Same IO, same plan, the works
- by
We are inviting Tips & Questions from you.
Click Here
to share your valuable Tips & Questions on this website.
- Your Tips & Questions will be displayed on website, after validating the information
you provided.
- Tips & Questions will be refreshed on every day 12:00 AM IST.