SQL This!

This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM , but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data. Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation. In Sql Server 2000 the command looks like this: Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID( TableName) AND indid While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.) In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = TableName note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by...

While you work on your newest indexes / table design it is often necessary to clean up the cache on Sql Server to ensure that your changes are actually making a difference. Typically to clear the cache you'll want to run, DBCC DROPCLEANBUFFERS. If you are also executing your test via sprocs you'll want to clear that cache as well to do that just run, DBCC FREEPROCCACHE. This ensures that you are always starting from the same point at each run. If you do not clear the cache between tests you can't be sure that your time results are accurate.