How I spot not-yet-documented features in SQL Server CTPs

Fight Club stencil by koscielny

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 create_date and 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.)

New objects

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:

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

New Columns

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:

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

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

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):

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:

Comparing changed objects the hard way

(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 DATEDIFF:

IntelliSense can be revealing

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:

Thar be gold in those autocomplete dropdowns

However, so far, only COMPRESS and DECOMPRESS have been formally documented. Others in the list do work, but I’ll leave it as an exercise for the reader to figure out what they do.

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 Bertrand

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.