TSQL Enhancements in SQL Server 2016

TSQL Enhancements.sql

Use TestDB

-- Before SQL Server 2016
IF EXISTS(SELECT * FROM dbo.Employee)
DROP TABLE dbo.Employee

IF EXISTS(SELECT * FROM SYS.TABLES WHERE NAME ='dbo.Employee')
DROP TABLE dbo.Employee

--------------------------------------------------------------------------------------------------

-- New syntax in SQL Server 2016 - DROP IF EXISTS
DROP TABLE IF EXISTS dbo.Employee

--------------------------------------------------------------------------------------------------

-- Create a sample table
CREATE TABLE dbo.Employee
(  
   EmployeeID		INT IDENTITY PRIMARY KEY,
   FirstName		VARCHAR(50),
   LastName         VARCHAR(50),
   Age              INT,
   PhoneNumber      VARCHAR(12),
   Email            VARCHAR(50),
   SSN              VARCHAR(16),
   CreditCardNumber VARCHAR(16) 
 )


 INSERT INTO dbo.Employee
(FirstName, LastName, Age, PhoneNumber, Email, SSN, CreditCardNumber) VALUES 
('Samir', 'Behara', '20', '1234567890', 'samirbehara@gmail.com', '987-123-3241', 'AWE123UJK5438765'),
('Peter', 'Denner', '30', '0987654321', 'peterdenner@yahoo.com', '765-234-9812', '1928EYHJ758492UE'),
('Dennis', 'Knox',  '40', '1122334455', 'dennisknox@hotmail.com','124-432-8965', '1234UEHW5678TEUJ');

 SELECT * FROM  dbo.Employee

--------------------------------------------------------------------------------------------------


-- New syntax in SQL Server 2016 for conditionally dropping column
 ALTER TABLE dbo.Employee
 DROP COLUMN IF EXISTS Address,
      COLUMN IF EXISTS CreditCardNumber

SELECT * FROM  dbo.Employee

---------------------------------------------------------------------------------------------------


-- String splitting function
SELECT * FROM STRING_SPLIT('SQL SERVER 2016 ROCKS', ' ')

DROP TABLE IF EXISTS dbo.EmployeeSkills


--Create a sample table
CREATE TABLE dbo.EmployeeSkills
(  
   EmployeeID		INT IDENTITY PRIMARY KEY,
   FirstName		VARCHAR(50),
   Email            VARCHAR(50),
   Skills           VARCHAR(100) 
 )


 INSERT INTO dbo.EmployeeSkills
(FirstName, Email, Skills) VALUES 
('Samir', 'samirbehara@gmail.com', '.Net, SQL' ),
('Peter', 'peterdenner@yahoo.com', 'Java, COBOL' ),
('Dennis','dennisknox@hotmail.com','Oracle, C#');

 SELECT * FROM  dbo.EmployeeSkills

 -- Split comma separated value string in a table column
 SELECT EmployeeID, FirstName, Email , value
 FROM dbo.EmployeeSkills
 CROSS APPLY string_split(Skills, ',')

----------------------------------------------------------------------------------------------------


SELECT DATEDIFF(MILLISECOND, '20000101','20160101') -- Return type is INT
--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.

SELECT DATEDIFF_BIG(MICROSECOND, '20000101','20160101') -- Return type is BIGINT

SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+5)

-----------------------------------------------------------------------------------------------------

--Demonstration of COMPRESS and DECOMPRESS Features
SELECT COMPRESS ('New Features of SQL Server 2016')

SELECT CAST(DECOMPRESS(0x1F8B0800000000000400F34B2D57704B4D2C292D4A2D56C84F53080EF451084E2D2A4B2D52303230340300FD90F96A1F000000) AS VARCHAR(MAX))