http://www.sqlservercentral.com/blogs/stratesql/2012/07/05/lost-in-translation-deprecated-system-tables-sysconfigures/

Printed 2014/11/20 07:12PM

Lost in Translation – Deprecated System Tables – sysconfigures

2012/07/05

4957867646_2f2478fd69_m5 This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objectsYou can find a list of all of the deprecated views in the introduction post.

The compatibility view sysconfigures returns rows for each configuration option within SQL Server that can be set by the user.  This includes options such as the maximum degree of parallelism and the minimum and maximum levels for SQL Server memory.  These value settings are those that will affect the next restart or RECONFIGURE statements.  From a complexity standpoint, this compatibility view is fairly simple across it’s four columns.

The replacement catalog view is sys.configurations.  This catalog view provides the same information as the compatibility view.  Along with those columns, there are a few more columns provided which assist in managing configuration options more easily.

Status Column

Similar to other compatibility views, there is a status column on the view.  The column contains tow know bit values.  These values are:

Query Via sysconfigures

Queries against sysconfigures are often pretty basic.  Besides decoding the status column, the columns are returned as-is.  The query in Listing 1 demonstrates what is often used.


Listing 1 – Query for sysconfigures

SELECT value
,config
,comment
,CONVERT(smallint,status & 0x1) / 1 AS is_dynamic
,CONVERT(smallint,status & 0x2) / 2 AS is_advanced
FROM sysconfigures

Additional Information

The introduction of the catalog views allows for some additional information to be retrieved about schema objects which were previously difficult to ascertain.  These are available in the following columns:

Query via Catalog Views

The query in Listing 2 retrieves the same information from sys.configurations as the query for sysconfigurations.  The chief difference between the queries is the lack of a need to decode the status column and the addition of the additional columns.


Listing 2 – Query for sys.configures

SELECT c.value_in_use AS value
,c.configuration_id AS config
,c.description AS comment
,c.is_dynamic
,c.is_advanced
,c.name
,c.value
,c.minimum
,c.maximum
,c.value_in_use
FROM sys.configurations c

Summary

In this post, we discussed the use of sys.configurations over sysconfigurations.  In this case, changing from using the deprecated compatibility view to the catalog view is a relatively simple task.  There are few changes to the columns and the dataset is represented in the same manner.

Do you see any reason to continue using sysconfigures?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

  1. Lost in Translation – Deprecated System Tables – syscurconfigs
  2. Lost in Translation – Deprecated System Tables – syscolumns
  3. Lost in Translation – Deprecated System Tables – syscomments

Original article: Lost in Translation – Deprecated System Tables – sysconfigures

©2012 Strate SQL. All Rights Reserved.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.