HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
totalrowsfixnumberthefailsanyheapcalculatingwhen

Problem

I have a procedure that I use to find out how big is a table.

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
go


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.

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:

DBCC UPDATEUSAGE(N'WMSData',N'dbo.WMS_SLALoad') WITH COUNT_ROWS


After 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_ROWS

Context

StackExchange Database Administrators Q#122896, answer score: 2

Revisions (0)

No revisions yet.