Table Rowcount Different

Last Post 26 Jan 2012 05:54 AM by rm. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
shinoj_r
New Member
New Member

--
25 Jan 2012 02:54 PM
I have three statements to find the row counts in a heap

SELECT COUNT(*) FROM [TableName]
GO
772 Rows

sp_spaceused ' [TableName] '
GO
1052 rows

SELECT * FROM sys.partitions AS P WHERE object_id = OBJECT_ID(' [TableName] ')
GO
Index 0 - 1052 rows
Index 1 - 772 rows 
Index 2 - 775 rows 

Questions:-
1) Why would the number of row in index = 0 differ from rows in index 2 and 3 in sys.partitions output
2) Why would Count(*) return the result from index = 2 or 3 and sp_spaceused return count from index = 0? (why are the results of sp_spaceused different from count(*))

BTW, I have tried the same query after executing DBCC UpdateUsage

Thanks
Shinoj
rm
New Member
New Member

--
26 Jan 2012 05:54 AM
Update stats on the table then try again.


Acceptable Use Policy
---