Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement