Obscure changes in SQL Server 2022: Metadata
May 24th, 20222
Obscure changes in SQL Server 2022: Metadata
May 24th, 20222
 
 

By now you've seen that the first public preview of SQL Server 2022 is available (resources here).

You can get the marketing blitz from just about anywhere, and the What's New documentation for the bigger hitters from the technical side.

But what about the changes that aren't on the highlight reel at Build and aren't getting all the attention from the media blitz? I'm a details person, so I get a lot of insight looking around at the little, non-headline-generating things that have changed. I've shown before how to sneak a peek under the hood, and I'm going to do it again today:

CTP 2.0 is build 16.0.600.9. Here's @@VERSION and the internal database version:

SELECT [@@Version] = @@VERSION;
SELECT [Internal DB Version] = DATABASEPROPERTYEX(N'msdb', N'Version');
 
/*
 
@@Version
-----------------------------------------------------
Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64) 
	May 20 2022 13:29:42 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows ...
 
Internal DB Version
-----------------------------------------------------
950
 
*/

I have another instance here named \SQL2019; so, I created a linked server:

DECLARE @srv sysname = N'.\SQL2019';
 
EXEC [master].dbo.sp_addlinkedserver   
     @server     = @srv, 
     @srvproduct = N'SQL Server';
 
EXEC [master].dbo.sp_addlinkedsrvlogin 
     @rmtsrvname = @srv, 
     @useself    = N'True';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'collation compatible', 
     @optvalue   = N'true';
 
EXEC [master].dbo.sp_serveroption
     @server     = @srv,
     @optname    = N'data access', 
     @optvalue   = N'true';

Then I created some synonyms in master:

USE [master];
GO
 
CREATE SYNONYM dbo.old_databases FOR [.\SQL2019].master.sys.databases;
CREATE SYNONYM dbo.old_objects   FOR [.\SQL2019].master.sys.all_objects;
CREATE SYNONYM dbo.old_columns   FOR [.\SQL2019].master.sys.all_columns;
CREATE SYNONYM dbo.old_modules   FOR [.\SQL2019].master.sys.all_sql_modules;
CREATE SYNONYM dbo.old_sessions  FOR [.\SQL2019].master.sys.server_event_sessions;
CREATE SYNONYM dbo.old_xevents   FOR [.\SQL2019].master.sys.server_event_session_events;
CREATE SYNONYM dbo.old_perf      FOR [.\SQL2019].master.sys.dm_os_performance_counters;
CREATE SYNONYM dbo.old_messages  FOR [.\SQL2019].master.sys.messages;

Then I started snooping, though I know some of what I find is going to be specific to non-box versions of SQL Server.

New Objects

This query is "give me all the objects in sys that exist in new but not in old."

SELECT [Object Name] = N'sys.' + o.name, [Type] = o.[type_desc]
FROM sys.all_objects AS o
WHERE o.[schema_id] = 4 -- we only care about sys.
AND o.[type_desc] <> N'PRIMARY_KEY_CONSTRAINT'
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_objects AS oo
    WHERE oo.[schema_id] = 4 AND oo.name = o.name
)
ORDER BY o.[type_desc], o.name;

This yielded 200 new objects! { Click to show / hide }

New Columns

This query is "for all the objects common to old version and new version, give me all the columns that are in the new but not in the old."

;WITH obj AS
(
  SELECT o.name, o.[object_id]
  FROM sys.all_objects AS o WHERE [schema_id] = 4
  INTERSECT
  SELECT o.name, o.[object_id]
  FROM dbo.old_objects AS o WHERE [schema_id] = 4
),
col(obj,col) AS
(
  SELECT obj.name, c.name 
  FROM obj INNER JOIN sys.all_columns AS c
    ON obj.[object_id] = c.[object_id]
  EXCEPT 
  SELECT obj.name, c.name 
  FROM obj INNER JOIN dbo.old_columns AS c
    ON obj.[object_id] = c.[object_id]
)
SELECT obj = N'sys.' + obj, col 
  FROM col ORDER BY obj, col;

This yielded 128 new columns! { Click to show / hide }

Changed Modules

This query is "give me all the definitions for all modules that are different between old and new."

SELECT o.[type_desc], N'sys.' + o.name,
  delta = LEN(m.[definition]) - LEN(rm.[definition]), 
  new_definition = m.[definition],
  old_definition = rm.[definition]
FROM sys.all_sql_modules AS m
INNER JOIN sys.all_objects AS o
  ON m.[object_id] = o.[object_id]
INNER JOIN dbo.old_objects AS ro
  ON o.name = ro.name
INNER JOIN dbo.old_modules AS rm
  ON ro.[object_id] = rm.[object_id]
WHERE  o.[schema_id] = 4
  AND ro.[schema_id] = 4
  AND m.[definition] <> rm.[definition]
ORDER BY o.[type_desc], o.name;

I'm not going to output the actual definitions here for space.

This yielded 117 changed modules! { Click to show / hide }

Changed XEvents

This query is "give me all the package/event combinations in the system health session that didn't exist in the old":

SELECT [Event] = e.package + '.' + e.name, e.event_id
  FROM sys.server_event_sessions AS s
  INNER JOIN sys.server_event_session_events AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
EXCEPT
SELECT e.package + '.' + e.name, e.event_id
  FROM dbo.old_sessions AS s
  INNER JOIN dbo.old_xevents AS e
  ON s.event_session_id = e.event_session_id
 WHERE s.name = N'system_health'
ORDER BY e.package + '.' + e.name, e.event_id;

This yielded 6 rows:

Package / Event
sqlos.process_killed
sqlserver.connectivity_ring_buffer_recorded
sqlserver.job_object_ring_buffer_stats
sqlserver.nonyield_copiedstack_ring_buffer_recorded
sqlserver.sp_server_diagnostics_component_result
sqlserver.sql_exit_invoked
event_id
51
37
42
43
40
59

New Error Messages

This query is "show me all the English error messages that are in the new but weren't in the old":

SELECT m.message_id, m.[text]
FROM sys.messages AS m
WHERE m.language_id = 1033 -- us_english
AND NOT EXISTS 
(
  SELECT 1 FROM dbo.old_messages AS om
    WHERE om.message_id = m.message_id
);

This produced a lot of rows (1,394, to be exact). Here are a few that matched the pattern '%ledger%':

22725
Enabling Change Feed for a ledger history table '%ls' is not allowed.

37340
Failed to generate the Ledger Blocks in the database with ID %d due to error %d. Check the errorlog for more information.

37354
LEDGER = ON cannot be specified with system versioning retention period.

37378
Ledger tables cannot be created on system databases.

37385
LEDGER = ON is not allowed for table variables.

In a future post…

…I'll take a look at some other metadata.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

2 Responses

  1. Neeraj Mittal says:
    May 31, 2022 at 2:33 PM

    Awesome to get new 2022 details at granule level.

  1. Obscure Changes in SQL Server 2022 – Curated SQL
    May 30, 2022

    […] Aaron Bertrand has a three-parter on obscure changes in SQL Server 2022. First up we have some new information: […]