Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Discovering New System Objects and Functions in SQL Server 2019


By:   |   Last Updated: 2018-10-11   |   Comments (1)   |   Related Tips: More > SQL Server 2019

Problem

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?

Solution

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 procedures.

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:

new sql server 2019 system objects
-- 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:

dropped sql server 2019 system objects

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:

new sql server 2019 system table columns

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:

new sql server 2019 system function parameters

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:\test\a.txt' -- source
, 'c:\temp\a1.txt'  destination
 
-- Wildcard allowed
exec master.sys.xp_copy_files 'c:\test\a*.txt' -- source
, 'c:\temp\'  destination
 
-- Wildcard allowed
exec master.sys.xp_delete_files 'c:\test\a*.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:

new sql server 2019 dm dm page info

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.

Summary

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, etc.

Next Steps

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 Updated: 2018-10-11


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao 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
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, October 13, 2018 - 4:37:57 AM - Manu Back To Top

Thanks for the info.


Learn more about SQL Server tools