System Procedures and Views Whose Code Changed Between SQL Server 2019 and 2022
I usually find these changes when an old deployment script starts behaving differently. I compared the SQL Server 2019 and 2022 catalog definitions for procedures and views that were already there.
In the first post in this series, I counted new system objects in SQL Server 2022. This post is about a different group: modules that already existed in SQL Server 2019, but whose definitions changed by SQL Server 2022.
The inventory found 217 changed object_hash rows between SQL Server 2019 Cumulative Update 32 (CU32) and SQL Server 2022 Cumulative Update 25 (CU25). The changed module text belonged to 156 stored procedures, 51 views, 8 inline table-valued functions and 2 scalar functions. SQL Server 2022 also had 112 new module hashes, but those new modules are not the subject here.
I compared the 2019 and 2022 definitions first after collapsing whitespace and case. Eighteen of the 217 changes disappeared at that stage. None were header-comment-only changes. Two more were comment-only changes inside the body. After those filters, 197 modules still had substantive definition changes that needed classification. The code blocks in this post are excerpts from the system definitions, not scripts to run.
What changed, at a high level
These categories overlap. A procedure can add a parameter and also change a result column.
| Category | Changed modules |
|---|---|
| Substantive definition changes | 197 |
| Procedures with new parameters | 21 |
| New or changed result columns in views and functions | 39 |
| New feature hooks | 40 |
| Whitespace or case only | 18 |
| Header-comment-only | 0 |
| Comment-only elsewhere | 2 |
Other changes add version or edition checks, permission checks and extra error handling. I sorted those by reading them, so I haven’t given counts for them. Most feature-specific changes relate to ledger, XML compression, change data capture (CDC), Microsoft Entra login handling, Query Store changes, and Azure or Managed Instance checks. Some CDC changes relate to extended data definition language (DDL) handling. I also saw internal hooks whose name terms include FIDO, DW and Trident. I didn’t find a SQL Server source that explains those internal name terms, so I’m not expanding them here.[1]
sys.sp_cdc_enable_table added a parameter
sys.sp_cdc_enable_table is the change data capture entry point for a source table. CDC records data manipulation language (DML) changes, such as inserts, updates and deletes, from the transaction log into change tables. Its SQL Server 2022 definition adds @enable_extended_ddl_handling and passes it to the internal worker procedure.
|
1 2 3 4 5 6 7 8 9 10 |
-- SQL Server 2019 @filegroup_name sysname = null, @allow_partition_switch bit = 1 ) -- SQL Server 2022 @filegroup_name sysname = null, @allow_partition_switch bit = 1, @enable_extended_ddl_handling bit = 0 ) |
The 2022 body also rejects @enable_extended_ddl_handling = 1 unless a feature switch says CDC DDL handling is enabled:
|
1 2 3 4 5 |
if (@enable_extended_ddl_handling = 1 and ([sys].[fn_cdc_handle_ddl_featureswitch_is_enabled]() <> 1)) begin raiserror(22874, 16, -1) return 1 end |
sys.sp_cdc_enable_table is documented. The Learn page for sys.sp_cdc_enable_table includes the parameter in the syntax block. It says the parameter is identified for informational purposes only, not supported, and not guaranteed for future compatibility.[2] The unsupported wording matters if you generate CDC setup scripts from metadata, because the parameter is present but not a supported contract.
sys.sp_estimate_data_compression_savings added XML compression
The 2022 definition adds @xml_compression. It also changes the validation so @data_compression can be NULL when @xml_compression is supplied.
|
1 2 3 4 5 6 7 8 |
-- SQL Server 2019 @partition_number int, @data_compression nvarchar(60) -- SQL Server 2022 @partition_number int, @data_compression nvarchar(60), @xml_compression bit = null |
The @xml_compression parameter is caller-visible. A script that estimates storage for XML compression has a supported parameter to use on SQL Server 2022. The Learn page lists @xml_compression and gives the rule that @data_compression cannot be NULL when @xml_compression is also NULL. It also says SQL Server 2022 added XML compression support for off-row XML data.[3]
sys.sp_readerrorlog accepts a newer permission
Both sys.sp_readerrorlog and sys.sp_enumerrorlogs changed their permission gate. In 2019, securityadmin or VIEW SERVER STATE was enough. In 2022, the procedure also allows VIEW ANY ERROR LOG.
|
1 2 3 4 5 6 7 8 |
-- SQL Server 2019 IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) -- SQL Server 2022 IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW ANY ERROR LOG') = 1) |
The sys.sp_readerrorlog Learn page documents a SQL Server 2022 permission change, but the wording does not mirror the code excerpt. It says SQL Server 2022 and later require either VIEW ANY ERROR LOG or VIEW SERVER PERFORMANCE STATE.[4] I did not test every permission combination. I am recording this as a code/documentation difference to check, not as a claim that the documentation is wrong.
I searched by file name and by git grep in the SQL Server, Azure and Fabric documentation source repositories. I did not find a separate Learn page for sys.sp_enumerrorlogs. Its code change is the same permission addition, so I treated it as related evidence rather than a separate documented procedure.
sys.sp_rename adds ledger checks
sys.sp_rename includes a much larger change. The SQL Server 2022 definition checks ledger table and ledger view metadata before renaming columns.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- SQL Server 2019 -- Is this a temporal table column? if (objectproperty(@objid, 'tabletemporaltype') = 1) begin -- This column belongs to a temporal history table so it can't be renamed. -- SQL Server 2022 -- Is this a dropped ledger column? if ((select is_dropped_ledger_column from sys.columns where column_id = @colid and object_id = @objid) = 1) begin -- dropped ledger columns cannot be renamed COMMIT TRANSACTION set @colname = @UnqualOldName raiserror(37427,-1,-1, @colnameLen, @colname) return 1 end |
The same procedure also calls an internal lock method that checks ALTER LEDGER permission when the target is a ledger table or view. The Learn page documents the caller-visible permission rule: to rename a ledger table, ALTER LEDGER is required.[5]
This definition change can alter an existing deployment script. A rename that worked for an ordinary table column can fail against a ledger object, and a dropped ledger column gets a specific error path.
sys.sp_helpsrvrolemember widened its role range
The SQL Server 2019 definition hard-codes the fixed-server-role principal range through bulkadmin. The SQL Server 2022 definition validates fixed roles differently and widens the result-set range.
|
1 2 3 4 5 6 7 8 9 10 |
-- SQL Server 2019 where name = @srvrolename and principal_id >= suser_id('sysadmin') and principal_id <= suser_id('bulkadmin') -- SQL Server 2022 where name = @srvrolename and principal_id >= suser_id('sysadmin') and type = 'R' and is_fixed_role = 1 |
|
1 2 3 4 5 |
-- SQL Server 2019 where rm.role_principal_id >=3 AND rm.role_principal_id <=10 -- SQL Server 2022 where rm.role_principal_id >=3 AND rm.role_principal_id <=20 |
The procedure’s Learn page still lists only the older fixed server roles in its argument table: sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin.[6] The older list matters if you use this procedure in a report. On SQL Server 2022, the system code can return rows for newer fixed server roles whose principal IDs are outside the old range.
sys.sp_spaceused can delegate to another procedure
sys.sp_spaceused is mostly the same for ordinary databases. SQL Server 2022 adds an early branch that checks sys.dm_dw_databases and delegates to sys.sp_fido_spaceused when the current database is listed there.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- SQL Server 2022 IF (HAS_PERMS_BY_NAME(null, 'DATABASE', 'VIEW DATABASE STATE') = 1) BEGIN IF EXISTS (SELECT * FROM sys.dm_dw_databases where logical_db_name = db_name()) BEGIN BEGIN TRY IF @objname IS NOT NULL EXEC sys.sp_fido_spaceused @objname = @objname, @updateusage = @updateusage, @oneresultset = @oneresultset ELSE EXEC sys.sp_fido_spaceused @updateusage = @updateusage, @oneresultset = @oneresultset return (0) |
The Learn page for sys.sp_spaceused documents the procedure syntax and result sets.[7] I did not find sys.dm_dw_databases or sys.sp_fido_spaceused on that page. I also did not find them in the SQL Server, Azure and Fabric documentation source search. I only have definition evidence for this branch, not a runtime test. On a database with a matching sys.dm_dw_databases row and VIEW DATABASE STATE, SQL Server 2022 can return whatever sys.sp_fido_spaceused returns instead of the older local calculation. Because I didn’t test that path, I wouldn’t turn it into an upgrade-test recommendation yet.
What I would test first
The caller-visible changes to review first are sys.sp_helpsrvrolemember, sys.sp_rename, sys.sp_readerrorlog and sys.sp_spaceused. They can change a result set, permission path or error path without the caller changing the procedure name.
Most of the 197 substantive changes don’t affect day-to-day DBA work, so I wouldn’t put all of them in an upgrade test plan. I’d start with the familiar procedures that changed a result set, permission path or error path.
Next in the series: new error messages in SQL Server 2022.
Have you seen one of these procedure changes affect an upgrade or a monitoring script? Tell me on Bluesky or LinkedIn, and I will update the notes.
References
- MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. Markdown source repositories searched with
git grep -w -Ffor selected procedure names and change terms. ↩ - sys.sp_cdc_enable_table (Transact-SQL) - Microsoft Learn. Documents the
@enable_extended_ddl_handlingparameter and marks it informational and unsupported. ↩ - sys.sp_estimate_data_compression_savings (Transact-SQL) - Microsoft Learn. Documents the
@xml_compressionparameter and SQL Server 2022 XML compression support. ↩ - sys.sp_readerrorlog (Transact-SQL) - Microsoft Learn. Documents the SQL Server 2022 permission requirement for reading error logs. ↩
- sys.sp_rename (Transact-SQL) - Microsoft Learn. Documents the
ALTER LEDGERpermission requirement for renaming a ledger table. ↩ - sys.sp_helpsrvrolemember (Transact-SQL) - Microsoft Learn. Lists the fixed server roles documented on the procedure page. ↩
- sys.sp_spaceused (Transact-SQL) - Microsoft Learn. Documents the procedure syntax and result sets that I checked for the FIDO branch. ↩