New T-SQL Features In SQL Server 2016 – Updated List
Application upgrades are an important procedure in order to ensure the provision of updated technology. SQL Server 2016 version is currently available in the online market as a preview type but with a number of advanced features added up to it. The enhancements include some major additions made to T-SQL, i.e. Transact-SQL that is an extension from Microsoft for the standard programming language of SQL. The developers and DBAs working with SQL planning to upgrade must be familiar with these features before official release comes into being. The segment helps explore some of the top enhancements made to Transact-SQL with the arrival of this new SQL Server version. Some of these will be MAXDOP for DBCC CHECKDB, TRUNCATE TABLE WITH PARTITION, and more. In addition to the new T-SQL features added to SQL Server 2016, the segment also focuses on the usage of these features to improve the database.
TRUNCATE TABLE WITH PARTITION
The quickest way to delete records from an SQL Server table completely is by using TRUNCATE TABLE clause. The best reason to use TRUNCATE over a DELETE statement for the mentioned purpose is that space consumption in the process is excessively minimized for transactional logging and barely any impact is made on other crucial system resources.
It is possible to truncate selective partitions of a partitioned table when using T-SQL in SQL Server version 2016 owing to the newly added clause TRUNCATE TABLE WITH PARTITION. The partitioning of table is a feature that was first brought into existence with SQL Server version 2008. However, until now, the possibility of performing individual the truncation of individual partitions was zero.
Executing the latest clause, the T-SQL syntax shown below has to be used:
ALTER TABLE WITH (ONLINE = ON | OFF)
The much needed feature by far that has been added to the SQL Server 2016 release is ALTER TABLE WITH (ONLINE = ON | OFF). The enhancement offers permission to alter data types, collation, column length, nullability along with other variables from a database table while it is actively available for the client users to connect. The option is disabled by default; however, one could use it if they wish to. The foremost need to use the feature is to specify the option ALTER TABLE WITH (ONLINE = ON) as it has been shown below:
MAXDOP for Several T-SQL CHECK Commands
The option for using MAXDOP Server configuration, i.e. Max Degree of Parallelism will be available for use with the following T-SQL commands:
- DBCC CHECKDB
- DBCC CHECKTABLE
- DBCC CHECKFILEGROUP
The provision of this option gives Database Administrators a great amount of control over the settings for the largest possible number of processors within a database server. This can further be used in parallel to executive queries.
Just in case, MAXDOP has been set to zero, then the value of maximum parallelism that is configured using SP_CONFIGURE will be chosen by SQL Server as an alternate.
Apart from these, there are a large number of other significant additions made to T-SQL in SQL Server 2016. This is not the limit because every new release introduces some new features and abilities to the server overcoming previously faced shortages and issues. However, a proper analysis and understanding of the features is important before jumping to the conclusion of upgrading to the latest available version. First, it is very important to evaluate the version and its enhancements like the ones made in T-SQL of SQL Server 2016. Doing this helps database administrators to decide the version that is better and provides improved output for increased value of the organization.