SQL Server 2016, currently available in preview versions, comes with many new features, including a variety of enhancements to Transact-SQL (T-SQL), Microsoft's extension of the standard SQL programming language. Database administrators and developers looking to upgrade should become familiar with the new T-SQL options before SQL Server 2016 is officially released. This article explores some of the major Transact-SQL enhancements available in the new edition of SQL Server, such as the ability to use MAXDOP for DBCC CHECKDB, and how to use them to improve your databases.
TRUNCATE TABLE WITH PARTITION
TRUNCATE TABLE is the fastest option to delete all the records from a SQL Server table. The key advantage of using a TRUNCATE statement over a DELETE statement is that it uses minimal transactional log file space and has very little impact on other valuable system resources.
When using the new TRUNCATE TABLE WITH PARTITION clause that has been added to T-SQL in SQL Server 2016, you can truncate specific partitions in a partitioned table. While the table partitioning feature was initially introduced in SQL Server 2008, until now, there was no option available to truncate individual partitions.
To take advantage of the new clause, use the following T-SQL syntax:
Before using TRUNCATE TABLE WITH PARTITION in SQL Server 2016, though, make sure all the indexes in a table are aligned with its partitions. Otherwise, you won't be able to successfully truncate the table by partitions.
DROP IF EXISTS
SQL Server 2016 also introduces a new DROP IF EXISTS syntax option for DROP statements. It's used to check whether an object exists before it is dropped, or removed, from a database. This enhancement to T-SQL is applicable for many types of objects, such as tables, indexes, columns, schemas, stored procedures, triggers and user-defined functions.
In the previous versions of SQL Server, you had to write a T-SQL script similar to the one below to check whether, say, a table exists before dropping it:
Starting with SQL Server 2016, the above statement can be rewritten more simply to achieve the same functionality:
ALTER TABLE WITH (ONLINE = ON | OFF)
SQL Server 2016 brings in the much-needed option ALTER TABLE WITH (ONLINE = ON | OFF). This T-SQL enhancement allows you to alter data types, column length, collation, nullability and other variables in a database table while the table remains available for end-user connectivity. By default, this feature is turned off. If you would like to utilize it, you need to specify the ALTER TABLE WITH (ONLINE = ON) option as demonstrated in the T-SQL code below.
MAXDOP for DBCC CHECKDB, CHECKTABLE and CHECKFILEGROUP
Starting with SQL Server 2016, the MAXDOP (max degree of parallelism) server configuration option will be available for use with T-SQL's DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP commands. This will help database administrators (DBAs) to control settings for the maximum number of processors in a database server that can be used in parallel to executive queries. However, if MAXDOP is set to zero, SQL Server will choose the currently configured value of maximum parallelism set using SP_CONFIGURE.
Follow this syntax to utilize the MAXDOP option:
AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES
SQL Server 2016 introduces two new options for modifying database file groups via the ALTER DATABASE command: AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES. They replace trace flag 1117, a switch-like setting that can be used in earlier versions to maintain a consistent data-file size across a file group when its capacity is expanded via SQL Server's auto-grow feature. The disadvantage of using the trace flag is that it automatically increases the size of each file for all of the databases in a SQL Server instance that have files defined as part of a file group.
Starting with SQL Server 2016, AUTOGROW_SINGLE_FILE is the default setting for when a database is created. Under that option, when a data or log file in a file group meets the automatic growth threshold set by a DBA, only that particular file grows, leaving the rest of the files the same size they were before.
On the other hand, if you want to grow all of the data or log files in a file group equally when one reaches the growth threshold, you can do so by leveraging the AUTOGROW_ALL_FILES option, which lets you limit the changes to a specific database within an instance. When using this option, it is recommended that you set the files to grow in fixed sizes (for example, megabyte or gigabyte) rather than by a percentage to mitigate the risk of T-SQL statement failures if the current file size is already large. Note that in SQL Server 2016, the initial default size of data and log files is 8 MB, while the default auto-grow addition is 64 MB per file.
COMPRESS and DECOMPRESS
Also new to T-SQL in SQL Server 2016 are two built-in system functions for compressing and decompressing data at a more granular level than SQL Server 2014 supports. The COMPRESS function can be used to compress input statements via the GZIP file-compression algorithm, generating VARBINARY(MAX) byte arrays. DECOMPRESS lets DBAs and developers decompress binary data using GZIP. However, to derive the actual data value during decompression, you need to use T-SQL's CAST command to express the result as a specific target type. Execute the T-SQL script below to view the results of the COMPRESS and DECOMPRESS functions.
STRING_SPLIT and STRING_ESCAPE
In addition, SQL Server 2016 includes two new string functions, STRING_SPLIT and STRING_ESCAPE.
Using STRING_SPLIT, you can split a character expression in T-SQL by specifying a separator that will divide the string of code into a single-column table, using the following syntax:
STRING_ESCAPE lets you convert special characters that can cause error messages in T-SQL text strings into understandable code. Initially, the function can only escape a set of JSON special characters, including quote marks and backspace, return and tab commands. To take advantage of it, use the following syntax:
Don't just get swept away by these T-SQL enhancements, though. Every new release of SQL Server comes with some exciting new features, but that doesn't mean you should automatically upgrade. Before you jump in on SQL Server 2016, evaluate the additions to T-SQL and the other new functionality the database has to offer and decide which version of SQL Server provides the maximum value for your organization.
Dynamic Data Masking is a new security feature for SQL Server 2016
SQL Server 2016 has enhancements to its AlwaysOn Availability Groups
In-memory OLTP is new and improved in SQL Server 2016