sp_createstats breaks if it hits a non-deterministic computed column by RMJCS

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	519625
Opened:	12/10/2009 11:37:20 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

sp_createstats errors and exits if it tries to create stats for a non-deterministic computed column. It ought to skip these columns much like it does for other non-stats friendly columns.

I've tried this on SQL Server 2005 32bit Standard SP3 and SQL Server 2008 64bit Developer SP1.

Regards,
Rhys

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Not Applicable

use master
go

create database exampledb
go

use exampledb
go

create table t1 (i int identity not null, j int not null)
go

insert t1(j) select 1 union all select 2 union all select 3
go

sp_createstats
go

create table t2 (i int identity not null, j int not null, k as cast(j as float))
go

insert t2(j) select 1 union all select 2 union all select 3
go

sp_createstats
go

use master
go

drop database exampledb
go

.
.
.
Msg 2799, Level 16, State 1, Line 1
Cannot create index or statistics 'k' on table 'dbo.t2' because the computed column 'k' is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.

.
.
.
Statistics have been created for the ... listed columns of the above tables.

0 attachments

Posted by Microsoft on 4/5/2010 at 2:02 AM

Hello,

Unfortunately, we will not be fixing this defect in the next Service Pack of SQL Server 2008. As I mentioned before, this has been fixed in the current code base.

Regards,
Boris.

Posted by Microsoft on 12/23/2009 at 5:37 PM

Thank you for reporting the bug!

It has already been fixed in the code base of the next release. This item will reflect its state for the current release, SQL Server 2008 (i.e. it will be updated when the fix makes into the next cumulative update/service pack).

Meanwhile you can consider enabling autostats or creating the stats manually, avoiding this column.

Regards,
Boris Baryshnikov.
SQL Server Engine

Microsoft