This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You 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:

• 0×1: Indicates whether the configuration option is dynamic, or whether it requires the RECONFIGURE statement to be enacted.
• 0×2: Indicates whether the configuration option is an advanced SQL Server option.

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:

• name: The name of the configuration value, which is used with the stored procedure sp_configure to change configuration option values.
• value: The value that the SQL Server instance is configured to use.
• value_in_use: The value that the SQL Server instance is currently using.
• minimum: The minimum possible value for the configuration value
• maximum: The maximum possible value for the configuration value

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.