SQL Server v.Next (Denali) : How has sys.databases changed?

Earlier, I documented the system objects that have changed in Denali.  One that caught my eye was a slew of new columns made available through sys.databases, mostly to support a feature I also blogged about recently, contained databases.  Consider the following simple query:

SELECT * FROM sys.databases;

Yes, this comes from a DMV, but where does the DMV really get its data?  First I wanted to view the execution plans in 2008 R2 and Denali to compare them: were there any significant differences?  Opening them in Management Studio proved to be relatively useless... to see everything on the screen, I can tell that they're a bit different, but zooming in and scrolling all over the place is for the birds:

2008 R2 graphical plan in SSMS

Denali graphical plan in SSMS

So I opened my trusty SQL Sentry Plan Explorer (which I've blogged about before) and got a much better quick view of these plans, without having to perform any zooming whatsoever:

2008 R2 graphical plan in Plan Explorer

Denali graphical plan in Plan Explorer

So this looks interesting so far; the execution plan for Denali is much busier, and has some references to internal objects I haven't noticed before: table-valued functions SYSLANG and SYSFULLTEXTLANGUAGES, and a system table called sysguidrefs.  You can see the actual definition of the view pretty simply, by issuing:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.databases'));

In SQL Server 2008 R2, you get this (about 4,200 characters):

CREATE VIEW sys.databases AS    SELECT d.name, d.id AS database_id,        r.indepid AS source_database_id,        d.sid AS owner_sid,        d.crdate AS create_date,        d.cmptlevel AS compatibility_level,        CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,        p.user_access, ua.name AS user_access_desc,        sysconv(bit, d.status & 0x400) AS is_read_only,          -- DBR_RDONLY        sysconv(bit, d.status & 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK        p.state, st.name AS state_desc,        sysconv(bit, d.status & 0x200000) AS is_in_standby,      -- DBR_STANDBY        sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown,  -- DBR_CLEANLY_SHUTDOWN        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG        p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT        p.recovery_model, ro.name AS recovery_model_desc,        p.page_verify_option, pv.name AS page_verify_option_desc,        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY        sysconv(bit, d.category & 1) AS is_published,        sysconv(bit, d.category & 2) AS is_subscribed,        sysconv(bit, d.category & 4) AS is_merge_published,        sysconv(bit, d.category & 16) AS is_distributor,        sysconv(bit, d.category & 32) AS is_sync_with_backup,        d.svcbrkrguid AS service_broker_guid,        sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,        p.log_reuse_wait, lr.name AS log_reuse_wait_desc,        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT        sysconv(bit, d.category & 64) AS is_cdc_enabled,        sysconv(bit, d.status2 & 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION        sysconv(bit, d.status2 & 0x8) AS is_honor_broker_priority_on             -- DBR_HONORBRKPRI    FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access    LEFT JOIN sys.syspalvalues si  ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait    WHERE d.id < 0x7fff        AND has_access('DB', d.id) = 1

And in Denali, it's much more verbose - almost 50% larger (about 6,200 characters):

CREATE VIEW sys.databases AS    SELECT d.name, d.id AS database_id,        r.indepid AS source_database_id,        d.sid AS owner_sid,        d.crdate AS create_date,        d.cmptlevel AS compatibility_level,        CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name,        p.user_access, ua.name AS user_access_desc,        sysconv(bit, d.status & 0x400) AS is_read_only,          -- DBR_RDONLY        sysconv(bit, d.status & 1) AS is_auto_close_on,          -- DBR_CLOSE_ON_EXIT        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,      -- DBR_AUTOSHRINK        p.state, st.name AS state_desc,        sysconv(bit, d.status & 0x200000) AS is_in_standby,      -- DBR_STANDBY        p.is_cleanly_shutdown,        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled,  -- DBR_SUPPLEMENT_LOG        p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,      -- DBR_READCOMMITTED_SNAPSHOT        p.recovery_model, ro.name AS recovery_model_desc,        p.page_verify_option, pv.name AS page_verify_option_desc,        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,          -- DBR_AUTOCRTSTATS        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,     -- DBR_AUTOUPDSTATS        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,   -- DBR_AUTOUPDSTATSASYNC        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,         -- DBR_ANSINULLDFLT        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,             -- DBR_ANSINULLS        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                  -- DBR_ANSIPADDING        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,         -- DBR_ANSIWARNINGS        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,      -- DBR_CATNULL        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,         -- DBR_NUMEABORT        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,           -- DBR_QUOTEDIDENT        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,           -- DBR_RECURTRIG        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,           -- DBR_DEFLOCALCURS        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,         -- DBR_FTENABLED        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced,  -- DBR_UNIVERSALAUTOPARAM        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,   -- DBR_MASTKEY        sysconv(bit, d.category & 1) AS is_published,        sysconv(bit, d.category & 2) AS is_subscribed,        sysconv(bit, d.category & 4) AS is_merge_published,        sysconv(bit, d.category & 16) AS is_distributor,        sysconv(bit, d.category & 32) AS is_sync_with_backup,        d.svcbrkrguid AS service_broker_guid,        sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled,        p.log_reuse_wait, lr.name AS log_reuse_wait_desc,        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,       -- DBR_DATECORRELATIONOPT        sysconv(bit, d.category & 64) AS is_cdc_enabled,        sysconv(bit, d.status2 & 0x100) AS is_encrypted,                     -- DBR_ENCRYPTION        CONVERT(bit, d.status2 & 0x8) AS is_honor_broker_priority_on,                -- DBR_HONORBRKPRI        sgr.guid AS replica_id,        sgr2.guid AS group_database_id,        default_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.default_language) ELSE NULL END,        default_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, sld.name) ELSE NULL END,        default_fulltext_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x800000 AND p.containment = 1) THEN CONVERT(INT, p.default_fulltext_language) ELSE NULL END,        default_fulltext_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, slft.name) ELSE NULL END,        is_nested_triggers_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.allow_nested_triggers) ELSE NULL END,        is_transform_noise_words_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.transform_noise_words) ELSE NULL END,        two_digit_year_cutoff = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.two_digit_year_cutoff) ELSE NULL END,        containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB        containment_desc = CONVERT(NVARCHAR(60), cdb.name)    FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access    LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait    LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0    -- SRC_AVAILABILITYGROUP    LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP    LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID    LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID    LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END    LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language    LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language    WHERE d.id < 0x7fff        AND has_access('DB', d.id) = 1

