debugsqlMinor
calculating the total number of rows in a table - fails when it is a heap - is there any fix?
Viewed 0 times
totalrowsfixnumberthefailsanyheapcalculatingwhen
Problem
I have a procedure that I use to find out how big is a table.
but when I select from that table, there is always 7 rows less.
my count always tell me the table has 7 rows more that it actually has.
one thing I thought might be the case, is this is actually not a table, it is a heap, because it does not have any indexes
as you can see on the script below.
how can I make sure my script gives me the correct number of rows, whether the object is a table or a heap?
select * from sys.objects
where name = 'WMS_SLALoad'
-- to get the object_id of my table
-- in this case it is 2041058307
SELECT o.object_id,
s_name=s.name,
o.name,
ddps.row_count
,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
and o.object_id = 2041058307
gobut when I select from that table, there is always 7 rows less.
my count always tell me the table has 7 rows more that it actually has.
one thing I thought might be the case, is this is actually not a table, it is a heap, because it does not have any indexes
as you can see on the script below.
CREATE TABLE [dbo].[WMS_SLALoad](
[MessageCycleNumber] [int] NOT NULL,
[Timestamp] [varchar](50) NOT NULL,
[Warehouse] [varchar](20) NOT NULL,
[ProdID] [varchar](8) NOT NULL,
[QuantityChange] [int] NOT NULL,
[Sign] [char](1) NOT NULL,
[Status] [varchar](20) NOT NULL,
[ReasonCode] [char](2) NULL,
[InUse] [int] NULL CONSTRAINT [DF_WMS_SLALoad_InUse] DEFAULT (0)
) ON [PRIMARY]how can I make sure my script gives me the correct number of rows, whether the object is a table or a heap?
Solution
make use of DBCC UPDATEUSAGE
as mentioned in the comments by Dan Guzman
has produced the following results:
After that the results of my original select was accurate enough for my purposes.
as mentioned in the comments by Dan Guzman
has produced the following results:
DBCC UPDATEUSAGE(N'WMSData',N'dbo.WMS_SLALoad') WITH COUNT_ROWSAfter that the results of my original select was accurate enough for my purposes.
Code Snippets
DBCC UPDATEUSAGE(N'WMSData',N'dbo.WMS_SLALoad') WITH COUNT_ROWSContext
StackExchange Database Administrators Q#122896, answer score: 2
Revisions (0)
No revisions yet.