January 31, 2013 at 7:47 am
Hi,
What's the best way of doing a complete row count on a table?
SELECT COUNT(*) FROM MovimentosFuncionarios
SELECT SUM(SP.rows) AS [total_rows]
FROM sys.partitions SP
WHERE SP.object_id = OBJECT_ID('MovimentosFuncionarios') AND SP.[index_id] = 1
is the sys.partitions table always "reliable" as for the number of rows?
Thanks,
Pedro
January 31, 2013 at 8:00 am
PiMané (1/31/2013)
is the sys.partitions table always "reliable" as for the number of rows?
It's reliable as an approximate count for an individual table or index. You may be able to get more accurate results by running "DBCC UPDATEUSAGE" with the COUNT_ROWS option just before querying.
January 31, 2013 at 8:05 am
I'm trying to determine the fastest way, and as far as I tested, the sys.partitions is the fastest but just don't know how reliable the info is...
DBCC UPDATEUSAGE is even slower than SELECT COUNT(*)....
Pedro
January 31, 2013 at 8:17 am
PiMané (1/31/2013)
I'm trying to determine the fastest way, and as far as I tested, the sys.partitions is the fastest but just don't know how reliable the info is...DBCC UPDATEUSAGE is even slower than SELECT COUNT(*)....
Pedro
As I said, it's an approximate row count so when used as such it is very reliable. If you're asking whether or not it will ever not be accurate, then yes it will sometimes be different to the actual row count. I mentioned DBCC UPDATEUSAGE because it corrects row count inaccuracies in the catalog views.
January 31, 2013 at 8:26 am
This is the fastest and most reliable way I have found.
SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.MovimentosFuncionarios')
AND (index_id=0 or index_id=1);
Enjoy!
January 31, 2013 at 8:31 am
Thanks,
Pedro
January 31, 2013 at 8:32 am
Dave62 (1/31/2013)
This is the fastest and most reliable way I have found.
SELECT SUM (row_count)FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.MovimentosFuncionarios')
AND (index_id=0 or index_id=1);
Enjoy!
Still an approximate row count.
January 31, 2013 at 9:01 am
i thought you had to get the actual counts from sys.indexes; since the index must have a heap/PK index for every table, teh row counts are materialized exactly correct there:
SELECT so.[name] as
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
Lowell
January 31, 2013 at 9:11 am
Lowell (1/31/2013)
i thought you had to get the actual counts from sys.indexes; since the index must have a heap/PK index for every table, teh row counts are materialized exactly correct there:
No, they're not.
sysindexes is deprecated, as is sysobjects, will be removed, should not be used. sys.indexes does not have a row count. sysindexes row count is known to be inaccurate, potentially very inaccurate. sys.partitions should be transactionally correct. Should.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2013 at 9:12 am
Lowell (1/31/2013)
i thought you had to get the actual counts from sys.indexes; since the index must have a heap/PK index for every table, teh row counts are materialized exactly correct there:
SELECT so.[name] as
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
The sysindexes table may not be as reliable because the accuracy will be determined by when the statistics have been updated. In addition, Microsoft says sysindexes will be removed in future versions.
January 31, 2013 at 9:14 am
Lowell (1/31/2013)
i thought you had to get the actual counts from sys.indexes; since the index must have a heap/PK index for every table, teh row counts are materialized exactly correct there:
SELECT so.[name] as
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
You've written down sys.indexes (which doesn't have a row count) then queries sys.sysindexes (which is deprecated).
I'm fairly sure that the only way to guarantee an accurate count is to directly query the table. The catalog and dynamic management views are normally correct, but not guaranteed - they're documented as "approximate" counts.
January 31, 2013 at 9:15 am
Dave62 (1/31/2013)
The sysindexes table may not be as reliable because the accuracy will be determined by when the statistics have been updated.
The row count in sysindexes is not related to statistics updates.
CREATE DATABASE testingindexes
GO
ALTER DATABASE testingindexes SET AUTO_CREATE_STATISTICS OFF
ALTER DATABASE testingindexes SET AUTO_UPDATE_STATISTICS OFF
GO
USE testingindexes
GO
CREATE TABLE useless (number int)
INSERT INTO useless
SELECT object_id + column_id FROM msdb.sys.columns AS c
GO
SELECT * FROM sys.stats AS s WHERE object_id = OBJECT_ID('useless') -- no rows, no statistics created or updated on that table
SELECT * FROM sysindexes WHERE id = OBJECT_ID('useless') -- rowcount of 2103
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2013 at 9:21 am
GilaMonster (1/31/2013)
The row count in sysindexes is not related to statistics updates.
This quote from the MSDN page I referenced seems to indicate some relation.
"Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table."
January 31, 2013 at 9:24 am
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'))
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 31, 2013 at 9:25 am
Dave62 (1/31/2013)
GilaMonster (1/31/2013)
The row count in sysindexes is not related to statistics updates.This quote from the MSDN page I referenced seems to indicate some relation.
"Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table."
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply