new features of sql server 2016
TRANSCRIPT
1
New developer Features of SQL Server 2012 -14 and 2016
By Mir Ahmad Mahmood
2
Agenda Developer Features (which can be used in SMARTBOOKS) SQL Server 2012 -14
SSDT Tools Programmability Enhancements: T-SQL Improvements Programmability Enhancements: New T-SQL Functions In-Memory OLTP Engine
SQL Server 2016 Drop if Exists (DIE) Dynamic Data Masking (DDM) Row level Security (RIS) Always encrypted Temporal Table JSON Support
3
What is SSDT New database Tooling in Visual Studio
SQL Server Management Studio (SSMS) Visual studio Database Professional Edition (DbPro)
Not replacing SSMS Replaces DbPro but not all
Model-Based Design SSDT does not operates directly on database instead it is in-memory
representation of database structure from on-premesis datacenter, from local Dev databases or Cloud databases.
It connects the development like SQL Server Object Explorer New panel in side Visual Studio New query window is provided as exists in SSMS Power Buffer can edit object which exists in the memory and through this we
can validate the change and automatically generates and execute the script. SSDT includes a lightweight single user instance of SQL Server for
development and testing. SSDT does not support data manipulation like no data comparison or data
generation
4
Programmability Enhancements: T-SQL Improvements THROW statement
Before THROW the RAISEERROR was used to show the error. But it requires user defined message and that message should exists in sys.message table whereas THROW does not require the error details exists in sys.messages. The THROW function error number should be greater than 50000 and severity should be 16
THROW 51000, 'The record does not exist.', 16;RAISERROR statement THROW statementIf a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The error_number parameter does not have to be defined in sys.messages.
The severity parameter specifies the severity of the exception.
There is no severity parameter. The exception severity is always set to 16.
5
Programmability Enhancements: T-SQL Improvements cont… Built-in pagination
Before SQL Server 2012 we were using row_number function for the pagination which was handle manually by the developers as shown below
;WITH PageNumbers AS(Select id, OrderNumber, row_number() over (order by ordernumber desc) rn from orders)SELECT *FROM PageNumbersWHERE rn BETWEEN 301 AND 310
SELECT id, OrderNumberFROM dbo.ordersORDER BY OrderNumber desc OFFSET 300 ROWS FETCH NEXT 10 ROWS ONLY;
6
Programmability Enhancements: T-SQL Improvements cont…. Sequences -- Create a simple sequence:
CREATE SEQUENCE dbo.ExampleSequence AS int START WITH 1 INCREMENT BY 1;
-- Create a simple/test table, too:CREATE TABLE dbo.SequentialTable ( SampleId int NOT NULL, SampleValue nvarchar(40) NOT NULL);
-- Sample/example of easiest way to grab value:SELECT NEXT VALUE FOR dbo.ExampleSequence;
-- Now copy 'next' value (2) into a parameter:DECLARE @NextSequence intSELECT @NextSequence = NEXT VALUE FOR dbo.ExampleSequence;
-- And use it for an INSERT.-- But in non-trivial examples - you could use it for FK inserts-- or other operations as well BEFORE trying the following INSERT.INSERT INTO dbo.SequentialTable (SampleId, SampleValue)VALUES (@NextSequence, '@NextSequence will have a value of 2.');
SELECT * FROM dbo.SequentialTable;GO
7
Programmability Enhancements: T-SQL Improvements cont… Projection redirection and the WITH RESULT
SETS argument. -- simple sample sproc:
CREATE PROC dbo.TestProc @input1 int, @input2 varchar(20)AS SET NOCOUNT ON
SELECT @input1 AS [Output1], @input2 [Output2]
RETURN 0GO
-- Now transform the output/projection/results:EXEC dbo.TestProc 292, 'This is Some Text'WITH RESULT SETS ( ([Column 1] int, [Column 2] varchar(20)) );
8
Programmability Enhancements: New T-SQL Functions FORMAT().
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date], FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [Full ISO], FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Long-hand Date (EN)], FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French Date], FORMAT(22.7, 'C', 'en-US') AS [US Currency], FORMAT(22.7, 'C', 'en-GB') AS [UK Currency], FORMAT(99 * 2.226, '000.000') AS [Padded Decimal], FORMAT(12345678, '0,0') AS [Finally: Commas in Large Numbers;
CHOOSE(). -- CHOOSE() makes these easy to 'format' in ad hoc
-- reports. The following returns 'Male' or position-- number 2 in the 1-based (i.e., non-0 based) array.SELECT CHOOSE(2, 'Female','Male', 'Unknown')AS [Gender];
-- Just be aware that hard-coding values into-- 'permanent' code can/will cause problems long term.-- The following returns NULL - as 4 exceeds array or is not found.SELECT CHOOSE(4,'Female','Male','Unknown') AS [x];
IIF DECLARE @P INT = NULL, @S INT = NULL; SELECT IIF ( 45 > 30, @p, @s ) AS Result;2.
Concat Select Concat (‘Smart’, ‘ ’, ‘Books’, NULL, ‘Version’ ,5)
9
In-Memory OLTP Engine It has mainly features two new data structures which are Memory-
Optimized Tables, and Natively-Compiled Stored Procedures. Memory-optimized tables
The main features of memory-optimized tables are: Rows in the table are read from, and written to, memory The entire table resides in memory Non-blocking multi-version optimistic concurrency control The option of durable & non-durable data A second copy is maintained on disk for durability (if enabled) Data in memory-optimized tables is only read from disk during database recovery It is interoperable with disk-based tables
Natively-compiled stored procedures It is compiled to native code (DLL) upon its creation (the interpreted stored
procedures are compiled at first execution) Aggressive optimizations take time at compile time It can only interact with memory-optimized tables The call to a natively-compiled stored procedure is actually a call to its DLL entry
point
10
In-Memory OLTP Engine
ALTER DATABASE AdventureWorks2012ADD FILEGROUP INMOLTP_fg CONTAINS MEMORY_OPTIMIZED_DATA;GO
ALTER DATABASE AdventureWorks2012ADD FILE (NAME='INMOLTP_fg', FILENAME='c:\temp\INMOLTP_fg') TO FILEGROUP INMOLTP_fg;GO--Memory-Optimized Table: Durable / Specifying a *_BIN2 CollationCREATE TABLE [dbo].[Product] ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000), Code VARCHAR(10) COLLATE Latinl_General_100_BIN2 NOT NULL, Description VARCHAR(200) NOT NULL, Price FLOAT NOT NULL )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);GO
The corresponding non-durable memory-optimized table for “Product” would be defined as below:
--Disk-Based TableCREATE TABLE [dbo].[Product] ( ID INT NOT NULL PRIMARY KEY, Code VARCHAR(10) NOT NULL , Description VARCHAR(200) NOT NULL , Price FLOAT NOT NULL);GO
11
In-Memory OLTP Engine
--Memory-Optimized Table: Durable / Specifying a *_BIN2 CollationCREATE TABLE [dbo].[Product] ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000), Code VARCHAR(10) NOT NULL, Description VARCHAR(200) NOT NULL, Price FLOAT NOT NULL )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);GO
Also, the corresponding durable memory-optimized table for “Product” would be defined as below, having as the only difference from the previous one the value “SCHEMA_AND_DATA” for the Durability setting:
12
In-Memory OLTP Engine
--Natively Compiled Stored Procedure for Product Table UpdateCREATE PROCEDURE [dbo].[spProductUpdate] WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) UPDATE dbo.Product SET Price = Price - ( Price * 0.10 );END;
Natively-compiled stored procedures.
13
Common Table Expressions (CTE) ;WITH ProductsCTE(ProdName,Price) AS ( SELECT ProductDesc,Price FROM PRODUCTS WHERE
Price>20.00 ) SELECT * FROM ProductsCTE
DECLARE @T INT,@I INT SET @T = 10 SET @I = 20 ;WITH ProductsCTE(ProdName,Price) AS ( SELECT ProductDesc,Price FROM PRODUCTS WHERE Price>20.00 )
SELECT @T+@I SELECT * FROM ProductsCTE
Msg 422, Level 16, State 4, Line 10 Common table expression defined but not used.
14
15
;WITH ProductsCTE(ProdName,Price) AS ( SELECT ProductDesc,Price FROM PRODUCTS WHERE Price>20.00 )
UPDATE ProductsCTE SET Price=50 WHERE ProdName='Milk' SELECT * FROM ProductsCTE
;WITH StudCTE(RollNo,StudentName,TeacherID) AS ( SELECT ID,Name,TID FROM
Student ) ,TeacherCTE(TID,TeacherName) AS ( SELECT ID,Name FROM Teacher ) SELECT RollNo,StudentName,TeacherName FROM StudCTE SC INNER JOIN TeacherCTE TC ON SC.TeacherID=TC.TID
16
Recursion in CTEDECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way' SET @T =@T+',' ;WITH MyCTE(Start,[End]) AS( SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End] UNION ALL SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T) ) Select SUBSTRING(@T,Start,[End]-Start)from MyCTE;
The OPTION MAXRECURSION enables the code to recurse only once and terminates as soon as that happens.The self explanatory message flashes and the values returned out on the results pane is: DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way' SET @T =@T+','
;WITH MyCTE(Start,[End]) AS( SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End] UNION ALL SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T) ) Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE OPTION (MAXRECURSION 1);
17
--create sequence orderids as int --minvalue 1 --cycle; --select next value for orderids; --select next value for orderids; create table MyOrders(
orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL )
DECLARE @Orders AS TABLE( orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL, shipcountry varchar(100) );
INSERT INTO @Orders(orderid, custid, empid, orderdate,shipcountry) VALUES (2, 1, 3, '20120612','Norway'),
(3, 2, 2, '20120612','Norway'), (4, 3, 5, '20120612',‘USA');
-- CTE for filtering rows -- CTE for filtering rows -- CTE for filtering rows WITH SRC AS (
SELECT * FROM @Orders WHERE shipcountry = N'Norway' ) MERGE INTO MyOrders AS TGT USING SRC ON SRC.orderid = TGT.orderid WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate); select * from MyOrders
18
SQL Server 2016 Drop if Exits (DIE) Drop if Exits (DIE)
After SQL Server 2016 you can use new DIE statements instead of big IF wrappers, e.g.:
Before SQL Server 2016 IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'<Schema
Name>.<Procedure Name>') AND TYPE IN (N'P', N'PC')) BEGIN Drop Procedure <Schema Name>.<Procedure Name>') END
After SQL Server 2016 DROP TABLE IF EXISTS '<Schema Name>.<Table Name>')
DROP Procedure IF EXISTS '<Schema Name>.<Procedure Name>')
19
`
20
SQL Server 2016 Dynamic Data Masking Dynamic data masking helps prevent unauthorized access
to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.
21
22
SQL Server 2016 Row Level Security Row-Level Security enables customers to control access to
rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).
Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer's data access to only the data relevant to their company.
23
24
SQL Server 2016 Always EncryptedAlways Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server).
25
SQL Server 2016 Temporal Table A temporal table is table that holds old versions of rows
within a base table.
26
SQL Server 2016 JSON Support JSON stands for Java Script Object Notation. With SQL
Server 2016 you can now interchange JSON data between applications and the SQL Server database engine.
27
References http://
devproconnections.com/database-development/sql-server-2012-top-new-features-net-developers
https://mcpmag.com/articles/2012/03/14/top-12-features-of-sql-server-2012.aspx
https://app.pluralsight.com/library/courses/sql-server-2014-admin-new-features/table-of-contents
https://app.pluralsight.com/library/courses/sql-server-2016-new-features-developers/table-of-contents
https://www.mssqltips.com/sqlservertip/2643/sql-server-2012-throw-statement-introduction/
https://msdn.microsoft.com/en-us/library/hh213019.aspx https://www.simple-talk.com/sql/learn-sql-server/introducing-sql-server-in-m
emory-oltp/
http://www.databasejournal.com/features/mssql/slideshows/10-new-features-worth-exploring-in-sql-server-2016.html
http://sqlmag.com/sql-server-2014/application-performance-inmemory-oltp-database-engine
https://msdn.microsoft.com/en-us/library/dn765131.aspx
28
Question & Answers