Record count errors | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Record count errors

I have a table, tbCDR, with 40 columns, one is an identity column called CDRID,another column is AdminRate, which is a decimal(16, 9) and is nullable. When I run the following scripts I get the results commented out by the "–". These numbers are bogus. If there are 19 million records in the table and 15.7 million have a value in the AdminRate column, how can 17.5 million have a null value in the AdminRate column. Help me please. SelectCount(CDRID)
FromCDRArchive.dbo.tbCDR
–19,321,170 SelectCount(CDRID)
FromCDRArchive.dbo.tbCDR
WhereAdminRate Is Not Null
–15,790,085 SelectCount(CDRID)
FromCDRArchive.dbo.tbCDR
WhereAdminRate Is Null
–17,518,988
I have no idea based on what I can see here.
Can you try this select count(cdrid), count(*),
sum(case when adminrate is null then 1 else 0 end) as totalnull,
sum(case when adminrate is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDR the first 2 values should be the same and the last 2 should add up to the first. Then try select count(cdrid), count(*),
sum(case when adminrate is null then 1 else 0 end) as totalnull,
sum(case when adminrate is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDR
where adminrate is null and the first 3 values should be the same, the last should be zero. I dont know if this will make a difference (it shouldnt, but I’m curious)
I work with pbrewer… I ran a DBCC CheckDB on the database (excluding indexes) and it had no errors. I’m now running it again (including indexes) to see if there’s an issue there. When that’s complete, if it shows no errors, I’ll run what you posted. In addition to his queries, I ran: Selectdistinct AdminRateCodeNew
FromtbCDRArchive
WhereAdminRateCodeNew Is Null And got values, not a null. Weird to say the least.
Very strange, very, very strange. Next question(probably already looked at), what are your ansi null settings on that database? In QA where you’re running this?
For:
select count(cdrid), count(*),
sum(case when adminrate is null then 1 else 0 end) as totalnull,
sum(case when adminrate is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDR I got:
totalnull totalNotnull
———– ———– ———– ————
19321170 19321170 3531085 15790085
For:
select count(CDRRecordID), count(*),
sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDRArchive
where AdminRateCodeNew is null I got:
totalnull totalNotnull
———– ———– ———– ————
19054553 19054553 3531085 15523468
Set ansi_defaults has a gray check
Set ansi_null_dflt_on is checked
Set ansi_padding is checked
Set ansi_warnings is checked
At the database level, we’ve tried ANSI Null Default both checked and unchecked.
wow, I can see your frustration,and even though this is not my data, feel it too!!! Okay, response from the first query, says that adminrate is being found correctly as null/not null since the 2 sums of possible values equals the total…. The second one though…
You selected only where the value is null, there should have been nothing in the second sum and all three other values the same. Can you run the second query, without the where clause and try the first with the where clause (still grasping straws, but at least one field is returning expected results)
select count(CDRRecordID), count(*),
sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDRArchive
where AdminRateCodeNew is null produced:
totalnull totalNotnull
———– ———– ———– ————
19054553 19054553 3531085 15523468 select count(CDRRecordID), count(*),
sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDRArchive produced:
totalnull totalNotnull
———– ———– ———– ————
19321170 19321170 3531085 15790085
BTW, the DBCC checkDB (including indexes) returned w/no errors. Just being curious, we rebooted the server also. The reboot was done prior to my first post.
More Info: Selectdistinct AdminRateCodeNew — Count(CDRRecordID)
FromtbCDRArchive — With(NoLock)
WhereCoalesce(AdminRateCodeNew, -999) = -999 Produced Null (correct)
SelectCount(CDRRecordID)
FromtbCDRArchive — With(NoLock)
WhereCoalesce(AdminRateCodeNew, -999) = -999 Produced 3531085 (correct) I’m now running: Updatea
Set AdminRateCodeNew = Null
FromtbCDRArchive a
WhereCoalesce(AdminRateCodeNew, -999) = -999 I’ll then run the initial queries again to see if that fixed the problem. I’ll let you know as soon as it’s done.
It updated the correct # of records, but querying the data still gives the odd/inaccurate results.
Guessing … Perhaps you’re trying to apply criteria on a calculated field, and maybe that won’t work?
Are you asking if AdminRateCodeNew is a calculated field? It’s not, it’s a normal nullable decimal field.
I meant the AdminRate field you mentioned in the first post.<br /><br />Another idea would be to check if your identity field contains NULLs, because if you do COUNT(&lt;fieldname&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />, only non-nulls get counted. That’s different from COUNT(*), which will count all rows.<br /><br />Is the table involved in some sort of replication?
The identity column "shouldn’t" contain nulls, it’s a SQL identity column. As for replication, I’ll let pbrewer answer that.
The table is not replicated, and CDRRecordID is an identity column. The seed is (1, 1) and should not have any nulls.
I know it shouldn’t contain nulls. But did you doublecheck? Sorry if this seems pointless, but unexpected results are often caused by incorrect assumptions – and I think we’ve already established that there has to be something here that is not what we assume it is.
I executed “Select Count(*)
FromCDRArchive.dbo.tbCDRArchive
WhereCDRRecordID Is Null”, and had 0 records.
Also, in an above message in this post, at my pbrewer already compared the count(*) to count(field) values and they were the same. I asked, just to be sure. What I find so strange are the results from the where compared to the results from the count case…its like "is null" is behaving differently depending on where its used. What is the datatype of adminratecode and adminratecodenew? Next set of ‘humor me’ queries, since "is null" looks a bit off, lets see how "isnull" behaves….
You need to pick a value for the field that cannot ever be there, if its an integer field, pick a negative number (-1), thats what I’ll put here
select count(CDRRecordID),
sum(case when AdminRateCodeNew is null then 1 else 0 end) as totalnull,
sum(case when AdminRateCodeNew is null then 0 else 1 end) as totalNotnull
from CDRArchive.dbo.tbCDRArchive
where isnull(AdminRateCodeNew,-1) = -1
then for the heck of it — what, if any records are returned from the below (should be nothing) select top 10 ccdrrecorid, AdminRateCodeNew
from CDRArchive.dbo.tbCDRArchive
where isnull(AdminRateCodeNew,-1) = -1
and adminratecodenew is not null select top 10 ccdrrecorid, AdminRateCodeNew
from CDRArchive.dbo.tbCDRArchive
where isnull(AdminRateCodeNew,-1) <> -1
and adminratecodenew is null
have you tried updating statistics on your tables? This shouldnt really help but I notice the system tables hold the record count and perhaps this is being used as an optimisation, but is out of date?<br /><br />hmmm well ok, its a long shot <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Chappy</i><br /><br />have you tried updating statistics on your tables? This shouldnt really help but I notice the system tables hold the record count and perhaps this is being used as an optimisation, but is out of date?<br /><br />hmmm well ok, its a long shot <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />That was pbrewer’s first thought, we need to do an update stats just to see if that fixes it.
I performed the following code and still the same problem in the new database and new table. Select *
Into CDRArchiveNew.dbo.tbCDRArchiveNew
From tbCDRArchive I also found that many of the columns in the table are lacking "COLLATE SQL_Latin1_General_CP1_CI_AS" when script a "Create" in QA.
Get this SelectCount(CDRRecordID)
FromCDRArchive.dbo.tbCDRArchive
WhereAdminRateCodeNew Is Null
andAdminRateCodeNew Is Not Null
Result: ———–
13152625 (1 row(s) affected)
Note that the result changes from run to run.
Can you post the create script for your table here please? Love that last set of results…..I know null is not a value, but sheesh…. one last one too (since I’m here) for the heck of it Select Count(CDRRecordID), count(adminratecodenew)
From CDRArchive.dbo.tbCDRArchive
Where AdminRateCodeNew = Null
Create script as generated via Query Analyzer (right click on table in object browser and script as create):
Create script as generated via Query Analyzer (right click on table in object browser and script as create): CREATE TABLE [tbCDRArchive] (
[CDRRecordID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberID] [smallint] NOT NULL ,
[MemberIdentMethodID] [int] NULL ,
[ServiceMonth] [tinyint] NULL CONSTRAINT [DF_tbCDRArchive_ServiceMonth] DEFAULT (datepart(month,getdate())),
[ServiceYear] [smallint] NULL CONSTRAINT [DF_tbCDRArchive_ServiceYear] DEFAULT (datepart(year,getdate())),
[CarrierID] [tinyint] NOT NULL ,
[ProductID] [tinyint] NOT NULL ,
[JurisdictionID] [tinyint] NOT NULL CONSTRAINT [DF_tbCDRArchive_JurisdictionID] DEFAULT (0),
[EMIIndicator19] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SettlementCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CategoryID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GroupID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CDRReceiveDate] [smalldatetime] NOT NULL ,
[CallDate] [smalldatetime] NOT NULL ,
[CallTime] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CallDurationMinutes] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CallDurationSeconds] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CallDurationTenths] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CallDurationTotal] [decimal](18, 4) NOT NULL ,
[BillableDurationTotal] [decimal](18, 4) NOT NULL CONSTRAINT [DF_tbCDRArchive_BillableDurationTotal] DEFAULT (0),
[FromStateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToStateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FromNumNPA] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToNumNPA] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FromLataCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToLataCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PlatformNum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AuthCode] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BillPhoneNum] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CIC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PIN] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddedToEMIInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PayPhoneFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DirectAssistFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CDRStatsUpdateFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AttentionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DedicatedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PeakFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CDRCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CostAmt1] DEFAULT (0),
[CDRRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CDRRateAmt] DEFAULT (0),
[CostMinusSurchargesAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CostMinusSurchargesAmt1] DEFAULT (0),
[CarrierContractRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierContractRateAmt1] DEFAULT (0),
[CarrierContractCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierContractCostAmt] DEFAULT (0),
[CalculatedRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CalculatedRateAmt1] DEFAULT (0),
[CalculatedCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CalculatedCostAmt1] DEFAULT (0),
[PayphoneSurChargeAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_PayphoneSurChargeAmt1] DEFAULT (0),
[AdminFeeAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_AdminFeeAmt] DEFAULT (0),
[CarrierExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_CarrierExceptionAmt] DEFAULT (0),
[GVNWExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_GVNWExceptionAmt] DEFAULT (0),
[ANPIExceptionAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_ANPIExceptionAmt] DEFAULT (0),
[InvoiceRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_InvoiceRateAmt] DEFAULT (0),
[InvoiceAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_InvoiceAmt1] DEFAULT (0),
[EMIRecord] [char] (210) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CDRModules] [varchar] (236) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SourceFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EMIFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StaffID] [smallint] NOT NULL ,
[UpdateDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tbCDRArchive_UpdateDate] DEFAULT (getdate()),
[Identified] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceRecord] [varchar] (700) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductTypeCodeID] [int] NULL ,
[DACodeID] [int] NULL ,
[OrigNum] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TermNum] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CostMinusSurchargesRateAmt] [decimal](25, 9) NULL ,
[CarrierContractRateCodeID] [int] NULL ,
[ContractRateCodeID1] [int] NULL ,
[ContractRateAmt1] [decimal](16, 9) NULL ,
[ContractCostAmt1] [decimal](25, 9) NULL ,
[ContractRateCodeID2] [int] NULL ,
[ContractRateAmt2] [decimal](16, 9) NULL ,
[ContractCostAmt2] [decimal](25, 9) NULL ,
[ContractRateCodeID3] [int] NULL ,
[ContractRateAmt3] [decimal](16, 9) NULL ,
[ContractCostAmt3] [decimal](25, 9) NULL ,
[ContractRateCodeID4] [int] NULL ,
[ContractRateAmt4] [decimal](16, 9) NULL ,
[ContractCostAmt4] [decimal](25, 9) NULL ,
[ContractRateCodeID5] [int] NULL ,
[ContractRateAmt5] [decimal](16, 9) NULL ,
[ContractCostAmt5] [decimal](25, 9) NULL ,
[UsedRateCodeID] [int] NULL ,
[UsedRateAmt] [decimal](16, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_UsedRateAmt] DEFAULT (0),
[UsedCostAmt] [decimal](25, 9) NOT NULL CONSTRAINT [DF_tbCDRArchive_UsedCostAmt] DEFAULT (0),
[AdminRateCodeID] [int] NULL ,
[AdminRateCodeNewID] [int] NULL ,
[AdminRateCodeNew] [decimal](16, 9) NULL ,
[OrigLRN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TermLRN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Running: Select Count(CDRRecordID), count(adminratecodenew)
From CDRArchive.dbo.tbCDRArchive
Where AdminRateCodeNew = Null
consistently produces:
———– ———–
0 0 (1 row(s) affected)
Well at least = null is behaving correctly. I’ll see if anything leaps out from the create table.
Not exactly, = null isn’t returning (and won’t to my knowledge) records where that field is null. For instance, the following query produces 0 as a result:
SelectCount(CDRRecordID)
FromCDRArchive.dbo.tbCDRArchive
WhereAdminRateCodeNew = Null
But this query actually produces 100 rows of valid data (where AdminRateCodeNew is null): Selecttop 100 *
FromtbCDRArchive — With(NoLock)
WhereCoalesce(AdminRateCodeNew, -999) = -999
I know, but I was jsut wondering if there had been something odd setup on your server/db that was making null behave as something other htan null. I expected no results since nothing can = null, but had to ask based on the other mess that is going on. oh, and then since you brought up coalesce, do you get the same results from each of these?
select count(*) from tbcdrarchive where Coalesce(AdminRateCodeNew, -999) = -999
select count(*) from tbcdrarchive where isnull(AdminRateCodeNew, -999) = -999
select count(*) from tbcdrarchive where AdminRateCodeNew is null
Here are the results from the queries. The table count has changed due to daily processing that we cannot defer to another table. select count(*) from tbcdrarchive where Coalesce(AdminRateCodeNew, -999) = -999
9231932 select count(*) from tbcdrarchive where isnull(AdminRateCodeNew, -999) = -999
9231932 select count(*) from tbcdrarchive where AdminRateCodeNew is null
24064826 — Should be somewhere around 10,000,000. select count(*) from tbcdrarchive
25033915
Now is the time to beat your head against the wall. Wow. The first three should produce the exact same results, but only the first 2 did! I’m actually stuck now, even with wild suggestions. From what I knew, coalesce, isnull and is null should all be check for the same concept of null, but is null seems to be off doing its own thing. Unless someone can come up with a reason, use isnull and/or coalesce with an impossible value rather than is null to keep your application going.
Then if you are bored and have a spare machine around, build a clean version of your server (service packs etc) then restore a database and see if behaves the same way. Sorry, wish I could help more.
pbrewer actually created another database on the same server, and inserted the data into it. The new database acts as it should, but I’ll tell you, when you start seeing results like that, you start to lose confidence in SQL Server.
If you still have your old database around, have you run full diagnostics on it? There must be some corruption somewhere? Wish I could get my hands on it to play, but oh well. At least you have something working!
I think there must be something with the database because a new table in the same database produces the exact problems. When I created the new database and table, though on the same server, it is working well.
]]>