In this article, I would like to describe several new T-SQL features available in SQL Server 2016: DROP IF EXISTS, SESSION_CONTEXT, MAXDOP, FORMATMESSAGE, COMPRESS and DECOMPRESS, DATEDIFF_BIG, AT TIME ZONE, FOR JSON, and others.
Contents:
1. DROP IF EXISTS
2. SESSION_CONTEXT
3. MAXDOP option in DBCC CHECKDB
4. FORMATMESSAGE
5. COMPRESS and DECOMPRESS
6. DATEDIFF_BIG
7. AT TIME ZONE
8. JSON
9. ALTER COLUMN ONLINE
10. TRUNCATE (partitioned) TABLE
11. CURRENT_TRANSACTION_ID
1. Objects Can DIE: the New DROP IF EXISTS Feature
CREATE TABLE dbo.Customer ( cust_id INT PRIMARY KEY ,cust_age INT ,cust_name CHAR(50) CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 AND 10000) ,INDEX ind CLUSTERED (cust_id) )
To drop the table, we need to execute the following code:
IF OBJECT_ID('dbo.Customer, 'U') IS NOT NULL
DROP TABLE dbo.Customer;
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trProductInsert')
DROP TRIGGER trProductInsert
Starting from SQL Server 2016 CTP3 you can use new DROP IF EXISTS statement instead of IF wrappers. In case an object does not exist, DROP IF EXISTS will not fail and execution will continue.
DROP TABLE IF EXISTS dbo.Customer DROP TRIGGER IF EXISTS trProductInsert
Also, there is an ability to delete child elements:
ALTER TABLE dbo.Cusromer DROP COLUMN IF EXISTS cust_name ALTER TABLE dbo.Cusromer DROP CONSTRAINT IF EXISTS chk_id ALTER TABLE dbo.Cusromer DROP CONSTRAINT IF EXISTS ind
DROP IF EXISTS is available for the following objects:
| AGGREGATE | PROCEDURE | TABLE |
| ASSEMBLY | ROLE | TRIGGER |
| VIEW | RULE | TYPE |
| DATABASE | SCHEMA | USER |
| DEFAULT | SECURITY POLICY | VIEW |
| FUNCTION | SEQUENCE | |
| INDEX | SYNONYM |
2. SESSION_CONTEXT
ASP.NET provides the Session object. You can use this object to store and retrieve values for a user as they browse ASP.NET pages in a web application within a particular session.
There are a number of reasons why you need to use session variables within SQL Server. For example, you may need to identify users for auditing purposes, or you need to preserve some information that can be accessible within a module, such as a trigger or view, which does not accept parameters. Or you simply want to maintain session-scoped data that is costly to look up.
SQL Server 2016 allows you to store multiple key and value pairs that are accessible within a particular session. These pairs can be set with help of the sp_set_session_context system stored procedure and retrieved (one at a time) with help of the SESSION_CONTEXT built-in function.
Before SQL Server 2016, you only could use CONTEXT_INFO(). Howevet, it has a number of restirctions:
- It is complicated to store multiple values in a single binary representation.
- It is limited to 128 bytes per connection.
- Data can be overwritten by a user at any time. This is kind of a security problem.
- Azure SQL Database does not support the same behaviour. In case no value has been set, it returns a random GUID.
Setting a Session Variable
This is a demo:
DECLARE @user_ID INT = 128;
EXEC sys.sp_set_session_context @key = N'user_ID'
,@value = @user_ID;
SELECT
SESSION_CONTEXT(N'user_ID');
The result is 128.
Trying to Update a Read-only Value
We can use the @read_only argument to set a read only key:
DECLARE @ID INT = 128; EXEC sys.sp_set_session_context @key = N'user_ID', @value = @user_ID, @read_only = 1;
Trying to update:
EXEC sys.sp_set_session_context @key = N'user_ID', @value = 256;
We get the following error:
Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 10 Cannot set key ‘user_ID’ in the session context. The key has been set as read_only for this session.
3. The new MAXDOP option in DBCC CHECKDB
By default, the number of threads checking the logical and physical integrity of all the objects in a specified database is equal to the number of logical CPU cores. The new MAXDOP feature allows you to manage the number of threads, to maintain overall server performance at the appropriate level.
DBCC CHECKDB(N'DatabaseName') WITH MAXDOP = 4
The same functionality is available for DBCC CHECKTABLE and DBCC CHECKFILEGROUP
USE DatabaseName
GO
DBCC CHECKTABLE('dbo.TableName') WITH MAXDOP = 4
DBCC CHECKFILEGROUP(1) WITH MAXDOP = 4
MAXDOP overrides the maximum degree of parallelism configuration option of sp_configure for the statement. The MAXDOP can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP. All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.
4. FORMATMESSAGE
In previous versions, FORMATMESSAGE constructed a message from strings located insys.messages. Now you can also supply your own string:
5. COMPRESS and DECOMPRESS
Another interesting feature is the ability to compress and decompress fields during DML (either select, insert, or update) using the COMPRESS and DECOMPRESSfunctions. Compressed fields cannot be indexed. It is a good option if you are not going to perform search across the compressed fields.
Here is an example of compression:
-- Step1. Create a test table.
CREATE TABLE NoCompression
(ID INT IDENTITY(1,1),
HugeField varchar(max) )
GO
-- Step2. Optimize large values storage
exec sp_tableoption 'NoCompression' , 'Large Value Types out of row',true
GO
-- Step 3. Insert 1000 records
INSERT INTO NoCompression (HugeField) values (replicate('No compression example',1000))
GO 1000
-- Step 4. Check the total pages of the table
SELECT
allocation_unit_type_desc,
page_type_desc, count(*) AS TotalPages
FROM sys.dm_db_database_page_allocations
(DB_ID('test'),OBJECT_ID('WithoutCompress'),0,1,'DETAILED')
GROUP BY allocation_unit_type_desc,page_type_desc
ORDER BY allocation_unit_type_desc
We have 1012 Text_Mix_Page, besides the other types of pages. Let’s compress fields:
-- Step1. Create a test table.
CREATE TABLE WithCompression
(ID INT IDENTITY(1,1),
HugeField varchar(max) )
GO
-- Step2. Optimize large values storage
exec sp_tableoption 'WithCompression' , 'Large Value Types out of row',true
GO
-- Step 3. Insert 1000 records
INSERT INTO WithCompression (HugeField) values (Compress(replicate('Compress example',1000)))
GO 1000
-- Step 4. Check the total pages of the table
SELECT
allocation_unit_type_desc,
page_type_desc, count(*) AS TotalPages
FROM sys.dm_db_database_page_allocations
(DB_ID('test'),OBJECT_ID('WithoutCompress'),0,1,'DETAILED')
GROUP BY allocation_unit_type_desc,page_type_desc
ORDER BY allocation_unit_type_desc
Now you can clearly see the result of the COMPRESSION feature – we have 24 TEXT_MIX_PAGEs. To decompress the information:
SELECT TOP 10 DECOMPRESS(HugeField) AS HugeField FROM WithCompression
The DECOMPRESS function result type is VARBINARY. You need to case the result to see the original data.
SELECT TOP 10 CAST(DECOMPRESS(HugeField) AS VARCHAR(MAX)) AS HugeField from WithCompress
The compression utilizes the GZIP algorithm. You can decompress the data in the client application, rather than in the query.
6. DATEDIFF_BIG
Erland Sommarskog requested this feature back in 2008 on the Microsoft connect website. DATEDIFF_BIG returns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate. Previous versions of SQL Server show the following error (When the date range is set too high):
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Now you can use:
DATEDIFF_BIG ( datepart , startdate , enddate )
7. AT TIME ZONE
Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules. AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.
With this function you can display the time in the specified time zone:
SELECT CONVERT(DATETIME2, GETDATE()) AT TIME ZONE N'Pacific SA Standard Time'
Result:
———————————-
2016-02-07 17:54:02.1354687 -03:00
8. JSON
I have already written about JSON Support in SQL Server 2016. SQL Server 2016 along with XML, now supports the JSON format. You can convert tabular data to JSON using the FOR JSON clause.
9. ALTER COLUMN ONLINE
ALTER TABLE can now alter many columns while the table remains online, using WITH (ONLINE = ON | OFF).
DROP TABLE IF EXISTS dbo.DemoTable
CREATE TABLE dbo.DemoTable (colName VARCHAR(255) NULL)
GO
ALTER TABLE dbo.DemoTable
ALTER COLUMN colName VARCHAR(255) NOT NULL
WITH (ONLINE = ON)
GO
ALTER TABLE dbo.DemoTable
ALTER COLUMN colName NVARCHAR(255)
COLLATE Cyrillic_General_100_CI_AS NOT NULL
WITH (ONLINE = ON)
10. TRUNCATE (partitioned) TABLE
Now it removes all rows from a table or specified partitions of a table, without logging the individual row deletions. The following code truncates specified partitions of a partitioned table. The WITH (PARTITIONS (2, 4, 6 TO 8)) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.
TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2, 4, 6 TO 8)); GO
11. CURRENT_TRANSACTION_ID
The function returns the transaction ID of the current transaction in the current session. The following code returns the transaction ID of the current session:
SELECT CURRENT_TRANSACTION_ID();
Summary
There is no doubt it is going to be a very interesting release. We are anticipating it!
Useful tool:
SQL Complete – write, beautify, refactor your code easily and boost your productivity.
Tags: sql server 2016, t-sql Last modified: October 06, 2021