But of course you can't dig much deeper than this.  If you try and see into objects like sys.singleobjectrefs and sys.sysdbreg, you get the SQL Server equivalent to a 404 Not Found error.  This is even true if you use an old hack to view the system resource db directly (which I've described before).

So there isn't all that much value in this after all, unless you're an avid explorer.  For me, I like to dig in as far as I can to see how things work, but I get pretty aggravated when I see syntax that works in system objects, but that we can't use ourselves (such as "hidden" OPENROWSET() calls) or things that seem custom for no obvious reason, such as the sysconv() function - why don't they just use CONVERT()? 

More importantly, I have a guilty pleasure in seeing bad practices in practice (or at least inconsistency).  Notice in the above code that the new columns for database containment properties have been added using "alias = expression" syntax, whereas the existing code had exclusively used "expression as alias" notation.  They also should be using statement terminators, if for no other reason than to demonstrate that they really mean it when they say they will be required in some future version of SQL Server (see this rant from last year). 

There are other things that irk me too - lack of AS for table/view aliases, use of LEFT JOIN instead of LEFT OUTER JOIN, inconsistent spacing, sometimes putting more than one output column on the same line, sloppy indenting and alignment... it surprises me when my own code quality and consistency standards would reject just about every module that ships in SQL Server.  It might take 5 extra minutes to polish this view up, and if I were responsible for it, I would simply squeeze it into my deadline.  Of course I don't have much insight into the Microsoft development cycle, but I do know that a lot of - let's say - "less than stellar" code comes out of those offices.  The stuff above is pretty tame in comparison.

Probably the most important thing *YOU* should be aware of: there are extra columns here, so if you are currently building #temp tables or auditing scripts with the previous schema of sys.databases in mind, you'll need to be prepared for this change so that your existing scripts don't break.