Stop the Hollyweb! No DRM in HTML5.   

Thursday, June 10, 2010

SQL Server Row Counts in Properties Are Wrong!

One of my users came to me and asked why the row count in the table properties was different from the SELECT COUNT(1) from the table. This was a database that was sent to us from a different company and attached to our server. The first question for me was; where does row count in properties come from in SQL Server? As it turns out, it gets this count from the sysindexes table which we can see from the following;

USE DatabaseName
GO
SELECT
rowcnt
FROM
dbo.sysindexes
WHERE OBJECT_NAME(id) =
'TableName'
GO

-- Returns
330013

A count of the table results in the following;

USE DatabaseName
GO
SELECT COUNT(1) AS
CNT
FROM
dbo.TableName
GO


-- Returns
331882



To update the sysindexes table with the correct row counts, we run the following DBCC;

USE DatabaseName
GO
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
;
GO

Now when we run our queries again, we get the following;

USE DatabaseName
GO
SELECT
rowcnt
FROM
dbo.sysindexes
WHERE OBJECT_NAME(id) =
'TableName '
GO

SELECT COUNT(1) AS
CNT
FROM
dbo.TableName
GO

-- Returns

331882
-- Returns
331882