Microsoft SQL Server 2017: Two Useful New Features
This week I discovered two new features in Microsoft SQL Server 2017 that I think will be helpful to both developers and DBAs. These are two small changes, but they solve problems that have been a consistent headache for a long time.
IDENTITY_CACHE
This is a database scoped parameter available in Microsoft SQL Server 2017 & in public preview for Microsoft Azure SQL Database.
Microsoft SQL Server’s default behavior (still default in 2017) is to cache identity column values to quickly supply them when an INSERT statement is run. IDENTITY_CACHE allows this behavior to be turned off or on at the Database-Level, which means when the instance is rebooted or fails over, there will be no (or fewer) gaps in the IDENTITY column values.
This is useful for a couple of people:
- DBAs who’ve been affected by this bug, or a similar issue.
- Developers who need the IDENTITY column to be a gapless, incrementing value.
The first people on that list. The DBAs. The pure of heart. They’ve got a good solution and should look forward to Microsoft SQL Server 2017.
The Developers have come one step closer to solving their problem. However, to be clear, this is still not a good idea. Microsoft SQL Server does not run INSERT statements sequentially because it’s a major bottleneck. Also, failed INSERTs will still cause the IDENTITY value to be discarded. But if you’re working with a legacy system, or an application you have no control over, this might fix some headaches for you.
The code is:
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE= [ON|OFF]
SELECT INTO ON FILEGROUP
Everyone’s favorite backup tool, SELECT … INTO is getting the ability to place the new table into the non-default FILEGROUP starting with Microsoft SQL Server 2017.
The syntax will be very simple:
SELECT ... INTO [tablename] ON FILEGROUP
It’s often a problem to copy a large table because of the extra overhead on an already overloaded drive. Other people might be dealing with a roll-your-own archive process.
This will allow the automatic creation and movement of data into a new FILEGROUP quickly and easily.
No comments