SQL Server v.Next (Denali) : New, Changed and Removed System Objects
While it's still only the CTP1 timeframe, getting a glimpse of the changes in system objects can start preparing you for what's coming – especially when there are potential breaking changes, as you'll see below. Sure, a few of these things can change (and hopefully be fixed!) as the product gets closer to release, but once you have the scripts on disk, you can take a pulse at any time. Obviously, queries that go after the system objects directly are going to be much less tedious and annoying than trying to parse Books Online for changes (even if you assume that Books Online is up to date – which, as you'll see below, is not the case).
Before starting, I'll assume that you have a 2008 R2 instance and a Denali instance; these instances should have valid linked servers pointing at each other. In the code below you will see references to fairly intuitive linked server names: [GREENLANTERN\SQL2008R2]
and [GREENLANTERN\DENALI]
. I will try to remember to make it clear which instance the code should run from (in a few cases it matters, because functions like OBJECT_SCHEMA_NAME()
don't work remotely without using [linked server].sys.sp_executesql
).
New system objects in SQL Server "Denali"
This is a query run from the Denali server that will find objects that did not exist in 2008 R2. I had to code in an exclusion in the where clause; while sp_MS_marksystemobject
certainly exists in 2008 R2, it does not appear in sys.all_objects
the way it does in Denali.
SELECT [name] = N'sys.' + o.name, [type] = o.type_desc FROM [master].sys.all_objects AS o LEFT OUTER JOIN [GREENLANTERN\SQL2008R2].[master].sys.all_objects AS oo ON o.name = oo.name AND o.[schema_id] = oo.[schema_id] WHERE oo.name IS NULL AND o.[schema_id] = 4 AND o.name <> N'sp_MS_marksystemobject' ORDER BY o.type_desc, o.name;
Some of these objects are documented, but several still are not listed in the current version of Denali BOL. I was going to remove the links for the items that aren't documented, but I'll leave all of the search links intact so that they will continue to work when they *are* added to Books Online.
System objects dropped in SQL Server "Denali"
We can run a query similar to the above from the 2008 R2 instance, to see what kind of things will no longer work in Denali.
SELECT [name] = N'sys.' + o.name, [type] = o.type_desc FROM [master].sys.all_objects AS o LEFT OUTER JOIN [GREENLANTERN\DENALI].[master].sys.all_objects AS oo ON o.name = oo.name AND o.[schema_id] = oo.[schema_id] WHERE oo.name IS NULL AND o.[schema_id] = 4 ORDER BY o.type_desc, o.name;
The results: these are mostly XPs that have been marked for deprecation several versions ago (and actually are still marked as "future deprecated" in the current Denali Books Online – see Connect #625200). However I cannot yet explain the service broker DMV that is missing; I filed a bug about this in Connect #625206.
name | type |
---|---|
sys.sp_batch_params | EXTENDED_STORED_PROCEDURE |
sys.sp_fetchLOBfromcookie | EXTENDED_STORED_PROCEDURE |
sys.xp_adsirequest | EXTENDED_STORED_PROCEDURE |
sys.xp_deletemail | EXTENDED_STORED_PROCEDURE |
sys.xp_findnextmsg | EXTENDED_STORED_PROCEDURE |
sys.xp_get_mapi_default_profile | EXTENDED_STORED_PROCEDURE |
sys.xp_get_mapi_profiles | EXTENDED_STORED_PROCEDURE |
sys.xp_MSADEnabled | EXTENDED_STORED_PROCEDURE |
sys.xp_MSADSIObjReg | EXTENDED_STORED_PROCEDURE |
sys.xp_MSADSIObjRegDB | EXTENDED_STORED_PROCEDURE |
sys.xp_MSADSIReg | EXTENDED_STORED_PROCEDURE |
sys.xp_readmail | EXTENDED_STORED_PROCEDURE |
sys.xp_sendmail | EXTENDED_STORED_PROCEDURE |
sys.xp_startmail | EXTENDED_STORED_PROCEDURE |
sys.xp_stopmail | EXTENDED_STORED_PROCEDURE |
sys.xp_test_mapi_profile | EXTENDED_STORED_PROCEDURE |
sys.sp_ActiveDirectory_Obj | SQL_STORED_PROCEDURE |
sys.sp_ActiveDirectory_SCP | SQL_STORED_PROCEDURE |
sys.sp_ActiveDirectory_Start | SQL_STORED_PROCEDURE |
sys.sp_dropalias | SQL_STORED_PROCEDURE |
sys.sp_MScheckIsPubOfSub | SQL_STORED_PROCEDURE |
sys.sp_processmail | SQL_STORED_PROCEDURE |
sys.database_principal_aliases | VIEW |
sys.dm_broker_forwarded_messages | VIEW |
View columns added or changed in SQL Server "Denali"
This query, run on the Denali instance, will show new or changed columns compared to SQL Server 2008 R2:
;WITH r AS ( SELECT c.[object_id], [schema] = OBJECT_SCHEMA_NAME(c.[object_id]), viewname = v.name, col = c.name, t = c.system_type_id, [type] = t.name, c.[precision], c.scale, c.max_length FROM [master].sys.all_columns AS c INNER JOIN [master].sys.all_views AS v ON c.[object_id] = v.[object_id] INNER JOIN [master].sys.types AS t ON t.system_type_id = c.system_type_id WHERE t.name <> N'sysname' ) SELECT [status] = CASE WHEN l.viewname IS NULL THEN 'new' ELSE 'changed' END, r.[schema], r.viewname, r.col, r.[type], FormerType = l.[type], r.[precision], FormerPrecision = l.[precision], r.scale, FormerScale = l.scale, max_length = r.max_length / CASE WHEN r.[type] = N'nvarchar' THEN 2 ELSE 1 END, FormerMaxLength = l.max_length / CASE WHEN l.[type] = N'nvarchar' THEN 2 ELSE 1 END FROM r LEFT OUTER JOIN ( SELECT [schema] = s.name, viewname = v.name, col = c.name, t = c.system_type_id, [type] = t.name, c.[precision], c.scale, c.max_length FROM [GREENLANTERN\SQL2008R2].[master].sys.all_columns AS c INNER JOIN [GREENLANTERN\SQL2008R2].[master].sys.all_views AS v ON c.[object_id] = v.[object_id] INNER JOIN [GREENLANTERN\SQL2008R2].[master].sys.schemas AS s ON v.[schema_id] = s.[schema_id] INNER JOIN [GREENLANTERN\SQL2008R2].[master].sys.types AS t ON c.system_type_id = t.system_type_id WHERE t.name <> N'sysname' ) AS l ON r.viewname = l.viewname AND r.[schema] = l.[schema] AND r.col = l.col WHERE ( l.col IS NULL OR ( r.t <> COALESCE(l.t, -10) OR r.[precision] <> COALESCE(l.[precision], -10) OR r.scale <> COALESCE(l.scale, -10) OR r.max_length <> COALESCE(l.max_length, -10) ) ) AND EXISTS ( SELECT 1 FROM [GREENLANTERN\SQL2008R2].[master].sys.all_views WHERE name = r.viewname ) ORDER BY [status] DESC, r.viewname, r.col;
The results: many new property columns for sys.databases
, several new (well, renamed; see below) memory usage indicators in a few key DMVs, and some new columns around contained databases. (Please note that not all of the columns should imply that those features, e.g. two-digit year cutoff, will be supported in the next major version of SQL Server.) Also, a handful of the big string columns in various DMVs have been widened from 256 or 2,048 characters to 3,072; I'm not sure if any of these caused bugs in earlier versions, or they are just being proactive.
column status | view name | column name | new data type | old data type |
---|---|---|---|---|
new | sys.all_sql_modules | is_contained | bit | |
new | sys.data_spaces | is_system | bit | |
new | sys.database_principals | authentication_type | int | |
new | sys.database_principals | authentication_type_desc | nvarchar(60) | |
new | sys.database_principals | default_language_lcid | int | |
new | sys.database_principals | default_language_name | nvarchar(128) | |
new | sys.databases | containment | tinyint | |
new | sys.databases | containment_desc | nvarchar(60) | |
new | sys.databases | default_fulltext_language_lcid | int | |
new | sys.databases | default_fulltext_language_name | nvarchar(128) | |
new | sys.databases | default_language_lcid | smallint | |
new | sys.databases | default_language_name | nvarchar(128) | |
new | sys.databases | group_database_id | uniqueidentifier | |
new | sys.databases | is_nested_triggers_on | bit | |
new | sys.databases | is_transform_noise_words_on | bit | |
new | sys.databases | replica_id | uniqueidentifier | |
new | sys.databases | two_digit_year_cutoff | smallint | |
new | sys.dm_clr_appdomains | compatibility_level | int | |
new | sys.dm_database_encryption_keys | encryptor_type | nvarchar(128) | |
new | sys.dm_db_file_space_usage | allocated_extent_page_count | bigint | |
new | sys.dm_db_file_space_usage | filegroup_id | smallint | |
new | sys.dm_db_file_space_usage | total_page_count | bigint | |
new | sys.dm_exec_cached_plans | global_token | varbinary(14) | |
new | sys.dm_exec_sessions | authenticating_database_id | int | |
new | sys.dm_os_memory_cache_counters | pages_in_use_kb | bigint | |
new | sys.dm_os_memory_cache_counters | pages_kb | bigint | |
new | sys.dm_os_memory_cache_entries | pages_kb | bigint | |
new | sys.dm_os_memory_clerks | page_size_in_bytes | bigint | |
new | sys.dm_os_memory_clerks | pages_kb | bigint | |
new | sys.dm_os_memory_nodes | foreign_committed_kb | bigint | |
new | sys.dm_os_memory_nodes | pages_kb | bigint | |
new | sys.dm_os_memory_objects | max_pages_in_bytes | bigint | |
new | sys.dm_os_memory_objects | pages_in_bytes | bigint | |
new | sys.dm_os_sys_info | committed_kb | bigint | |
new | sys.dm_os_sys_info | committed_target_kb | bigint | |
new | sys.dm_os_sys_info | physical_memory_kb | bigint | |
new | sys.dm_os_sys_info | virtual_machine_type | int | |
new | sys.dm_os_sys_info | virtual_machine_type_desc | nvarchar(60) | |
new | sys.dm_os_sys_info | virtual_memory_kb | bigint | |
new | sys.dm_os_sys_info | visible_target_kb | bigint | |
new | sys.dm_os_worker_local_storage | query_driver_address | varbinary(8) | |
new | sys.filegroups | is_system | bit | |
new | sys.fulltext_indexes | property_list_id | int | |
new | sys.partition_functions | is_system | bit | |
new | sys.partition_schemes | is_system | bit | |
new | sys.server_event_session_events | predicate_xml | nvarchar(0) | |
new | sys.server_principals | is_fixed_role | bit | |
new | sys.server_principals | owning_principal_id | int | |
new | sys.server_trigger_events | is_trigger_event | bit | |
new | sys.sql_modules | is_contained | bit | |
new | sys.trigger_events | is_trigger_event | bit | |
changed | sys.dm_os_memory_cache_entries | entry_data | nvarchar(3072) | nvarchar(2048) |
changed | sys.dm_os_ring_buffers | record | nvarchar(3072) | nvarchar(2048) |
changed | sys.dm_os_waiting_tasks | resource_description | nvarchar(3072) | nvarchar(2048) |
changed | sys.dm_xe_map_values | map_value | nvarchar(3072) | nvarchar(2048) |
changed | sys.dm_xe_object_columns | description | nvarchar(3072) | nvarchar(256) |
changed | sys.dm_xe_objects | description | nvarchar(3072) | nvarchar(256) |
changed | sys.dm_xe_packages | description | nvarchar(3072) | nvarchar(256) |
changed | sys.dm_xe_session_events | event_predicate | nvarchar(3072) | nvarchar(2048) |
changed | sys.dm_xe_session_object_columns | column_value | nvarchar(3072) | nvarchar(2048) |
changed | sys.syscacheobjects | cacheobjtype | nvarchar(50) | nvarchar(17) |
Columns dropped from SQL Server "Denali"
And finally, we should check for columns that have been dropped from DMVs or catalog views, since cpu_ticks_in_ms was dropped from sys.dm_os_sys_info in SQL Server 2008 with little fanfare. On the 2008 R2 instance, you can run this query:
;WITH r AS ( SELECT c.[object_id], [schema] = OBJECT_SCHEMA_NAME(c.[object_id]), viewname = v.name, col = c.name FROM [master].sys.all_columns AS c INNER JOIN [master].sys.all_views AS v ON c.[object_id] = v.[object_id] ) SELECT r.[schema], r.viewname, r.col FROM r LEFT OUTER JOIN ( SELECT [schema] = s.name, viewname = v.name, col = c.name FROM [GREENLANTERN\DENALI].[master].sys.all_columns AS c INNER JOIN [GREENLANTERN\DENALI].[master].sys.all_views AS v ON c.[object_id] = v.[object_id] INNER JOIN [GREENLANTERN\DENALI].[master].sys.schemas AS s ON v.[schema_id] = s.[schema_id] ) AS l ON r.viewname = l.viewname AND r.[schema] = l.[schema] AND r.col = l.col WHERE l.col IS NULL AND EXISTS ( SELECT 1 FROM [GREENLANTERN\DENALI].[master].sys.all_views WHERE name = r.viewname ) ORDER BY r.viewname, r.col;
The results: as it turns out, the only columns that have been "dropped" are the ones that have been renamed or consolidated (they are listed as new above).
view name | column name |
---|---|
sys.dm_os_memory_cache_counters | multi_pages_in_use_kb |
sys.dm_os_memory_cache_counters | multi_pages_kb |
sys.dm_os_memory_cache_counters | single_pages_in_use_kb |
sys.dm_os_memory_cache_counters | single_pages_kb |
sys.dm_os_memory_cache_entries | pages_allocated_count |
sys.dm_os_memory_clerks | multi_pages_kb |
sys.dm_os_memory_clerks | page_size_bytes |
sys.dm_os_memory_clerks | single_pages_kb |
sys.dm_os_memory_nodes | multi_pages_kb |
sys.dm_os_memory_nodes | single_pages_kb |
sys.dm_os_memory_objects | max_pages_allocated_count |
sys.dm_os_memory_objects | pages_allocated_count |
sys.dm_os_sys_info | bpool_commit_target |
sys.dm_os_sys_info | bpool_committed |
sys.dm_os_sys_info | bpool_visible |
sys.dm_os_sys_info | physical_memory_in_bytes |
sys.dm_os_sys_info | virtual_memory_in_bytes |
If you're currently using any of these DMVs in your own code, this is potentially a major breaking change – you may want to make note of the columns that have changed, as upgrading to Denali may break some of your scripts.
Why do you want sys.functions? sys.procedures actually has some information that's not in sys.objects; I don't think any additional columns would exist in sys.functions, so it would just be the same as writing a predicate on sys.objects. Seems like a waste of space to me.
Marc, they are considering it. But only 3 votes in 2+ years? It's not going to float to the top of the priority list with all that enthusiasm.
http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/363452/needed-catalog-view-for-functions
Sheesh – seems that Microsoft has **STILL** not added a "sys.functions" catalog view. Why do we have a "sys.procedures", but no "sys.functions" ?? About time they would fix that!!
Awesome work sir – glad I'm on the right track adding denali ctp1 support with my apps…
Arthur, I don't think it's ever too early. By the time the public gets CTP1, pretty much all of the functionality is either baked in or decided. You won't see a lot of change between now and, say, CTP3 except that the UI bits will be coded in to support the underlying changes that are already in place. Better to be prepared early than wait until the last minute.
Isn't it too early to talk about new/depreciated objects if "Denali" is still in CTP1?
Thanks Adam, yes the memory DMVs certainly have the potential to break existing code – not just column existence, but also what do the columns mean (notice that some metrics have been consolidated). But even the additional column on sys.dm_exec_sessions can break code that uses SELECT INTO or INSERT … SELECT * to capture the DMV values (I haven't checked out what MDW does, but as for custom code out there, all bets are off). Maybe this is a place where the enhancements for metadata discovery can help.
Great work, Aaron. Interesting to note the changes to the memory DMVs. Will definitely have to dig in there sooner rather than later.
Wow – great post. Thanks for sharing – bookmarked!
That is a comprehensive list. Thanks Aaron!