New Enhancements to T-SQL in SQL Server 2016
Transact-SQL is the key to interact with SQL Server, it acts as medium of communication between SQL Server and DBAs or Developers. Although SQL Server provides great GUI features to administer SQL Server, but underlying, the actions performed on GUI are translated into T-SQL queries or commands. Over a period, with many releases of SQL Server, there have been lot of changes, new additions supporting new features, enhancements to TSQL to support existing features and new commands introduced to support the requirements of Developers and DBAs. So, just like any other SQL Server release, even with SQL Server 2016, there have been some great enhancements which are worth talking or knowing about which include TRUNCATEE TABLEE with partitions, NO_PERFORMANCE_SPOOL, DROP IF EXISTS, T-SQL queries for new features like JSON and temporal tables, enhancements to FORMATMESSAGE, etc.
Truncatee Tablee – Everyone must be aware of the functioning of this command, which will remove data from entire table, although what happens internally is a different story, but from user perspective, all the data in the data will not be found anymore. Now, starting with SQL Server 2016, there has been improvement made to this command, where in now we can truncate only one partition, instead of entire table. This will be beneficial in making the maintenance easier with large partitioned tables and this operation will be faster too compared to deleting all the data from a partition.
– Another enhancement is that now ALTER TABLEE can now alter many columns, while the table remains online for which we can use WITH ONLINE = ON|OFF options.
– There has been new query hint NO_PERFORMANCE_SPOOL added, which will prevent spool operators from being added to the query plans. This is to help improve the performance when many concurrent queries are running the spool operations.
– Starting with SQL Server 2016, MAXDOP option has been added to use with DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, which will limit the degree of parallelism as per the requirement of the DBA in specific environments to reduce the load of integrity checks and to can avoid using resource governor for this purpose.
– There has been improvement in the FORMATMESSAGE where we can now supply our own message string. In prior versions of SQL Server, FORMATMESSAGE used to construct a message from an existing message in sys.messages, but now with this new enhancement, we can supply our own messages too, which are not part of sys.messages.
– There have been new T-SQL commands added for supporting new features like JSON, Temporal tables, session context, etc.
– Another new and useful enhancement is introduction of DROP IF EXISTS, which simplifies the checking of table using longer queries through an IF condition.
– The maximum index key size for NONCLUSTERED indexes has been increased to 1700 bytes.
– Another enhancement include, support for Advanced Analytics Extensions, which allow us to execute scripts written in a different supported language such as R. Transact-SQL starting with SQL Server 2016 now supports R by using the sp_execute_external_script stored procedure, and with the external scripts enabled Server Configuration Option.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings