Each version of SQL Server potentially adds new columns to the DMVs. These new columns are available even if you change the compatibility level of the database to a previous version.
Is there a list somewhere that shows which columns are available for a given version of SQL Server?
Each version of SQL Server potentially adds new columns to the DMVs.
Correct, and since each CU is technically a new version this can happen inside of major version releases.
Is there a list somewhere that shows which columns are available for a given version of SQL Server?
While this may not be in the spirit of the question asked, there technically is an answer which is to use the system tables. For example, what columns does sys.availability_groups currently have in this version (14.0.3037):
select name FROM sys.all_columns where object_id = object_id('sys.availability_groups') order by column_id asc
Will output:
╔═════════════════════════════════════════════╗
║ name ║
╠═════════════════════════════════════════════╣
║ group_id ║
║ name ║
║ resource_id ║
║ resource_group_id ║
║ failure_condition_level ║
║ health_check_timeout ║
║ automated_backup_preference ║
║ automated_backup_preference_desc ║
║ version ║
║ basic_features ║
║ dtc_support ║
║ db_failover ║
║ is_distributed ║
║ cluster_type ║
║ cluster_type_desc ║
║ required_synchronized_secondaries_to_commit ║
║ sequence_number ║
╚═════════════════════════════════════════════╝
More in the spirit of the question, Docs is your best source for columns based on version outside of your own queries.
The definitive answer is of course the documentation.
This isn't always easy to track down though - especially if you are attempting to write scripts compatible with older versions (such as 2005).
If you only want to cater for additional columns and you have a server version at the lower and upper end of the target range you can use a trick like this one to write a query compatible with all server versions in that range.
With OptionalColumns AS
(
--Added between 2005 and 2017
SELECT
is_auto_create_stats_incremental_on = CAST(NULL AS bit),
is_query_store_on = CAST(NULL AS bit),
is_cdc_enabled = CAST(NULL AS bit),
is_encrypted = CAST(NULL AS bit),
is_honor_broker_priority_on = CAST(NULL AS bit),
replica_id = CAST(NULL AS uniqueidentifier),
group_database_id = CAST(NULL AS uniqueidentifier),
resource_pool_id = CAST(NULL AS int),
default_language_lcid = CAST(NULL AS smallint),
default_language_name = CAST(NULL AS nvarchar(128)),
default_fulltext_language_lcid = CAST(NULL AS int),
default_fulltext_language_name = CAST(NULL AS nvarchar(128)),
is_nested_triggers_on = CAST(NULL AS bit),
is_transform_noise_words_on = CAST(NULL AS bit),
two_digit_year_cutoff = CAST(NULL AS smallint),
containment = CAST(NULL AS tinyint),
containment_desc = CAST(NULL AS nvarchar(60)),
target_recovery_time_in_seconds = CAST(NULL AS int),
delayed_durability = CAST(NULL AS int),
delayed_durability_desc = CAST(NULL AS nvarchar(60)),
is_memory_optimized_elevate_to_snapshot_on = CAST(NULL AS bit),
is_federation_member = CAST(NULL AS bit),
is_remote_data_archive_enabled = CAST(NULL AS bit),
is_mixed_page_allocation_on = CAST(NULL AS bit),
is_temporal_history_retention_enabled = CAST(NULL AS bit)
), sys_databases AS
(
SELECT ca.*
FROM OptionalColumns
CROSS APPLY
(
--These will be resolved from sys.databases if they exist there in the current version or the OptionalColumns CTE otherwise
SELECT name, database_id, source_database_id, owner_sid, create_date, compatibility_level, collation_name, user_access, user_access_desc, is_read_only, is_auto_close_on, is_auto_shrink_on, state, state_desc, is_in_standby,
is_cleanly_shutdown, is_supplemental_logging_enabled, snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on, recovery_model, recovery_model_desc, page_verify_option,
page_verify_option_desc, is_auto_create_stats_on, is_auto_create_stats_incremental_on, is_auto_update_stats_on, is_auto_update_stats_async_on, is_ansi_null_default_on, is_ansi_nulls_on, is_ansi_padding_on,
is_ansi_warnings_on, is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on, is_quoted_identifier_on, is_recursive_triggers_on, is_cursor_close_on_commit_on, is_local_cursor_default,
is_fulltext_enabled, is_trustworthy_on, is_db_chaining_on, is_parameterization_forced, is_master_key_encrypted_by_server, is_query_store_on, is_published, is_subscribed, is_merge_published, is_distributor,
is_sync_with_backup, service_broker_guid, is_broker_enabled, log_reuse_wait, log_reuse_wait_desc, is_date_correlation_on, is_cdc_enabled, is_encrypted, is_honor_broker_priority_on, replica_id, group_database_id,
resource_pool_id, default_language_lcid, default_language_name, default_fulltext_language_lcid, default_fulltext_language_name, is_nested_triggers_on, is_transform_noise_words_on, two_digit_year_cutoff, containment,
containment_desc, target_recovery_time_in_seconds, delayed_durability, delayed_durability_desc, is_memory_optimized_elevate_to_snapshot_on, is_federation_member, is_remote_data_archive_enabled,
is_mixed_page_allocation_on, is_temporal_history_retention_enabled
FROM sys.databases
) ca
)
SELECT *
FROM sys_databases
In the absence of information about columns available in 2005 the same technique could be used but just enumerating all the columns in the OptionalColumns CTE rather than being able to target just the newly added ones.
One method to show the columns available in DMVs of different versions without consulting the documentation is with an SSMS multi-server query. Assuming you already have the versions installed, create an SSMS registered server group with a registered server for each version. Then, right-click on the group name and select new query to open a multi-server query window and run this script to list the version, patch level, and columns:
DECLARE @ObjectName nvarchar(261) = N'sys.availability_groups';
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') AS ProductLevel
, @ObjectName AS ObjectName
, name AS ColumnName
FROM sys.all_columns
WHERE object_id = object_id(@ObjectName)
ORDER BY column_id ASC;
Below are example results from my test machine using the DMV in @SeanGallardy's answer, I'm a bit behind on patching these instances :-).
+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
| Server Name | ProductVersion | ProductLevel | ObjectName | ColumnName |
+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | group_id |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | name |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | resource_id |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | resource_group_id |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | failure_condition_level |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | health_check_timeout |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | automated_backup_preference |
| WIN2016TEST1\SQL2012 | 11.0.7001.0 | SP4 | sys.availability_groups | automated_backup_preference_desc |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | group_id |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | name |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | resource_id |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | resource_group_id |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | failure_condition_level |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | health_check_timeout |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | automated_backup_preference |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | automated_backup_preference_desc |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | version |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | basic_features |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | dtc_support |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | db_failover |
| WIN2016TEST1\SQL2016 | 13.0.5233.0 | SP2 | sys.availability_groups | is_distributed |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | group_id |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | name |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | resource_id |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | resource_group_id |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | failure_condition_level |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | health_check_timeout |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | automated_backup_preference |
| WIN2016TEST1\SQL2014 | 12.0.6024.0 | SP3 | sys.availability_groups | automated_backup_preference_desc |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | group_id |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | name |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | resource_id |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | resource_group_id |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | failure_condition_level |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | health_check_timeout |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | automated_backup_preference |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | automated_backup_preference_desc |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | version |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | basic_features |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | dtc_support |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | db_failover |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | is_distributed |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | cluster_type |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | cluster_type_desc |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | required_synchronized_secondaries_to_commit |
| WIN2016TEST1\SQL2017 | 14.0.3045.24 | RTM | sys.availability_groups | sequence_number |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | group_id |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | name |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | resource_id |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | resource_group_id |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | failure_condition_level |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | health_check_timeout |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | automated_backup_preference |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | automated_backup_preference_desc |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | version |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | basic_features |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | dtc_support |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | db_failover |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | is_distributed |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | cluster_type |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | cluster_type_desc |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | required_synchronized_secondaries_to_commit |
| WIN2016TEST1\SQL2019CTP2 | 15.0.1200.24 | CTP2.2 | sys.availability_groups | sequence_number |
+--------------------------+----------------+--------------+-------------------------+---------------------------------------------+
