How I spot not-yet-documented features in SQL Server CTPs
Quite often, the plumbing for new features starts making its way into the product long before those new features ever get mentioned publicly, whether formally documented in Books Online, championed in the Data Platform blog, or announced on stage at popular conferences.
SQL Server 2016 is no exception; I've stumbled upon several new features during the CTP cycle that hadn't yet been talked about elsewhere. And it has nothing to do with being an MVP or being on the SQL Server vNext TAP. Revealing things I could only have learned there would be very anti-Fight Club - and I've already said too much. But if a build is public and you can find it, it's got to be fair game to speculate, right?
It's the metadata, silly
In honesty, I'm just meticulous about installing each new build and immediately digging into the metadata. It would be hard to take a look at
sys.all_objects and identify what's new by sight; even columns like
modify_date are not as accurate as you might expect. (For example, in CTP 3.1,
sp_helpindex has a
create_date of 2015-11-21 18:03:15.267.)
So instead of relying on photographic memory or hoping that something new will jump out at me while scanning the new catalog, I always install the new CTP side-by-side with the previous CTP (or, in the case of the very first CTP, side-by-side with the previous version). Then I can just perform various types of anti-semi-joins across a linked server to see objects and columns that have been added, removed, or changed. First, I set up the linked server:
USE [master]; -- on .\CTP31 EXEC dbo.sp_addlinkedserver @server = N'.\CTP30', @srvproduct = N'SQL Server'; EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname = N'.\CTP30', @useself = N'True'; EXEC dbo.sp_serveroption @server = N'.\CTP30', @optname = N'collation compatible', @optvalue = N'true'; EXEC dbo.sp_serveroption @server = N'.\CTP30', @optname = N'data access', @optvalue = N'true';
Then I create three synonyms to point to the older CTP so that I can reuse the same scripts without manually changing the linked server name:
CREATE SYNONYM dbo.OlderCTP_all_objects FOR [.\CTP30].master.sys.all_objects; CREATE SYNONYM dbo.OlderCTP_all_columns FOR [.\CTP30].master.sys.all_columns; CREATE SYNONYM dbo.OlderCTP_all_sql_modules FOR [.\CTP30].master.sys.all_sql_modules;
Now, I run a series of queries that I've developed over the years to identify changes. (Note that in some cases you may have to add
COLLATE clauses if you use different server collations.)
The most obvious thing to check for is the set of views, tables, and other objects that are appearing for the first time.
SELECT o.name, o.[type_desc] FROM sys.all_objects AS o WHERE o.[schema_id] = 4 -- only care about sys. AND o.name NOT IN ( SELECT name FROM dbo.OlderCTP_all_objects WHERE [schema_id] = 4 ) ORDER BY o.name;
In this case, this reveals the following three rows as new objects in CTP 3.1:
|Schema / Object||Type|
So, this allowed me to discover a new catalog view (which I speculated here might have something to do with new functionality around database- and replica-specific settings), and two new extended stored procedures for supporting stretch tables (
rda stands for "remote data archive").
(You can reverse the query to find objects that have been dropped, but this is pretty rare, even during a CTP phase.)
Many catalog views and DMVs adapt over time, usually exposing new information through new columns.
SELECT o.name, c.name FROM sys.all_columns AS c INNER JOIN sys.all_objects AS o ON c.[object_id] = o.[object_id] WHERE o.[schema_id] = 4 EXCEPT SELECT o.name, c.name FROM dbo.OlderCTP_all_columns AS c INNER JOIN dbo.OlderCTP_all_objects AS o ON c.[object_id] = o.[object_id] WHERE o.[schema_id] = 4 ORDER BY o.name, c.name;
This resulted in 13 rows:
|Schema / Object||New Column|
In some cases, the columns resulting in this query are actually because the column was renamed. If you flip the two queries around, so that the
EXCEPT works in the opposite direction, you might see those, but you'll also see columns that were dropped. Between CTP 3.0 and CTP 3.1, the only column that was dropped was
sys.tables.remote_data_archive_filter_predicate; this seems to have been normalized away into the
sys.remote_data_archive_tables view, which added a new column,
And yes, I included the objects I established earlier were new, so that I could see the column names introduced there, too. If you want to leave those objects out, you could write a different query:
SELECT o.name, c.name FROM sys.all_columns AS c INNER JOIN sys.all_objects AS o ON c.[object_id] = o.[object_id] INNER JOIN dbo.OlderCTP_all_objects AS ro ON o.name = ro.name LEFT OUTER JOIN dbo.OlderCTP_all_columns AS rc ON ro.[object_id] = rc.[object_id] AND c.name = rc.name WHERE rc.[object_id] IS NULL AND ro.[schema_id] = 4 AND o.[schema_id] = 4 ORDER BY o.name, c.name;
This produced the same result as above, except for the four rows from
sys.database_scoped_configurations. The most interesting insight I gained from these results? That there is some upcoming functionality having to do with
Changed Views / Procedures
Sometimes little nuggets of information are buried in changes to catalog views, so I have this query which tells me which definitions have changed and, for the more interesting ones, I can manually look at the code to see exactly what's changed.
SELECT 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.OlderCTP_all_objects AS ro ON o.name = ro.name INNER JOIN dbo.OlderCTP_all_sql_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.name;
For CTP 3.1, this produced 9 rows (I'll leave out the
definition columns for space):
|Schema / Object||Delta|
Usually, the changes to views simply reflect the new columns I identified above. But, if you're following along at home, and you used results to grid, you can actually eyeball a comparison and find the changes pretty easily. Since results to grid eliminates all carriage returns, you can copy the 4th and 5th columns and paste them onto two adjacent lines in a new query window, then scroll over until you spot a difference. I did this for
sys.tables, and scrolled all the way over to character 2,003:
(This is a really ugly, "quick & dirty" way to do this, and not what I recommend, especially because
-- inline comments can make a mess of the syntax highlighting and, sometimes, modules can be truncated by the grid settings. You will almost certainly be better off using a schema comparison tool that supports system objects, and point it at the two instances. And in fact that is true for all of the above queries. But that's a problem for a different day.)
And sometimes I'm a little lucky
A situation that is far less common, at least for me, is coming across new syntax inadvertently through IntelliSense. I noticed
DATEDIFF_BIG() accidentally, since it appeared in a dropdown while I was typing
While this new function has since been documented (and actually it was documented the same day I made the discovery), there are other cases where this is not true. For example, if you start typing
COMPRESS, you will see this dropdown with several new compression-related functions:
THE MORAL OF THE STORY : EXPLORE
You can download the latest CTP from the SQL Server Evaluation Center. I highly encourage you to download it, install it, and snoop around. You can get a leg up on new functionality before reading it on some blog, and get a sense of what's coming before it even works.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.