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 syscurconfigs returns rows for each configuration option within SQL Server that can be set by the user.  The options that can be configured are those such as the maximum degree of parallelism and whether to allow remote connections to the SQL Server.  The value returned for each configuration value presented are those that the SQL Server is currently running with.  Any configuration changes that have been made, but not applied, will not be represented in the results.  This is the key difference between this compatibility view and sysconfigures; which returns the values that will take affect after the next RECONFIGURE or service restart.

As with the sysconfigures compatibility view, syscurconfigs is also replace with the catalog view 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

Syscurconfigs also contains a status column that provides information similar to data available in the sysconfigures status column.  The column contains two known 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 syscurconfigs

Queries against syscurconfigs are often pretty basic.  Besides decoding the status column, the rest of the columns are returned as they are.  The code in Listing 1 can be used to query the compatibility view.

Listing 1 – Query for syscurconfigs
SELECT value
,config
,comment
,CONVERT(smallint,status & 0x1) / 1 AS is_dynamic
,CONVERT(smallint,status & 0x2) / 2 AS is_advanced
FROM syscurconfigs

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 syscurconfigs.  The chief difference between the queries is the lack of a need to decode the status column and the inclusion of the configured value with the running value.

Listing 2 – Query for sys.configures 
SELECT c.configuration_id AS config
    ,c.description AS comment
    ,c.is_dynamic
    ,c.is_advanced
    ,c.name
    ,c.value_in_use
    ,c.value
    ,c.minimum
    ,c.maximum
FROM sys.configurations c

Summary

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

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

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

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysconfigures
2. Lost in Translation – Deprecated System Tables – sysdepends
3. Lost in Translation – Deprecated System Tables – sysaltfiles