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 (('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.