Alternative to "SELECT COUNT(*)" to get the number of rows in a table

I do quite a bit of data integration, so "SELECT COUNT(*)" is a favorite of mine when refreshing data to determine the amount of time remaining before the refresh will complete. With small tables, this isn't a problem. Slap a (nolock) hint on it, and off ya go. With large tables (millions of records) this can be a problem. Anyone working with larger tables knows that they are far less forgiving than smaller tables and require experience, finess, luck, and clairvoyance to maintain performance. This holds true for the old "COUNT(*)".

Fortunately, SQL keeps a row count in sysindexes. It's not updated with every record, but it will give you an approximate count. Most times, that's all I really need.

So, instead of:

select count(*) from PurchaseOrderDetail (nolock)

Use:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('PurchaseOrderDetail') AND indid < 2

If you absolutely have to have up to the millisecond reporting on the number of rows, you can always run DBCC UpdateUsage to update the sysindexes table. However, if you're doing that, you're probably better off just using count(*).