Update Statistics - Views

Question

Update Statistics - Views

SQLSACT

SSC-Insane

Points: 21838
More actions
February 27, 2012 at 7:58 am

#257491

Hi
In have read that if you update statistics on a view and specify a Sample Rate, it will fail with the below error:
Cannot create or update statistics on view "view_name" because both FULLSCAN and NORECOMPUTE options are required
Is this true for SQL 2008?
I am able to update statistics on views on the AdventureWorks database with a Sample Rate and it doesn't give an error
Script:
use AdventureWorks
Go
update statistics Production.vProductAndDescription
with sample 10 percent

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is the article you read taking about views (which are just saved select statements and have neither data, indexes nor statistics) or indexed views?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster (2/27/2012)
Is the article you read taking about views (which are just saved select statements and have neither data, indexes nor statistics) or indexed views?

Hi

See link below

http://saveadba.blogspot.com/2012/02/cannot-update-statistics-fullscan-and.html

I have created an Update Statistics maintenance plan, I specified all the user databases in the instance and also specified a sample rate.

I checked the output file and when it gets to to the Views (Indexed or not), it applies the sample rate I specified and doesn't fail.

The script I gave in my original post Updates the statistics on an Indexed View

Thanks

Derek

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Check what Books Online says, there's a chance that the blog post is just wrong.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster (2/28/2012)
Check what Books Online says, there's a chance that the blog post is just wrong.

Thanks

I've created an Update Statistics Maintenance plan on a SQL2005 Sp3 box. I specified all the Databases and I also specified a Sample rate in the maintenance plan and the job fails with the error

Cannot create or update statistics on view "view_name" because both FULLSCAN and NORECOMPUTE options are required

I created the Same maintenance plan on a SQL 2008 Sp2 box and there was no error

Is it safe to say that the behaviour has changed between 2005 and 2008 and that you are in fact able to update statistics on an Indexed View with a Sample Rate

Thanks

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

GilaMonster (2/28/2012)
Check what Books Online says, there's a chance that the blog post is just wrong.

I checked 2008 BOL and there is no reference to not being able to specify a Sample Rate with an Indexed View

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

derekr 43208 (2/28/2012)
Is it safe to say that the behaviour has changed between 2005 and 2008 and that you are in fact able to update statistics on an Indexed View with a Sample Rate

Seeing as that's what your tests showed, it's probably safe to say so. (I don't know offhand)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass