Fastest way to count total number of rows...

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  January 31, 2013 at 9:26 am

  #1583083

  MyDoggieJessie (1/31/2013)

  ---

  Won't this work (slightly different than what's posted)? - can't say I've ever noticed a count not equaling a select COUNT on a table directlySELECT ps.row_count

  FROM sys.indexes AS i

  INNER JOIN sys.objects AS o ON

  i.OBJECT_ID = o.OBJECT_ID

  INNER JOIN sys.dm_db_partition_stats AS ps ON

  i.OBJECT_ID = ps.OBJECT_ID

  AND i.index_id = ps.index_id

  WHERE

  i.index_id < 2

  AND o.is_ms_shipped = 0

  AND o.object_id = OBJECT_ID(RTRIM('TableName'))

  As I keep trying to say. . . it should work, but it's not guaranteed. It's an approximation.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Dave62

  SSCertifiable

  Points: 7459

  More actions

  January 31, 2013 at 9:39 am

  #1583088

  GilaMonster (1/31/2013)

  ---

  That's the rowmodctr column, not rows.

  Rowmodctr = row modification counter, number of rows changed since that statistic was last updated. Set to 0 when the index is rebuilt or stats updated. Replaced by the colmodctr in SQL 2005 and above.

  The column 'Rows' is the total number of rows in the index.

  Either way, the fact that sysindexes may not be in future versions is enough for me not to use it. 😎