Discovering New System Objects and Functions in SQL Server 2019
SQL Server 2019 CTP was recently released. There is some information posted
about new features such as this document by Microsoft
What is new in SQL Server 2019 and this article on MSSQLTips
What’s New in the First Public CTP of SQL Server 2019. What other new features
are there that can be useful to a DBA’s daily operations?
As a SQL Server DBA, we are always excited about a new release of SQL Server.
I once wrote a tip
Identify System Object Differences Between SQL Server Versions and by using
the script in that tip, I have explored the new objects and new changes for existing
objects, such as new columns for views/tables or new parameters for functions/stored
Here are the detailed steps and some interesting findings in SQL Server 2019.
Step 1 – Environment Setup
To find what’s new in SQL Server 2019, we need two versions of SQL Server.
I will use SQL Server 2017 and SQL Server 2019. To follow along, you should have
these two SQL Server instances installed.
Step 2 – Collect Data
Follow the detailed instructions in this previous tip,
Identify System Object Differences Between SQL Server Versions, once this is
done, we should have three inventory tables populated. I put all these three tables
in [TempDB] for convenience. The three inventory tables will be shown in the
analysis scripts below.
Step 3 – Analyze Data
Once step 2 is done, we can start to do some interesting exploration. We first
take a look at the system object changes in SQL Server 2019 since SQL Server 2017.
-- 1. find new system objects declare @tgt_ver int = 15; -- sql server 2019 declare @src_ver int = 14; -- sql server 2017 select [schema], [object], [type] from tempdb.dbo.allobject where [version][email protected]_ver except select [schema], [object], [type] from tempdb.dbo.allobject where [version][email protected]_ver go
We get 53 new objects, the last few are shown below:
-- 2. find dropped system objects declare @tgt_ver int = 15; -- sql server 2019 declare @src_ver int = 14; -- sql server 2017 select [schema], [object], [type] from tempdb.dbo.allobject where [version][email protected]_ver except select [schema], [object], [type] from tempdb.dbo.allobject where [version][email protected]_ver go
The result is two extended stored procedures are dropped:
Now we take a look at the new columns added to system tables and views:
--3. new columns to sys views/tables declare @tgt_ver int = 15; -- sql server 2019 declare @src_ver int = 14; -- sql server 2017 ; with c as ( select distinct o.id, o.[schema], o.[object], o.[type], o.version--, p2.[object_type] from dbo.AllObject o inner join dbo.AllObject o2 on o.[schema]=o2.[schema] and o.[object]=o2.[object] and o.[version][email protected]_ver and o2.[version][email protected]_ver ), w as ( select [Object]=ac.[schema]+'.'+ac.[Object] , ac.[column], ac.column_type, ac.max_length, ac.[version]--, c.[version] from dbo.AllColumn ac inner join c on c.[object]=ac.[object] and c.[schema]=ac.[schema] and c.[version]=ac.[version] left join dbo.AllColumn ac2 on ac2.[schema]=ac.[schema] and ac2.object = ac.object and ac2.[column]=ac.[column] and ac2.[version][email protected]_ver where ac2.[column] is null) select [object], [column], [column_type], max_length from w order by 1
We see 75 changes; a sample of the changes is listed as below:
Now we will check the parameter changes to functions and stored procedures:
--4. find new parameters added to SPs/functions declare @tgt_ver int = 15; declare @src_ver int = 14; ; with c as ( select distinct p1.id, p1.[schema], p1.[object], p1.[object_type]--, p2.[object_type] from dbo.AllParam p1 inner join dbo.AllParam p2 on p1.[schema]=p2.[schema] and p1.[object]=p2.[object] and p1.[version][email protected]_ver -- sql server 2017 and p2.[version][email protected]_ver -- sql server 2012 ) select [Object]=p1.[schema]+'.'+p1.[Object] , p1.[param], p1.param_type, p1.max_length,p1.is_output, p1.[version] from dbo.AllParam p1 inner join c on c.id=p1.id left join dbo.AllParam p2 on p2.[schema]=p1.[schema] and p2.object = p1.object and p2.[param]=p1.[param] and p2.[version][email protected]_ver where p2.[param] is null; go
We can see the following changes:
Step 4 – Test new objects
In the new stored procedure / functions, I am pretty interested in the 3 new
extended stored procedures:
xp_copy_file xp_copy_files xp_delete_files
Before SQL Server 2019, for all copy and delete operations, we needed to rely
on xp_cmdshell with embedded copy and delete commands, but with these three new
SPs, we can do our work in a more T-SQL native way.
The basic syntax is:
-- NO wildcard allowed exec master.sys.xp_copy_file 'c:testa.txt' -- source , 'c:tempa1.txt' – destination -- Wildcard allowed exec master.sys.xp_copy_files 'c:testa*.txt' -- source , 'c:temp' – destination -- Wildcard allowed exec master.sys.xp_delete_files 'c:testa*.txt' -- source
There is another interesting new table valued function, sys.dm_db_page_info,
it has the following syntax
Sys.dm_db_page_info(<db_id>, <file_id>, <page num>, 'option') – where [option] can be of ‘limited’ or ‘detailed’, and <page num> starts from 0.
This view will surely be an important tool for internal storage troubleshooting.
A quick example is like the following:
select * from sys.dm_db_page_info(db_id('master'), 1, 1, 'detailed')
I get the following:
In theory, we can loop through each page of a database file via this view and
then do analysis. For example, after an index rebuild, check how IAM statistics
look or how data pages statistics look, etc. It should be fun.
In this tip, we have explored how to find changes in SQL Server 2019 since SQL
Server 2017. The exploration mainly focuses on changes on system objects, there
are some other areas that we can explore, such as record changes in some system
tables / views. For example, sys.configurations, sys.dm_xe_objects, sys.messages,
Download and install SQL Server 2019 and then read the following articles and
explore the new features and share your findings with the community here at MSSQLTips.com.
Last Update: 2018-10-11
About the author
Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.
View all my tips