December 8, 2011 at 4:17 am
Hi, I'm getting a very strange anomoly here.
We have a table of roughly 28,000,000 rows. The schema is below.
When we do:
Select Count(*),Count(Distinct Audit_ID) From Audit
We get a lower figure for Count(*).
My understanding is Count(*) is row count regardless of nulls whilst Count(Distinct ColumnName) will only return the number of unique values in that column.
The table is a HEAP and there are other indexes on the table.
Explanations would be greatly appreciated.
Thanks
Giles
/****** Object: Table [dbo].[Audit] Script Date: 12/08/2011 11:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Audit](
[audit_type] [nvarchar](40) NULL,
[audit_date] [datetime] NOT NULL,
[email_address] [nvarchar](100) NULL,
[communication_sid] [nvarchar](16) NULL,
[item_sid] [nvarchar](16) NULL,
[bounce_type] [nvarchar](40) NULL,
[bounce_category] [nvarchar](40) NULL,
[web_page] [nvarchar](255) NULL,
[hit_count] [int] NULL,
[info] [ntext] NULL,
[audit_id] [int] IDENTITY(200000000,1) NOT NULL,
[source_id] [nvarchar](50) NULL,
[customer_id] [nvarchar](6) NOT NULL,
[status] [nvarchar](10) NULL,
[isblocked] [bit] NULL,
[sync_audit_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF__Audit__audit_dat__3D5E1FD2] DEFAULT (getutcdate()) FOR [audit_date]
GO
December 8, 2011 at 6:49 am
I don't see why count(*) would give a lower number then count(distinct) based on your table design and the query being a single select statement.
However I noticed there is no primary key on the table. It shouldn't, but does it give a different result when you put one on it?
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 8, 2011 at 6:54 am
We're trying to determine if we can make audit_id a primary key which is why we're running the query.
The only thing I can think at this time is because the table is a Heap the statistics might not have anywhere to be stored?
We took the audit_id in to a temp table on its own and the count(*) and the count(distinct audit_id) tallied ok.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply