SQL Server® 2016 has many new features and enhancements. This is a trend with most software companies—that the much-anticipated next release holds something to wow every enthusiast. In that aspect, this release of SQL Server 2016 is no different for me. Every major release of SQL includes a little something for developers. After taking a tour of the release, I came across some interesting features and capabilities. Here are few of them:
COMPRESS / DECOMPRESS
These are new string functions, which were introduced in the SQL Server 2016 release.
- COMPRESS: This function compresses data and returns binary data, using GZip algorithm.
- DECOMPRESS: This function decompresses binary data using GZip algorithm, and returns binary data.
Here is the script to understand the usage:
SET NOCOUNT ON
GO
USE tempdb
GO
DROP TABLE IF EXISTS Employee;
GO
CREATE TABLE Employee (
id INT PRIMARY KEY identity
,NAME NVARCHAR(max)
,surname NVARCHAR(max)
,info VARBINARY(max)
)
GO
INSERT INTO Employee (
NAME
,surname
,info
)
VALUES (
'Rob'
,'Mannison'
,COMPRESS('SQL Server')
)
SELECT id
,info
,cast(DECOMPRESS(info) AS VARCHAR(max)) 'Decompress'
FROM Employee
GO
As shown in output below, the info column has compressed value. Once we use DECOMPRESS, we can get back the same value.
Before implementing, we need to evaluate the data that we are trying to compress. It is common that small data is larger because of the base “overhead” of your compression scheme.
DECLARE @STR VARCHAR(MAX) SELECT @STR = 'SQL SERVER' SELECT DATALENGTH(@STR) 'Original', DATALENGTH(COMPRESS(@STR)) 'Compressed' Original Compressed -------------------- -------------------- 10 30
As shown above, the compressed data might take more space than regular data. This has to be evaluated on a case-by-case basis before implementing.
DMV – sys.time_zone_info
If you have developed an application that is used by end-users working across several time zones, you can understand the benefit of this new catalog view. There is always a complexity when it comes to handling dates and times in SQL Server along with the time zone. Then, of course, Daylight saving adds another dimension to the problem.
In SQL Server 2016, we can immediately get information about supported time zones. The information is retrieved from the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones, as shown below:
Here is the query used:
Select * from sys.time_zone_info order by name
It can be used in conjunction with the AT TIME ZONE clause to get the datetime offset value. This is also a new addition in SQL Server 2016, which converts an input date/datetime to the corresponding datetimeoffset value in the specified time zone.
DROP IF EXISTS
IF OBJECT_ID('[MySchema].[MasterTable]', 'U') IS NOT NULL
DROP TABLE [MySchema].[MasterTable];
Basically, the purpose of the script is to check if the table exists. If it does, drop it. With SQL Server 2016, the same script can be rewritten as:
DROP TABLE IF EXISTS [MySchema].[MasterTable];
This is possible for the objects below (as of CTP3.1):
AGGREGATE, PROCEDURE, TABLE, ASSEMBLY, ROLE, TRIGGER, VIEW, RULE, TYPE, DATABASE, SCHEMA, USER, DEFAULT, SECURITY, POLICY, VIEW, FUNCTION, SEQUENCE, COLUMN, INDEX, SYNONYM
Along with objects, this is also available for COLUMN and CONSTRAINT in ALTER TABLE statement.
ALTER TABLE DROP IF EXISTS COLUMN
ALTER TABLE DROP IF EXISTS CONSTRAINT
Conclusion
Microsoft has been listening to feedback from developers as well. Hopefully by the RTM release of SQL Server 2016, we will see more features and enhancements.
Leave a Reply