DBCC SHOW_STATISTICS - wrong? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DBCC SHOW_STATISTICS - wrong?

Author

Topic

MesterLars
Starting Member

3 Posts

Posted - 2005-11-22 : 07:25:04

--Create a table with a decimal column and add a index

CREATE TABLE [dbo].[lbtest] (
[test] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[lbtest] WITH NOCHECK ADD
CONSTRAINT [PK_lbtest] PRIMARY KEY CLUSTERED
(
[test]
) ON [PRIMARY]
GO

--Insert some data

insert into lbtest values(1)
insert into lbtest values(2)
insert into lbtest values(3)
insert into lbtest values(4)

--Update statistics
update statistics lbtest

--View the statistics
DBCC SHOW_STATISTICS (lbtest , PK_lbtest)

It says average length is 20 for the primary key, when I believe it should be 9? (sp_help on the table says length=9 for the column)

Try change datatype to INT and update statistics. Now Average Length=4 , as expected.

Try change back to decimal without updating statistics.
It now says 9. Update statistics and it says 20 again.

Is any good explanation why it reports average length as 20? And has it any influence on system performance?

Tried on a SQL Server 2000 SP3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources