SQL Server 2012 : Changes to system objects in RC0

AaronBertrand — Fri, 18 Nov 2011 22:07:00 GMT

As with every new major milestone, one of the first things I do is check out what has changed under the covers. Since RC0 was released yesterday, I've been poking around at some of the DMV and other system changes. Here is what I have noticed:

New objects in RC0 that weren't in CTP3

Quick summary:

We see a bunch of new aggregates for use with geography and geometry. I've stayed away from that area of programming so I'm not going to dig into them.
There is a new extended procedure called sp_showmemo_xml. The name could send me all over the place guessing whether it has something to do with Access, straight XML handling, or something else. This one doesn't seem to be documented in Books Online so, for now, I'm going to leave it alone.
The undocumented stored procedure sp_MSgetversion has returned. I observed previously that this procedure had been dropped, and brushed it aside - being undocumented and unsupported, it shouldn't be all that prevalent. Who knew that Microsoft's own replication code relied on it?
There are some new AlwaysOn DMVs and a validation stored procedure.
There is a new DMV and catalog view around Resource Governor, reflecting one of the changes I outlined last month.
By far the most interesting to me are the extended stored procedures around parallel nested transactions. Thus far these are undocumented and I would not be shocked if they remain so for the time being. I would hazard a guess as to what these are for but I don't want to sound like a fortune teller so, again, I'll leave them alone - for now.

New columns in existing DMVs

Some relatively self-explanatory new columns for the AlwaysOn and Resource Governor DMVs:

Columns that have changed definition

Several DMV columns have been expanded in size, except in the case of one, which was made smaller. These are all NVARCHAR columns:

Modules that have been modified

It's impossible to tell which modules have been modified since the previous CTP, since all but a few system and internal objects get a new create_date when a new build is produced. But at least for objects represented in sys.sql_modules, we can obtain a list of objects where the size of the definition has changed, meaning there may be some meaningful changes we want to investigate further. I definitely spot a few that I am going to check out more thoroughly:

Hope this information is useful. Let me know if you spot any under-the-cover changes I haven't noticed.

Search results matching tags 'SQL Server 2012', 'SQL Server "Denali"', 'dynamic management objects', and 'metadata'

SQL Server 2012 : Changes to system objects in RC0