Just a quick tidbit today, but a very useful one.
Ever need to do SELECT Count(*) on a very large table? It can take ages.
Instead, use sp_spaceused
EXEC sp_spaceused 'MyTable'
It returns you a useful result set like
| name | rows | reserved | data | index_size | unused |
| MyTable | 19690290 | 13637488 KB | 3001688 KB | 9986416 KB | 649384 KB |
Not only does it also give you extra useful information about the table, but it comes back virtually instantly.
Columns:
name – name of the object requested
rows – the number of rows in the table (or service broker queue)
reserved – the total reserved space for the object
data – amount of space used by the data
index_size – amount of spaced used by indexes in the object
unused – total space reserved for the object but not yet used
You can also use sp_spaceused without any parameters, and it will return you the stats for the whole database.
Frog-Blog Out
Also you can use very old system view:
SELECT rowcnt AS NumberOfRows
FROM sysindexes
WHERE indid IN (0,1) — heap or cluster
AND id = OBJECT_ID(‘YourTableName’)
Thanks Vidas, didn’t know about that one
The problem with using sp_spaceused is that it does not return the schema name in the output and if you need to get the counts for a list of tables with same name belonging to different schema, then you cannot make out which table count it is.
Here are few other ways to get the row counts in SQL Server: http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
Thanks for the link. I tend to use sp_spaceused to get details for a specific schema/table, but yes, if you’re using it to get details on multiple tables then there are better options which include the schema in the result set.