Data Warehousing and Data Science

12 November 2010


Filed under: Analysis Services — Vincent Rainardi @ 7:39 pm

To get the number or rows in a table we usually do:


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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: