Manage Learn to apply best practices and optimize your operations.

SQL Server 2016 T-SQL features add to DBCC CHECKDB and more

Expert Ashish Kumar Mehta shares some of the enhancements to T-SQL ahead in SQL Server 2016. This includes a DROP IF EXISTS option and MAXDOP for DBCC CHECKDB.

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:

TRUNCATE TABLE

For example:

TRUNCATE TABLE WITH PARTITION

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:

DROP IF EXISTS

Starting with SQL Server 2016, the above statement can be rewritten more simply to achieve the same functionality:

DROP TABLE

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.

ALERT TABLE

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:

MAXDOP for DBCC CHECKDB

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.

COMPRESS and DECOMPRESS

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_SPLIT

For example:

SELECT STRING_SPLIT

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:

STRING_ESCAPE

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.

Next Steps

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

This was last published in April 2016

Essential Guide

SQL Server 2016 release guide: News and analysis on the new version

Join the conversation

3 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

How will you use the new T-SQL enhancements for SQL Server 2016?
Cancel
industries batabase managment system
Cancel
Looks like there should be some nice improvements. Now if we only upgrade to take advantage of them.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close