To get the number or rows in a table we usually do:
SELECT COUNT(*) FROM VERY_BIG_TABLE
For a small table (<100k rows) it’s fine but if it’s a very big table it takes a long time.
A table with 250 million rows , unpartitioned, no index, 40 columns: it takes 2 mins 18 seconds to do select count(*). (SQL Server with dual processors, 32GB RAM, single RAID5).
This one only takes 1 second:
select sum(P.rows) from sys.partitions P with (nolock) join sys.tables T with (nolock) on P.object_id = T.object_id where T.Name = 'VERY_BIG_TABLE' and index_id = 1
Would be very useful to memorise that one! As in data warehousing we deal with lots of very big tables.
Well the “nolock” hint is totally optional. I was just being too careful there. No need for it really as they are system tables which are not modifiable by users.