Vardecimal state without using sp_db_vardecimal_storage_format

The vardecimal state for any database (2005 SP2 onwards) can be either on or off. To find out this value you have to execute the sp_db_vardecimal_storage_format stored procedure with the relevant database name.

Eg EXECUTE sp_db_vardecimal_storage_format master

I can not find any relevant database property or column in the sys.databases view which relates to this value. It must be held somewhere. Does any one know where it? Or if it is not available by any othere means, why?,

more ▼

asked Jun 28, 2012 at 04:21 PM in Default

avatar image

170 11 11 16

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

This is internally stored by the version of the database. So it should be checked by the said procedure as recommended by Microsoft. But this is how you can get the information from databaseproperty

 select name as 'Database Name', 
 when databaseproperty(name, 'version') = 612 then 'ON'
 when databaseproperty(name, 'version') is null then null
 else 'OFF'
 end) as 'Vardecimal State'
 from master.dbo.sysdatabases
more ▼

answered Jun 28, 2012 at 04:40 PM

avatar image

Usman Butt
14.9k 6 13 21

Thanks for the reply but it does not seem to work. If I run sp_db_vardecimal_storage_format along side the code it shows the status to be ON for many databases whereas the code shows it to be OFF. The version number in these cases is generally 611

Jun 29, 2012 at 08:29 AM StuKay

@StuKay Are you running it in master database? I have not developed any thing fancy ;) This excerpt is taken from the procedure's definition itself, which exists in every database by default. You can see the procedure definition yourself, if you have the permissions. There you can sort out any difference with this query.

Jun 29, 2012 at 10:05 AM Usman Butt

Exactly as @Usman wrote, this is from the system stored proc itself. It is only on SQL 2005. On 2008+ it is not necessary and this SP on higher versions returns automatically ON for all user databases and OFF for system databases.

Jun 29, 2012 at 11:43 AM Pavel Pawlowski

All of your remarks have been beneficial. Thank you. I have also discovered you can not toggle the feature in SQL 2008. Master databases are always OFF and users databases are always ON. The recovery mode makes no difference. It makes you wonder why MS have bothered displaying it as an Option in the SSMS property page for a database other than the relevant SQL2005 versions. Note for SQL2008: This option always shows as TRUE in SSMS even for system databases! Do you think MS have just hard coded the word in the screen?

Jun 29, 2012 at 01:51 PM StuKay
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 28, 2012 at 04:21 PM

Seen: 1405 times

Last Updated: Jun 29, 2012 at 01:51 PM

Copyright 2018 Redgate Software. Privacy Policy