Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

What's New In SQL Server For The Developer

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

jssug.org

Every 3rd Wednesday 6-8pm

Except for May, November and December

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

What's New In SQL Server For The Developer

Questions

What is your role?
Which version of SQL Server do you currently use?
What Edition of SQL Server do you currently use?

SQL Server 2012

IIF

DECLARE @FirstArgument INT = 10;
DECLARE @SecondArgument INT = 20;
SELECT
    IIF(@FirstArgument > @SecondArgument, 'TRUE', 'FALSE') AS [TrueFalse],
    IIF(@FirstArgument > @SecondArgument, 1, 0) AS [BitResult];

IIF

DECLARE @StudentGrades TABLE 
(
	Name VARCHAR(100), 
	Mark INT
)
INSERT @StudentGrades VALUES('Dilbert',98)
INSERT @StudentGrades VALUES('Pointy-Haired Boss',64)
INSERT @StudentGrades VALUES('Dogbert',83)
INSERT @StudentGrades VALUES('Wally',75)
INSERT @StudentGrades VALUES('Asok',65)
 
SELECT 
	Name, 
	IIF( Mark BETWEEN 90 AND 100, 'A Grade',
	IIF( Mark BETWEEN 80 AND 89,  'B Grade',
	IIF( Mark BETWEEN 70 AND 79,  'C Grade',
	IIF( Mark BETWEEN 66 AND 69,  'D Grade',
	IIF( Mark < 65, 'Failed', 'E Grade' ))))) AS Grade
FROM 
	@StudentGrades

Choose

Similar concept to coding enums

SELECT TOP 5
    DATEPART(DD, l.[Date]) AS [Date],
    CHOOSE(DATEPART(MM, l.[Date]), 'January', 'February', 'March', 'April', 'May', 'June', 
                                   'July', 'August', 'September', 'October', 'November', 
                                   'December') [Month],
    DATEPART(YYYY, l.[Date]) AS [Year]
FROM
    dbo.[Log] AS l
ORDER BY
    l.Id DESC;

Choose

SELECT 
    COUNT(l.id) AS LoggedIssues,
    CHOOSE(DATEPART(MM, l.[Date]), 'January', 'February', 'March', 'April', 'May', 'June',
                                   'July', 'August', 'September', 'October', 'November', 
                                   'December') [Month],
    DATEPART(YYYY, l.[Date]) AS [Year],
    IIF(DATEPART(MM, l.[Date]) = 1 or DATEPART(MM, l.[Date]) BETWEEN 10 AND 12, 
                            'Holiday Season', 'Regular Season') AS HolidaySeason,
    CHOOSE(MONTH(l.[Date]),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer', 
                           'Summer','Autumn','Autumn','Autumn','Winter') AS WeatherSeason
FROM
    dbo.[Log] AS l
WHERE
	l.[Date] IS NOT NULL
GROUP BY
    DATEPART(MM, l.[Date]),
    DATEPART(YYYY, l.[Date])
ORDER BY
    1 desc

Choose

Declares @DaysOfWeek TABLE (DayOfWeek INT)
INSERT @DaysOfWeek (DayOfWeek) VALUES (1), (2), (3), (4), (5), (6), (7);

SELECT
	[DayOfWeek],
 
	CHOOSE([DayOfWeek],'Sunday','Monday', 'Tuesday', 'Wednesday','Thursday','Friday'
                           ,'Saturday') AS [Choose-Function],
 
	IIF([DayOfWeek] = 1, 'Sunday', 
		IIF([DayOfWeek] = 2, 'Monday', 
			IIF([DayOfWeek] = 3, 'Tuesday', 
				IIF([DayOfWeek] = 4, 'Wednesday', 
					IIF([DayOfWeek] = 5, 'Thursday', 
						IIF([DayOfWeek] = 6, 'Friday', 
                                                    'Saturday')))))) AS [IIF-Function],
 
	CASE [DayOfWeek]
		WHEN 1 THEN 'Sunday'
		WHEN 2 THEN 'Monday'
		WHEN 3 THEN 'Tuesday'
		WHEN 4 THEN 'Wednesday'
		WHEN 5 THEN 'Thursday'
		WHEN 6 THEN 'Friday'
		WHEN 7 THEN 'Saturday'
	END AS [Case-Function]
FROM
	@DaysOfWeek

Try_Convert()

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Cast failed

NULL

Try_PARSE()

SELECT id, somevalue FROM dbo.TestTry

SELECT CONVERT(INT, id) AS IntegerValue, somevalue FROM dbo.TestTry

SELECT CONVERT(INT, id) AS IntegerValue, somevalue FROM dbo.TestTry WHERE ISNUMERIC(id) = 1;

SELECT 
    CONVERT(INT, id) AS IntegerValue, somevalue 
FROM 
    dbo.TestTry 
WHERE 
    ISNUMERIC(TRY_PARSE(id AS int)) = 1;

Format

SELECT
    FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date],
    FORMAT(GETDATE(), 'MMMM dd, yyyy hh:mm:ss') AS [Custom 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]

Dates

DECLARE @d DATETIME = '05/01/2015';
SELECT 
    FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result',
    FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result',
    FORMAT ( @d, 'D', 'de-de' ) AS 'German Result',
    FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

Format

DECLARE @datePattern DATETIME
SET @datePattern=GETDATE()
 
SELECT 
    FORMAT(@datePattern, 'M') AS MonthDayPattern
    ,FORMAT(@datePattern, 'M', 'hr-HR') AS MmLanguage
    ,FORMAT(@datePattern, 'Y') AS MonthYear
    ,FORMAT(@datePattern, 's') AS SortableDateTime
    ,FORMAT(@datePattern, 'T') AS LongTime
    ,FORMAT(@datePattern, 't') AS ShortTime
    ,FORMAT(@datePattern, 'U') AS UniversalTimeFull
    ,FORMAT(@datePattern, 'u') AS UniversalTimeShort
    ,FORMAT(@datePattern, 'O') AS RoundTripPattern_SameOutputPattern
    ,FORMAT(@datePattern, 'R') AS RFC1123Pattern_SameOutputPattern

Dates

Format

DECLARE @Era DATETIME
SET @Era=GETDATE()
 
SELECT 
    FORMAT(@Era, 'MM/dd/yyyy g') AS Era
    ,FORMAT(@Era, 'MM/dd/yyyy g', 'fr-FR') as FrenchEra

Dates

Format

DECLARE @1224hour DATETIME
SET @1224hour='5/9/2001 4:54:08 PM'
SELECT 
    FORMAT(@1224hour, 'h ') AS [12hr-4]
    ,FORMAT(@1224hour, 'h:m:s') AS [12hr-h:m:s]
    ,FORMAT(@1224hour, 'h:m:s', 'fr-FR') AS [12hr-4:54:8]
    ,FORMAT(@1224hour, 'hh') AS [12hr-04]
    ,FORMAT(@1224hour, 'hh:m:s') AS [12hr-04:54:8]
    ,FORMAT(@1224hour, 'hh:m:s', 'fr-FR') AS [12hr-04:54:8]
SELECT
    FORMAT(@1224hour, 'H ') AS [24h-16]
    ,FORMAT(@1224hour, 'H:m:s') AS [24h-16:54:8]
    ,FORMAT(@1224hour, 'H:m:s', 'fr-FR') AS [24h-16:54:8]
    ,FORMAT(@1224hour, 'HH') AS [24h-16]
    ,FORMAT(@1224hour, 'HH:m:s') AS [24h-16:54:8]
    ,FORMAT(@1224hour, 'HH:m:s', 'fr-FR') AS [24h-16:54:8]

Time

Format

SELECT
    FORMAT(22.7, 'C', 'en-US') AS [US Currency],
    FORMAT(222460.7, 'C', 'en-US') AS [US Currency],
    FORMAT(1222460.7, 'C', 'en-US') AS [US Currency],
    FORMAT(22.7, 'C', 'en-GB') AS [UK Currency],
    FORMAT(222460.7, 'C', 'en-GB') AS [UK Currency],
    FORMAT(1222460.7, 'C', 'en-GB') AS [UK Currency]

Currency

Format

--Fixed Point to String
DECLARE @fp FLOAT = 18934.1879
SELECT 
    FORMAT(@fp, 'F') AS F,
    FORMAT(@fp, 'F1') AS F1,
    FORMAT(@fp, 'F2') AS F2,
    FORMAT(@fp, 'F3') AS F3,
    FORMAT(@fp, 'F5') AS F5,
    FORMAT(-@fp, 'F0') AS F0

Fix Point

Format

DECLARE @p FLOAT =.2468013
SELECT 
    FORMAT(@p, 'P') AS PercentValue,
    FORMAT(@p, 'P', 'hr-HR') AS CroatiaPercentValue

Percent/Decimal

DECLARE @dp FLOAT = 34.5
SELECT 
    FORMAT(@dp, '0.0') AS OneDecimalPlace,
    FORMAT(@dp, '00') AS NoDecimalPlace,
    FORMAT(@dp, '0000') AS PaddedNoDecimal,
    FORMAT(@dp, '0000.00') AS PaddedDecimalPlace

Format

DECLARE @h INT = 123456789
SELECT 
    FORMAT(@h, 'X') AS Hex,
    FORMAT(@h, 'x') AS HexLower,
    FORMAT(@h, 'X12') AS Hex12

HEX/Degrees/Number

DECLARE @t FLOAT = 68    
SELECT FORMAT(@t, '# "degrees"') AS [Degrees]

DECLARE @num FLOAT = 012.30
SELECT FORMAT(@num, '###.##') AS NumberFormat

Format

DECLARE @posValue FLOAT = 1234;
DECLARE @negValue FLOAT = -1234;
DECLARE @zeroValue FLOAT = 0;    
SELECT 
    FORMAT(@posValue, '##;(##)') AS Positive,
    FORMAT(@negValue, '##;(##)') AS Negative,
    FORMAT(@zeroValue, '##;(##);N/A') AS Zero

Pos & Neg/Phone

DECLARE @phonenumber INT = 1234567890
SELECT 
    FORMAT(@phonenumber, '(###) ###-####') AS PhoneNumberFormat1,
    FORMAT(@phonenumber, '###-###-####') AS PhoneNumberFormat2,
    FORMAT(@phonenumber, '###\.###\.####') AS PhoneNumberFormat3

Try/Catch

--Set Your own, not required to use sys.messages
--Use in CLR
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;

BEGIN TRANSACTION
BEGIN TRY
    SELECT @ErrorMessage = 'Because I want to...', @ErrorSeverity = 500001, 
           @ErrorState = 1;
    THROW @ErrorSeverity, @ErrorMessage, @ErrorState;

    DECLARE @RecordCount INT = 1, @Updatecount INT = 0;
    IF @RecordCount <> @Updatecount
    BEGIN
    	SELECT @ErrorMessage = 'You cannot divide by zero.', 
               @ErrorSeverity = 500001, @ErrorState = 1;
	THROW @ErrorSeverity, @ErrorMessage, @ErrorState;
    END
END TRY
BEGIN CATCH
    PRINT 'FAILURE. AUTOMATIC ROLLBACK TRANSACTION'
    ROLLBACK TRANSACTION;
    THROW
END CATCH

Throw - New

Sequence

CREATE SEQUENCE dbo.LogSequence AS int
        START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 
GO

DECLARE @NextSequence int
SET  @NextSequence = NEXT VALUE FOR dbo.LogSequence;

INSERT INTO dbo.LogTable (LogId, LogValue)
VALUES (@NextSequence, 
       '@NextSequence will have a value of '+ CAST(@NextSequence AS VARCHAR) + '.')

SELECT current_value FROM sys.sequences WHERE name = 'LogSequence';

Sequence

CREATE SEQUENCE dbo.TicketSequence AS int
        START WITH -2147483648 INCREMENT BY 1;

SELECT current_value FROM sys.sequences WHERE name = 'TicketSequence';

INSERT INTO dbo.ServiceTickets (Department) VALUES ('M')

CREATE TABLE dbo.ServiceTickets
(
 TicketID INT PRIMARY KEY CLUSTERED DEFAULT NEXT VALUE FOR TicketSequence,
 Department CHAR(1) NOT NULL CHECK (Department IN ('M', 'F'))
) ON [FGData];

CREATE SEQUENCE [dbo].[TicketSequence] 
 AS [INT]
 START WITH -2147483648
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 
GO

Sequence

USE [Utility];
GO
DECLARE
    @CurrentMax INT,
    @CreateSequence VARCHAR(MAX);
SELECT
    @CurrentMax = CAST(current_value AS INT)
FROM
    sys.sequences
WHERE
    name = 'LogSequence';
DROP SEQUENCE [dbo].[LogSequence];

SET @CreateSequence = 'CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH ' + CAST(@CurrentMax AS VARCHAR) + '
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE';

PRINT @CreateSequence;

EXEC(@CreateSequence);

Sequence

[ CYCLE | NO CYCLE ]

Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequence objects is NO CYCLE.
Note that cycling restarts from the minimum or maximum value, not from the start value.

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE
 NO CYCLE
GO

Sequence

[ CACHE [<constant> ] | NO CACHE ]

Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. It defaults to CACHE.

For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 50
GO

Pageination

DECLARE @PageNumber INT = 1 , @PageSize INT = 10
;WITH LogTable AS
(
    SELECT
        l.Id, l.Date, l.Exception, l.HostName, l.Level, l.Logger, l.Message,
        l.NestedContext, l.Thread, l.Thread_Context_ID
	,ROW_NUMBER() OVER (ORDER BY l.Date) AS RowNumber
    FROM 
	dbo.[Log] AS l
	
)   
SELECT 
    nl.Id, nl.Date, nl.Exception, nl.HostName, nl.Level, nl.Logger, nl.Message,
    nl.NestedContext, nl.Thread, nl.Thread_Context_ID
FROM 
    LogTable AS nl
WHERE 
    RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY
    nl.Date DESC;

Old Method

Pageination

DECLARE @PageNumber INT = 1 , @PageSize INT = 10;
SELECT
    l.Id, l.Date, l.Exception, l.HostName, l.Level, l.Logger, l.Message,
    l.NestedContext, l.Thread, l.Thread_Context_ID
FROM
    dbo.[Log] AS l
ORDER BY
    l.Date DESC
    OFFSET @PageNumber ROWS
        FETCH NEXT @PageSize ROWS ONLY;

New Method

Pageination

Difference

Old

New

End Of Month

Function

DECLARE @date DATETIME = GETDATE();
SELECT
    EOMONTH(@date) AS Result;
GO

DECLARE @date DATETIME = GETDATE();
SELECT
    EOMONTH(@date) AS 'This Month',
    EOMONTH(@date, 1) AS 'Next Month',
    EOMONTH(@date, -1) AS 'Last Month';
GO

SQL Server 2016

Split String

Function

DECLARE	@emails VARCHAR(400) = 'test@test.com,testing@mywebsite.com,demo@test.com';   
SELECT
	value
FROM
	STRING_SPLIT(@emails, ',');  

SELECT
	value
FROM
	STRING_SPLIT('My random sentance for this demo.', ' ');

SELECT 
	IndustryID, 
	IndustryName
FROM 
	dbo.Industries  
	INNER JOIN STRING_SPLIT('1,3,5,7,9,10,14,27,74,86', ',')   
		ON value = IndustryID;

Datediff_Big

Function

DECLARE @StartDate AS DATETIME2(0) = '1/1/2016', @EndDate AS DATETIME2(0) = GETDATE();
--DECLARE @StartDate AS DATETIME2(0) = DATEADD(YEAR,-19,GETDATE()), @EndDate AS DATETIME2(0) = GETDATE();
SELECT 
	DATEDIFF_BIG(YEAR, @StartDate, @EndDate) AS [Year],
	DATEDIFF_BIG(quarter, @StartDate, @EndDate) AS [Quarter],
	DATEDIFF_BIG(month, @StartDate, @EndDate) AS [Month],
	DATEDIFF_BIG(dayofyear, @StartDate, @EndDate) AS [DayOfYear],
	DATEDIFF_BIG(day, @StartDate, @EndDate) [Day],
	DATEDIFF_BIG(week, @StartDate, @EndDate) AS [Week],
	DATEDIFF_BIG(hour, @StartDate, @EndDate) AS [Hour],
	DATEDIFF_BIG(minute, @StartDate, @EndDate) AS [Minute],
	DATEDIFF_BIG(second, @StartDate, @EndDate) AS [Second],
	DATEDIFF_BIG(millisecond, @StartDate, @EndDate) AS [Millisecond];

If EXISTS

Condition

DROP PROCEDURE IF EXISTS dbo.MySP;

DROP INDEX IF EXISTS IX_MyTestTable_Index ON dbo.MyTestTable;

DROP TABLE IF EXISTS dbo.MyTestTable;

DROP SYNONYM IF EXISTS dbo.MySynonym;

DROP TRIGGER IF EXISTS dbo.MyTrigger;

DROP USER IF EXISTS [user_name];

DROP VIEW IF EXISTS dbo.MyView;

DROP TYPE IF EXISTS dbo.MyType;

DROP SCHEMA IF EXISTS Demo;

DROP FUNCTION IF EXISTS dbo.MyFunction;

ALTER TABLE dbo.MyTestTable DROP COLUMN IF EXISTS MyTestColumn;

ALTER TABLE dbo.MyTestTable DROP CONSTRAINT IF EXISTS PK_MyTestTable;

JSON

Native Support

SELECT TOP 10 [AccountId],[EmailHash],[DisplayName],[Age],[Location],[CreationDate],
              [LastAccessDate],[Reputation],[DownVotes],[UpVotes],[Views] 
FROM dbo.Users ORDER BY LastAccessDate DESC
FOR JSON AUTO

[  {
    "AccountId":5638794,
    "DisplayName":"RiaDess",
    "CreationDate":"2015-01-17T13:34:58",
    "LastAccessDate":"2015-03-07T23:51:32.930",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":2348349,
    "DisplayName":"Ed Cottrell",
    "Age":36,
    "Location":"Houston, TX",
    "CreationDate":"2013-10-30T05:47:17",
    "LastAccessDate":"2015-03-07T16:02:03.720",
    "Reputation":103,
    "DownVotes":0,
    "UpVotes":0,
    "Views":1
  },
  {
    "AccountId":384624,
    "DisplayName":"THelper",
    "Location":"The Netherlands",
    "CreationDate":"2011-12-22T08:24:28",
    "LastAccessDate":"2015-03-07T15:19:56.533",
    "Reputation":2065,
    "DownVotes":0,
    "UpVotes":54,
    "Views":6
  },
  {
    "AccountId":45818,
    "DisplayName":"Adam",
    "CreationDate":"2011-07-16T13:39:07",
    "LastAccessDate":"2015-03-06T22:22:21.207",
    "Reputation":1264,
    "DownVotes":0,
    "UpVotes":2,
    "Views":1
  },
  {
    "AccountId":3074278,
    "DisplayName":"Biotechnologist",
    "CreationDate":"2014-01-21T23:49:45",
    "LastAccessDate":"2015-03-04T11:58:03.593",
    "Reputation":106,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":5080390,
    "DisplayName":"Sue",
    "Location":"Central Massachusetts",
    "CreationDate":"2015-02-04T18:58:56",
    "LastAccessDate":"2015-03-04T02:28:21.943",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":0
  },
  {
    "AccountId":42117,
    "DisplayName":"Pops",
    "Age":64,
    "Location":"East Coast, United States",
    "CreationDate":"2013-08-26T15:35:51",
    "LastAccessDate":"2015-03-03T21:23:23.593",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":3
  },
  {
    "AccountId":178712,
    "DisplayName":"mehta",
    "Location":"India",
    "CreationDate":"2013-02-04T08:14:42",
    "LastAccessDate":"2015-03-03T19:26:11.760",
    "Reputation":301,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":1275707,
    "DisplayName":"kircm",
    "Age":43,
    "Location":"New York",
    "CreationDate":"2012-11-18T22:39:24",
    "LastAccessDate":"2015-03-03T16:55:49.090",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":4521361,
    "DisplayName":"Enthusiastic Student",
    "Age":27,
    "CreationDate":"2014-05-25T21:16:30",
    "LastAccessDate":"2015-03-03T08:54:19.967",
    "Reputation":145,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  }]

JSON

Native Support

SELECT TOP 10 [AccountId] AS AccountNumber,[EmailHash],[DisplayName],[Age],
              [Location],[CreationDate], [LastAccessDate],[Reputation],[DownVotes],
              [UpVotes],[Views] 
FROM dbo.Users ORDER BY LastAccessDate DESC 
FOR JSON PATH

[  {
    "AccountNumber":5638794,
    "DisplayName":"RiaDess",
    "CreationDate":"2015-01-17T13:34:58",
    "LastAccessDate":"2015-03-07T23:51:32.930",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":2348349,
    "DisplayName":"Ed Cottrell",
    "Age":36,
    "Location":"Houston, TX",
    "CreationDate":"2013-10-30T05:47:17",
    "LastAccessDate":"2015-03-07T16:02:03.720",
    "Reputation":103,
    "DownVotes":0,
    "UpVotes":0,
    "Views":1
  },
  {
    "AccountNumber":384624,
    "DisplayName":"THelper",
    "Location":"The Netherlands",
    "CreationDate":"2011-12-22T08:24:28",
    "LastAccessDate":"2015-03-07T15:19:56.533",
    "Reputation":2065,
    "DownVotes":0,
    "UpVotes":54,
    "Views":6
  },
  {
    "AccountNumber":45818,
    "DisplayName":"Adam",
    "CreationDate":"2011-07-16T13:39:07",
    "LastAccessDate":"2015-03-06T22:22:21.207",
    "Reputation":1264,
    "DownVotes":0,
    "UpVotes":2,
    "Views":1
  },
  {
    "AccountNumber":3074278,
    "DisplayName":"Biotechnologist",
    "CreationDate":"2014-01-21T23:49:45",
    "LastAccessDate":"2015-03-04T11:58:03.593",
    "Reputation":106,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":5080390,
    "DisplayName":"Sue",
    "Location":"Central Massachusetts",
    "CreationDate":"2015-02-04T18:58:56",
    "LastAccessDate":"2015-03-04T02:28:21.943",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":0
  },
  {
    "AccountNumber":42117,
    "DisplayName":"Pops",
    "Age":64,
    "Location":"East Coast, United States",
    "CreationDate":"2013-08-26T15:35:51",
    "LastAccessDate":"2015-03-03T21:23:23.593",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":3
  },
  {
    "AccountNumber":178712,
    "DisplayName":"mehta",
    "Location":"India",
    "CreationDate":"2013-02-04T08:14:42",
    "LastAccessDate":"2015-03-03T19:26:11.760",
    "Reputation":301,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":1275707,
    "DisplayName":"kircm",
    "Age":43,
    "Location":"New York",
    "CreationDate":"2012-11-18T22:39:24",
    "LastAccessDate":"2015-03-03T16:55:49.090",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":4521361,
    "DisplayName":"Enthusiastic Student",
    "Age":27,
    "CreationDate":"2014-05-25T21:16:30",
    "LastAccessDate":"2015-03-03T08:54:19.967",
    "Reputation":145,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  }]

JSON

Native Support

SELECT
    Users.accountid,
    Posts.Id,
    Posts.PostTypeId,
    ISNULL(Posts.title, LEFT(CAST(Posts.body AS VARCHAR(MAX)), 50)) AS Title
FROM
    dbo.Users AS Users
    INNER JOIN dbo.Posts AS Posts
        ON Users.Id = Posts.OwnerUserId
WHERE
    Users.id IN (SELECT TOP 10
                    u.id
                 FROM
                    dbo.users AS u
                 ORDER BY
                    u.LastAccessDate DESC)
ORDER BY
    Users.id
FOR
    JSON AUTO

[
{"accountid":45818,"Posts":[{}]},
{"accountid":384624,
    "Posts":[
        {"Id":192,"PostTypeId":1,"Title":"Why is there an \"untagged\" tag?"},
        {"Id":222,"PostTypeId":1,"Title":"Typo in 'self-managment' tag"},
        {"Id":230,"PostTypeId":1,"Title":"Merge software tag and application tag?"},
        {"Id":250,"PostTypeId":1,"Title":"How do we get this site out of beta?"},
        {"Id":323,"PostTypeId":2,"Title":"<p>I've decided to start keeping track of the PP s"},
        {"Id":346,"PostTypeId":1,"Title":"Remove [productivity] tag once again?"}]},
{"accountid":1275707,"Posts":[{}]},
{"accountid":178712,"Posts":[{}]},
{"accountid":42117,"Posts":[{}]},
{"accountid":2348349,"Posts":[{}]},
{"accountid":3074278,"Posts":[{}]},
{"accountid":4521361,"Posts":[{}]},
{"accountid":5638794,"Posts":[{}]},
{"accountid":5080390,"Posts":[{}]}
]

JSON

Native Support

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}'
print @info

-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike')
print @info

-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith')
print @info

-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL)
print @info

-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure')
print @info
go

{"name":"John","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL"],"surname":"Smith"}
{"skills":["C#","SQL"],"surname":"Smith"}
{"skills":["C#","SQL","Azure"],"surname":"Smith"}

JSON

Native Support

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}'
print @info
-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure')
print @info
GO

DECLARE @product NVARCHAR(100) = '{"price":49.99}'
print @product
-- Rename property
SET @product = 	JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS numeric(4,2))), '$.price', NULL)
print @product
GO

DECLARE @stats NVARCHAR(100) = '{"click_count": 173}'
print @stats
-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count',CAST(JSON_VALUE(@stats, '$.click_count') as int) + 1)
print @stats

{"name":"John","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL","Azure"],"surname":"Smith"}
{"price":49.99}
{"Price":49.99}
{"click_count": 173}
{"click_count": 174}

Compress/Decompress

Function

DECLARE @jsontext VARCHAR(MAX) = '[{"name": "Åland Islands", "code": "AX"},{"name": "Albania", "code": "AL"},{"name": "Algeria", "code": "DZ"},{"name": "American Samoa", "code": "AS"},{"name": "AndorrA", "code": "AD"},{"name": "Angola", "code": "AO"},{"name": "Anguilla", "code": "AI"},{"name": "Antarctica", "code": "AQ"},{"name": "Antigua and Barbuda", "code": "AG"},{"name": "Argentina", "code": "AR"},{"name": "Armenia", "code": "AM"},{"name": "Aruba", "code": "AW"},{"name": "Australia", "code": "AU"},{"name": "Austria", "code": "AT"},{"name": "Azerbaijan", "code": "AZ"},{"name": "Bahamas", "code": "BS"},{"name": "Bahrain", "code": "BH"},{"name": "Bangladesh", "code": "BD"},{"name": "Barbados", "code": "BB"},{"name": "Belarus", "code": "BY"},{"name": "Belgium", "code": "BE"},{"name": "Belize", "code": "BZ"},{"name": "Benin", "code": "BJ"},{"name": "Bermuda", "code": "BM"},{"name": "Bhutan", "code": "BT"},{"name": "Bolivia", "code": "BO"},{"name": "Bosnia and Herzegovina", "code": "BA"},{"name": "Botswana", "code": "BW"},{"name": "Bouvet Island", "code": "BV"},{"name": "Brazil", "code": "BR"},{"name": "British Indian Ocean Territory", "code": "IO"},{"name": "Brunei Darussalam", "code": "BN"},{"name": "Bulgaria", "code": "BG"},{"name": "Burkina Faso", "code": "BF"},{"name": "Burundi", "code": "BI"},{"name": "Cambodia", "code": "KH"},{"name": "Cameroon", "code": "CM"},{"name": "Canada", "code": "CA"},{"name": "Cape Verde", "code": "CV"},{"name": "Cayman Islands", "code": "KY"},{"name": "Central African Republic", "code": "CF"},{"name": "Chad", "code": "TD"},{"name": "Chile", "code": "CL"},{"name": "China", "code": "CN"},{"name": "Christmas Island", "code": "CX"},{"name": "Cocos (Keeling) Islands", "code": "CC"},{"name": "Colombia", "code": "CO"},{"name": "Comoros", "code": "KM"},{"name": "Congo", "code": "CG"},{"name": "Congo, The Democratic Republic of the", "code": "CD"},{"name": "Cook Islands", "code": "CK"},{"name": "Costa Rica", "code": "CR"},{"name": "Cote D"Ivoire", "code": "CI"},{"name": "Croatia", "code": "HR"},{"name": "Cuba", "code": "CU"},{"name": "Cyprus", "code": "CY"},{"name": "Czech Republic", "code": "CZ"},{"name": "Denmark", "code": "DK"},{"name": "Djibouti", "code": "DJ"},{"name": "Dominica", "code": "DM"},{"name": "Dominican Republic", "code": "DO"},{"name": "Ecuador", "code": "EC"},{"name": "Egypt", "code": "EG"},{"name": "El Salvador", "code": "SV"},{"name": "Equatorial Guinea", "code": "GQ"},{"name": "Eritrea", "code": "ER"},{"name": "Estonia", "code": "EE"},{"name": "Ethiopia", "code": "ET"},{"name": "Falkland Islands (Malvinas)", "code": "FK"},{"name": "Faroe Islands", "code": "FO"},{"name": "Fiji", "code": "FJ"},{"name": "Finland", "code": "FI"},{"name": "France", "code": "FR"},{"name": "French Guiana", "code": "GF"},{"name": "French Polynesia", "code": "PF"},{"name": "French Southern Territories", "code": "TF"},{"name": "Gabon", "code": "GA"},{"name": "Gambia", "code": "GM"},{"name": "Georgia", "code": "GE"},{"name": "Germany", "code": "DE"},{"name": "Ghana", "code": "GH"},{"name": "Gibraltar", "code": "GI"},{"name": "Greece", "code": "GR"},{"name": "Greenland", "code": "GL"},{"name": "Grenada", "code": "GD"},{"name": "Guadeloupe", "code": "GP"},{"name": "Guam", "code": "GU"},{"name": "Guatemala", "code": "GT"},{"name": "Guernsey", "code": "GG"},{"name": "Guinea", "code": "GN"},{"name": "Guinea-Bissau", "code": "GW"},{"name": "Guyana", "code": "GY"},{"name": "Haiti", "code": "HT"},{"name": "Heard Island and Mcdonald Islands", "code": "HM"},{"name": "Holy See (Vatican City State)", "code": "VA"},{"name": "Honduras", "code": "HN"},{"name": "Hong Kong", "code": "HK"},{"name": "Hungary", "code": "HU"},{"name": "Iceland", "code": "IS"},{"name": "India", "code": "IN"},{"name": "Indonesia", "code": "ID"},{"name": "Iran, Islamic Republic Of", "code": "IR"},{"name": "Iraq", "code": "IQ"},{"name": "Ireland", "code": "IE"},{"name": "Isle of Man", "code": "IM"},{"name": "Israel", "code": "IL"},{"name": "Italy", "code": "IT"},{"name": "Jamaica", "code": "JM"},{"name": "Japan", "code": "JP"},{"name": "Jersey", "code": "JE"},{"name": "Jordan", "code": "JO"},{"name": "Kazakhstan", "code": "KZ"},{"name": "Kenya", "code": "KE"},{"name": "Kiribati", "code": "KI"},{"name": "Korea, Democratic People"S Republic of", "code": "KP"},{"name": "Korea, Republic of", "code": "KR"},{"name": "Kuwait", "code": "KW"},{"name": "Kyrgyzstan", "code": "KG"},{"name": "Lao People"S Democratic Republic", "code": "LA"},{"name": "Latvia", "code": "LV"},{"name": "Lebanon", "code": "LB"},{"name": "Lesotho", "code": "LS"},{"name": "Liberia", "code": "LR"},{"name": "Libyan Arab Jamahiriya", "code": "LY"},{"name": "Liechtenstein", "code": "LI"},{"name": "Lithuania", "code": "LT"},{"name": "Luxembourg", "code": "LU"},{"name": "Macao", "code": "MO"},{"name": "Macedonia, The Former Yugoslav Republic of", "code": "MK"},{"name": "Madagascar", "code": "MG"},{"name": "Malawi", "code": "MW"},{"name": "Malaysia", "code": "MY"},{"name": "Maldives", "code": "MV"},{"name": "Mali", "code": "ML"},{"name": "Malta", "code": "MT"},{"name": "Marshall Islands", "code": "MH"},{"name": "Martinique", "code": "MQ"},{"name": "Mauritania", "code": "MR"},{"name": "Mauritius", "code": "MU"},{"name": "Mayotte", "code": "YT"},{"name": "Mexico", "code": "MX"},{"name": "Micronesia, Federated States of", "code": "FM"},{"name": "Moldova, Republic of", "code": "MD"},{"name": "Monaco", "code": "MC"},{"name": "Mongolia", "code": "MN"},{"name": "Montserrat", "code": "MS"},{"name": "Morocco", "code": "MA"},{"name": "Mozambique", "code": "MZ"},{"name": "Myanmar", "code": "MM"},{"name": "Namibia", "code": "NA"},{"name": "Nauru", "code": "NR"},{"name": "Nepal", "code": "NP"},{"name": "Netherlands", "code": "NL"},{"name": "Netherlands Antilles", "code": "AN"},{"name": "New Caledonia", "code": "NC"},{"name": "New Zealand", "code": "NZ"},{"name": "Nicaragua", "code": "NI"},{"name": "Niger", "code": "NE"},{"name": "Nigeria", "code": "NG"},{"name": "Niue", "code": "NU"},{"name": "Norfolk Island", "code": "NF"},{"name": "Northern Mariana Islands", "code": "MP"},{"name": "Norway", "code": "NO"},{"name": "Oman", "code": "OM"},{"name": "Pakistan", "code": "PK"},{"name": "Palau", "code": "PW"},{"name": "Palestinian Territory, Occupied", "code": "PS"},{"name": "Panama", "code": "PA"},{"name": "Papua New Guinea", "code": "PG"},{"name": "Paraguay", "code": "PY"},{"name": "Peru", "code": "PE"},{"name": "Philippines", "code": "PH"},{"name": "Pitcairn", "code": "PN"},{"name": "Poland", "code": "PL"},{"name": "Portugal", "code": "PT"},{"name": "Puerto Rico", "code": "PR"},{"name": "Qatar", "code": "QA"},{"name": "Reunion", "code": "RE"},{"name": "Romania", "code": "RO"},{"name": "Russian Federation", "code": "RU"},{"name": "RWANDA", "code": "RW"},{"name": "Saint Helena", "code": "SH"},{"name": "Saint Kitts and Nevis", "code": "KN"},{"name": "Saint Lucia", "code": "LC"},{"name": "Saint Pierre and Miquelon", "code": "PM"},{"name": "Saint Vincent and the Grenadines", "code": "VC"},{"name": "Samoa", "code": "WS"},{"name": "San Marino", "code": "SM"},{"name": "Sao Tome and Principe", "code": "ST"},{"name": "Saudi Arabia", "code": "SA"},{"name": "Senegal", "code": "SN"},{"name": "Serbia and Montenegro", "code": "CS"},{"name": "Seychelles", "code": "SC"},{"name": "Sierra Leone", "code": "SL"},{"name": "Singapore", "code": "SG"},{"name": "Slovakia", "code": "SK"},{"name": "Slovenia", "code": "SI"},{"name": "Solomon Islands", "code": "SB"},{"name": "Somalia", "code": "SO"},{"name": "South Africa", "code": "ZA"},{"name": "South Georgia and the South Sandwich Islands", "code": "GS"},{"name": "Spain", "code": "ES"},{"name": "Sri Lanka", "code": "LK"},{"name": "Sudan", "code": "SD"},{"name": "Suriname", "code": "SR"},{"name": "Svalbard and Jan Mayen", "code": "SJ"},{"name": "Swaziland", "code": "SZ"},{"name": "Sweden", "code": "SE"},{"name": "Switzerland", "code": "CH"},{"name": "Syrian Arab Republic", "code": "SY"},{"name": "Taiwan, Province of China", "code": "TW"},{"name": "Tajikistan", "code": "TJ"},{"name": "Tanzania, United Republic of", "code": "TZ"},{"name": "Thailand", "code": "TH"},{"name": "Timor-Leste", "code": "TL"},{"name": "Togo", "code": "TG"},{"name": "Tokelau", "code": "TK"},{"name": "Tonga", "code": "TO"},{"name": "Trinidad and Tobago", "code": "TT"},{"name": "Tunisia", "code": "TN"},{"name": "Turkey", "code": "TR"},{"name": "Turkmenistan", "code": "TM"},{"name": "Turks and Caicos Islands", "code": "TC"},{"name": "Tuvalu", "code": "TV"},{"name": "Uganda", "code": "UG"},{"name": "Ukraine", "code": "UA"},{"name": "United Arab Emirates", "code": "AE"},{"name": "United Kingdom", "code": "GB"},{"name": "United States", "code": "US"},{"name": "United States Minor Outlying Islands", "code": "UM"},{"name": "Uruguay", "code": "UY"},{"name": "Uzbekistan", "code": "UZ"},{"name": "Vanuatu", "code": "VU"},{"name": "Venezuela", "code": "VE"},{"name": "Viet Nam", "code": "VN"},{"name": "Virgin Islands, British", "code": "VG"},{"name": "Virgin Islands, U.S.", "code": "VI"},{"name": "Wallis and Futuna", "code": "WF"},{"name": "Western Sahara", "code": "EH"},{"name": "Yemen", "code": "YE"},{"name": "Zambia", "code": "ZM"},{"name": "Zimbabwe", "code": "ZW"}]'

DECLARE @TestTable TABLE (  
    Id INT PRIMARY KEY IDENTITY,  
    CompressedData VARBINARY(max),
    UnCompressedData VARCHAR(MAX),
    JSONData AS CAST(decompress(CompressedData) AS VARCHAR(MAX))  
);

INSERT INTO @TestTable (CompressedData, UnCompressedData)
VALUES (COMPRESS(@jsontext), @jsontext)

SELECT UnCompressedData, DATALENGTH(CompressedData) CompressedSize
FROM @TestTable

SELECT CAST(DECOMPRESS(CompressedData) AS VARCHAR(MAX)) AS Decompressed, DATALENGTH(UnCompressedData) UncompressedSize
FROM @TestTable

Live Execution Plan

Performance Results

Before 2016, you could only Query Plan after a query executed.

Now, watch in real-time using LQS.

Includes number of rows, elapsed time, operator progress and live warnings.

Live Execution Plan

Performance Results

Always Encrypted

Security

Always Encrypted

Security

Always Encrypted

Security

Still use TDE
It works inflight and at rest.
It is supported in .Net Framework 4.6.
Other frameworks/driver support coming soon.
Some redundancy exist when using TDE and Always Encrypted simultaneously.
Create Master Key on server.
Create Column Key and place in the certificate store on user machine or web/app server.
Big Issue with .Net Framework 4.6.2!
(https://blogs.msdn.microsoft.com/sqlreleaseservices/net-4-6-2-framework-client-driver-for-always-encrypted-resulting-in-intermittent-failures-to-decrypt-individual-rows/)

Always Encrypted

Security

There will be two kinds of encryption available in SQL Server 2016

Randomized: if you encrypt the same value (“12345″) twice, you’ll get two different encrypted values. Great for super-duper-security, but useless if you need to join between tables or do GROUP BYs.
Deterministic encryption: if you encrypt the same value multiple times, you’ll always get the same encrypted values. Less secure, but lets you do equality operations, WHERE clauses, SELECT DISTINCT, GROUP BY, etc.

Always Encrypted

Security

CREATE COLUMN MASTER KEY DEFINITION [CMKD]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'CurrentUser/My/32714BF3CD4EBD97E1E952E8DE2BCC0231521E8C'
)
END
GO

Always Encrypted

Security

CREATE COLUMN ENCRYPTION KEY [CEK]
WITH VALUES
(
	COLUMN MASTER KEY DEFINITION = [CMKD],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0033003200370031003400620066003300630064003400650062006400390037006500310065003900350032006500380064006500320062006300630030003200330031003500320031006500380063007DF88AE1521092180EBD34C56614BD0B8A9F0EDB13C008715003AF315D6A43DEC5DF85B98585CEB4FC0E8B5B81465C1225A70A15016204650EC65B66A926365465D3929673560ABD13D7F1E8F5580DE8176F47D265C605EEF5F1CA85F8223FED54C66E4C4C487899A063BDDA3766764774E9D1A0643BADBC43BF01EE72227A436B7285D11A5F98F0EFD49F8EB9EC7A6A8699CC9AD0A5102878D711F1223BB91F5F284B095F895560C54FC2670A58A4F82AEC75382FF875EDD959FB0EE3D7CE3BB670531CDB0275E2D39C5FC2FF576D345DDBE61D70710BDDC2750AF960CDCE09E8B50DF89FCF2BE77A489B20DB0EF583CADCBDBD7D113FE14B9B73C4BB529B3F55A3FF4AA72C3B808E5F49DC7A98BBFDD2E0DE1F62E1AA65A708DF95F3DEF5668FE9AAB9ED85211B26ACC60810649C6007C73360C5110F961BA2D3039E173B7CB425D81D25FB211A961C262948C039F527B1AD609675DCC7723D05E40BF3A63EEF70E172577328D618624223530179C1224118F07B5AF971AC80EA5E989598D8F8F82B4AC9C48E54E280840A8796C3D109F7BB3F3576C8E4B256B7073DD321B6AAA7B88A7D2189B01E26DCA54F252902FEB0A98A717D00275EECBB9001C25E2EB8547A102D83C874CB682B98EEBA4A1E259D16AA5337E26E159A9C06395A68965EBC621BD8CF4D27F76A2FAF3D2E073DE33FD0C1B662E784678A477F31B7FE4C
)
GO

Always Encrypted

Security

CREATE TABLE [dbo].[Patients](
	[PatientId] [int] IDENTITY(1,1) NOT NULL,
	[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
                ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK], 
                                ENCRYPTION_TYPE = Deterministic, 
                                ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[StreetAddress] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[ZipCode] [int] NULL,
	[State] [nvarchar](50) NULL,
	[BirthDate] [datetime2](7)
                ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK], 
                                ENCRYPTION_TYPE = Randomized, 
                                ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED 
(
	[PatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Always Encrypted

Security

"Data Source=server63; Integrated Security=true; Column Encryption Setting=enabled";

cmd.CommandText = @"INSERT INTO [dbo].[Patients] 
([SSN], [FirstName], [LastName], [BirthDate]) 
VALUES (@SSN, @FirstName, @LastName, @BirthDate);";
 
 SqlParameter paramSSN = cmd.CreateParameter();
 paramSSN.ParameterName = @"@SSN";
 paramSSN.DbType = DbType.String;
 paramSSN.Direction = ParameterDirection.Input;
 paramSSN.Value = ssn;
 paramSSN.Size = 11;
 cmd.Parameters.Add(paramSSN);
 …
 SqlParameter paramBirthdate = cmd.CreateParameter();
 paramBirthdate.ParameterName = @"@BirthDate";
 paramBirthdate.DbType = DbType.DateTime2;
 paramBirthdate.Direction = ParameterDirection.Input;
 paramBirthdate.Value = birthdate;
 cmd.Parameters.Add(paramBirthdate);
 
 cmd.ExecuteNonQuery();

Always Encrypted

Security

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
|             SSN |        FirstName |         LastName |                 BirthDate |
-------------------------------------------------------------------------------------
|     123-45-6789 |             John |              Doe |      Friday, May 21, 1971 |
-------------------------------------------------------------------------------------
|     111-22-3333 |           Joanne |              Doe | Sunday, December 01, 1974 |
-------------------------------------------------------------------------------------
|     562-00-6354 |          Michael |             Park | Sunday, November 18, 1928 |
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

Always Encrypted

Security

Dynamic Data Masking

Security

CREATE TABLE [dbo].[Employees]
([EmployeeId] [int] NOT NULL IDENTITY(1,1),
[FirstName] [varchar] (25) NOT NULL,
[LastName] [varchar] (25) NOT NULL,
[CreditCard] [varchar](16) NULL,
[Salary] [int] NULL,
[EmailAddress] [varchar] (50) NULL,
[PhoneNumber] [varchar](12) NULL)
GO

ALTER Table [dbo].[Employees] ALTER COLUMN [Salary] 
ADD MASKED WITH (FUNCTION='default()')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [CreditCard] 
ADD MASKED WITH (FUNCTION='partial(2,"XXXX",2)')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [EmailAddress] 
ADD MASKED WITH (FUNCTION='email()')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [PhoneNumber] 
ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')
GO

Dynamic Data Masking

Security

INSERT INTO [dbo].[Employees] 
([FirstName],[LastName],[CreditCard],[Salary],[EmailAddress],[PhoneNumber])
VALUES 
('Jeff','Taylor','1234123412341234','12000','jtaylor@mydomain.com','123-123-1234')
GO

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'testuser', DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Demo16]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestUser]
GO

EXECUTE AS USER='TestUser';
SELECT * FROM dbo.Employees;
REVERT;

Dynamic Data Masking

Security

GRANT UNMASK TO TestUser;
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.Employees;
REVERT;

-- Removing the UNMASK permission
REVOKE UNMASK TO TestUser;

EXECUTE AS USER='TestUser';
SELECT * FROM dbo.Employees;
REVERT;

Row Level Security

Security

Row Level Security

Security

Alter Security Policy to use different function based on time of day or other criteria without disabling Security Policy.
Handled on 'Creation' of record, not during execution of query.
Works for a simple ID or username, or as much complicated criteria you want.
Currently it's 'FILTER' based - You can create any custom filter combination or schedule

Query Data Store

Performance

Captures Queries, Query Plans, Runtime Statistics. - Think of it as a flight recorder or black box for your database.
Policies - Option to 'Force Plans'
Access full history of query execution
Quickly pinpoint the most expensive queries.
Get all queries whose performance regressed over time.
Identify issues with upgrades

Query Data Store

Performance

Temporal Database

Change Tracking

Data is rarely static.
Query data as of a particular point in time.
Each temporal table consists of two tables actually - on for current data, one for historical data. - Complexity hidden from user.
SQL Server maintains validity for periods for current and old records.
Provide regulatory compliance and performance auditing.
Reverting table to a 'last good known state' without downtime.
Perform time-based data analysis.

Temporal Database

Change Tracking

IF OBJECT_ID('dbo.MyTemporalTable', 'U') IS NOT NULL
BEGIN
       IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = 
            OBJECT_ID('dbo.MyTemporalTable', 'U')) = 2)
       BEGIN
            ALTER TABLE [dbo].[MyTemporalTable] SET (SYSTEM_VERSIONING = OFF)
       END
       DROP TABLE [dbo].[MyTemporalTable]
       --Had to add this in order to reset, otherwise it brings history table in as 
       --actual table.
       DROP TABLE dbo.MyTemporalTable_history
END
GO

CREATE TABLE [dbo].[MyTemporalTable]
(
       MyTemporalTableId int NOT NULL IDENTITY(1,1),
       UserName varchar(25) NOT NULL,
       UserGroupId int NOT NULL,
       CreatedDatetime datetime2(7) not null CONSTRAINT DF_MyTemporalTable_CreatedDatetime default (getdate()),
       ModifiedDatetime datetime2(7) null,
       SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
       SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
       PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime),
       CONSTRAINT PK_MyTemporalTable PRIMARY KEY (MyTemporalTableId)
)WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [History].[MyTemporalTable]))
GO

Temporal Database

Change Tracking

--Showing First Insert
SELECT * from dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:32:54.0004984'

--Showing First and Second Insert
SELECT * from dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:33:00.0004984'

--Showing after all three inserts
SELECT * from dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:00.0004984'

Temporal Database

Change Tracking

--As Of for Group 1 - Showing Before/After updates/deletes
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:23.3944162' 
WHERE [UserGroupId] = 1
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:25.3944162' 
WHERE [UserGroupId] = 1

--As Of for Group 2 - Showing Before/After updates/deletes
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:23.3944162' 
WHERE [UserGroupId] = 2
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:25.3944162' 
WHERE [UserGroupId] = 2

Temporal Database

Change Tracking

--Current State
SELECT * FROM dbo.MyTemporalTable

--Showing Between Start and End
DECLARE @Start datetime2 = '2015-06-19 20:32:54.0004984', 
        @End datetime2 = '2015-06-19 20:32:55.0004984'
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME BETWEEN @Start and @End 
WHERE [UserGroupId] = 1
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME BETWEEN @Start and @End 
WHERE [UserGroupId] = 2

In-Memory OLTP

Native Compilation

LEFT and RIGHT OUTER JOIN
SELECT DISTINCT
OR and NOT operators
Subqueries in all clauses of a SELECT statement
Nested stored procedure calls
UNION and UNION ALL
All built-in math functions

Natively compiled procedures will support a wider range of features, including the following constructs:

SQL Server 2017

OS

Unix

Platform	Supported version(s)
SUSE Linux Enterprise Server	v12 SP2
Ubuntu	16.04
Docker Engine	1.8+

ConCAt_WS

Function

CREATE TABLE dbo.Addresses
(
    AddressId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    UserId INT NOT NULL,
    AddressLine1 VARCHAR(100) NULL,
    AddressLine2 VARCHAR(100) NULL,
    AddressLine3 VARCHAR(100) NULL,
    City VARCHAR(50) NULL,
    [State] VARCHAR(2) NULL,
    ZipCode VARCHAR(10) NULL
) ON FGData;

INSERT INTO dbo.Addresses
	(UserId,AddressLine1,AddressLine2,AddressLine3,City,State,ZipCode)
VALUES
	(1, '123 Main Street', NULL, NULL, 'Jacksonville',  'FL', '32225' ),
	(2, '5th Ave South', 'Suite 500', NULL, 'New York', 'NY','01032'),
	(3, 'Attn: Returns Department', '1 Google Way','Building 25',  'San Fancisco', 'CA','66435');

SELECT
    CONCAT_WS(', ', AddressLine1, AddressLine2, AddressLine3, City, [State], ZipCode) AS MailingAddress
FROM
    dbo.Addresses;

Trim

Function

SELECT TRIM( '     test    ');

SELECT TRIM( '.,! ' FROM  '#     test    .');

SELECT trim('-' FROM CAST(NEWID() AS VARCHAR(36)));

SELECT trim('-, ' FROM '-5E905525-CE5B-');

SELECT trim('.' FROM '.5E905525.CE5B.');

SELECT REPLACE(CAST(NEWID() AS VARCHAR(36)),'-','');

SELECT TRIM( '#.' FROM  '#     test    .');

Translate

Function

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')');

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

TRANSLATE ( inputString, characters, translations)
Equivalent to multiple replace functions.
TRANSLATE is always SC collation aware.

Translate

Function

SELECT 
    TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point, 
    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

GeoJSON points to WKT format and vice versa

Within Group

String_AGG Function

SELECT 
	City, 
    STRING_AGG (EmailAddress, ';') WITHIN GROUP (ORDER BY EmailAddress ASC)
FROM 
	dbo.Users
GROUP BY 
	City;

SQL Server 2019

OS

Unix

Platform	Supported version(s)
Red Hat Enterprise Linux	7.3, 7.4, 7.5, 7.6

Developer Features

Use derived table or view aliases in graph match query
Match support in MERGE DML
Edge Constraints - Now supports the ability to define cascaded delete actions on an Edge Constraint

SQL Graph Enhancements

Developer Features

UTF-8 Support
Machine Learning Services
Java extension - Resource Governor tied in
New Polybase external connectors - SQL Server, Oracle, Teradata and MongoDB
String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

SQL Server On Linux

MSDTC
Machine Learning on Linux
Open LDAP Provider Support
Microsoft Container Registry

Tuning

Columnstore stats in DBCC CLONEDATABASE
Estimate compression for Columnstore indexes

EXEC sp_estimate_data_compression_savings 'dbo','Votes',NULL,NULL,'COLUMNSTORE'
EXEC sp_estimate_data_compression_savings 'dbo','Votes',NULL,NULL,'COLUMNSTORE_ARCHIVE'

Tuning

Troubleshoot page resource waits - DBCC Page not needed - Entire dbcc page header now available

use tempdb
go
SELECT object_name(page_info.object_id), page_info.* 
FROM sys.dm_exec_requests AS d 
  CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
  CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id,'DETAILED')
    AS page_info
GO

Performance and Scale

Approximate COUNT DISTINCT

Access data sets of millions of rows
Aggregates column(s) that have large number of distinct values
Responsiveness is more critical than absolute precision.
Results are typically within 2% of actual answer.
Returns the approx in fraction of time.

SQL Server 2022

String_Split

Ordinal

SELECT
    a.Authors,
    TRIM(a1.value) AS Author,
    TRIM(a2.value) AS Author
FROM
    stg.Authors AS a
    CROSS APPLY STRING_SPLIT(a.Authors, ',', 1) AS a1
    CROSS APPLY STRING_SPLIT(a.Authors, ',', 2) AS a2
WHERE
    a1.ordinal = 1
    AND a2.ordinal = 2
GROUP BY
    a.Authors,
    a1.value,
    a2.value;

Generate_Series

Start/Stop

SELECT
    value
FROM 
    GENERATE_SERIES(1, 10);

Generate_Series

Start/Stop/Step

SELECT 
    value
FROM 
    GENERATE_SERIES(1, 50, 5);

Generate_Series

Start/Stop/Step/Decimal

DECLARE @start decimal(2, 1) = 0.0;
DECLARE @stop decimal(2, 1) = 1.0;
DECLARE @step decimal(2, 1) = 0.1;

SELECT 
    value
FROM 
    GENERATE_SERIES(@start, @stop, @step);

Logical Function

Least/Greatest SELECT

SELECT
    LEAST('5.44', 7.434, N'9') AS LeastVal;
GO
SELECT
    GREATEST('5.44', 7.434, N'9') AS GreatestVal;
GO

Logical Function

Greatest WHERE

SELECT
    a.PublishedDate,
    a.JournalDate
FROM
    dbo.Articles AS a
WHERE
    GREATEST(a.PublishedDate, a.JournalDate) >= '2019-07-01'
    AND a.JournalDate IS NOT NULL;

ISJSON

Type Constraint

SELECT 
    id,
    JSONValue
FROM 
    dbo.Table
WHERE 
    ISJSON(JSONValue, SCALAR) = 1

Value	Description
VALUE	Tests for a valid JSON value. This can be a JSON object, array, number, string or one of the three literal values (false, true, null)
ARRAY	Tests for a valid JSON array
OBJECT	Tests for a valid JSON object
SCALAR	Tests for a valid JSON scalar – number or string

JSON_Path_Exists

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{"info":{"address":[{"city":"Jacksonville"},{"City":"Miami"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.address'); -- 1

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{"info":{"address":[{"City":"Jacksonville"},{"City":"Orlando"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.addresses'); -- 0

JSON_ARRAY

SELECT
    s.session_id,
    JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name)
FROM
    sys.dm_exec_sessions AS s
WHERE
    s.is_user_process = 1;

session_id	info
52	["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"]
55	["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"]
56	["WIN19-VM","SQLServerCEIP",".Net SqlClient Data Provider"]

JSON_OBJECT

SELECT
    s.session_id, 
    JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) as info
FROM 
    sys.dm_exec_sessions AS s
WHERE 
    s.is_user_process = 1;

session_id	info
51	{"security_id":"AQYAAAAAAAVQAAAAY/0dmFnai5oioQHh9eNArBIkYd4=","login":"NT SERVICE\\SQLTELEMETRY$SQL22","status":"sleeping"}
52	{"security_id":"AQUAAAAAAAUVAAAAoGXPfnhLm1/nfIdwAMgbAA==","login":WORKGROUP\\sqluser","status":"running"}

ISJSON, ARRAY?

DECLARE @JSONValue VARCHAR(MAX), @IsArray BIT;

SELECT @IsArray = ISJSON(d.Value, ARRAY) FROM OPENJSON(@JSONValue) AS d WHERE d.[Key] = 'users';

IF (@IsArray = 1)
	BEGIN
		UPDATE
			u
		SET
			u.UserName = JSON_VALUE(d.Value, '$.UserName'),
			u.UpdatedDateTime = SYSDATETIMEOFFSET()
		FROM
			OPENJSON(@JSONValue, '$.users') AS d
			INNER JOIN Dim.Users AS u
				ON a.UserId = CAST(JSON_VALUE(u.Value, '$.UserId') AS INT)
		WHERE
			ISJSON(@JSONValue) = 1;
	END;
ELSE
	BEGIN
		UPDATE
			u
		SET
			u.UserName = JSON_VALUE(d.Value, '$.UserName'),
			u.UpdatedDateTime = SYSDATETIMEOFFSET()
		FROM
			OPENJSON(@JSONValue) AS d
			INNER JOIN Dim.Users AS u
				ON a.UserId = CAST(JSON_VALUE(u.Value, '$.UserId') AS INT)
		WHERE
			ISJSON(@JSONValue) = 1
			AND d.[Key] = 'users';
	END;

Real World

Window

SELECT
    SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER win AS Total
    ,AVG(OrderQty) OVER win AS [Avg]
    ,COUNT(OrderQty) OVER win AS [Count]
    ,MIN(OrderQty) OVER win AS [Min]
    ,MAX(OrderQty) OVER win AS [Max]
FROM 
    Sales.SalesOrderDetail
WHERE 
    SalesOrderID IN(43659, 43664)
    WINDOW win AS (PARTITION BY SalesOrderID);
GO

SELECT 
    SalesOrderID,
    ProductID,
    OrderQty
    ,SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Avg]
    ,COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Count]
    ,MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Min]
    ,MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Max]
    FROM Sales.SalesOrderDetail
WHERE 
    SalesOrderID IN(43659, 43664);
GO

Partition vs. Window

Window

;WITH totalOrders AS
(
    SELECT
        st.StoreId,
        st.ReportDate,
        ISNULL(st.SalesAmount, 0.00) AS DailyTotal,
        SUM(ISNULL(st.SalesAmount, 0.00)) OVER wmtd AS TotalMTD,
        SUM(ISNULL(st.SalesAmount, 0.00)) OVER wqtd AS TotalQTD,
        SUM(ISNULL(st.SalesAmount, 0.00)) OVER wytd AS TotalYTD,
		SUM(ISNULL(st.SalesAmount, 0.00)) OVER r365td AS Rolling365
    FROM
        dbst.Stores AS st
        INNER JOIN Dim.Date AS d
            ON d.Date = st.ReportDate
	WINDOW 
		wmtd AS(PARTITION BY st.StoreId, d.YEAR, d.MONTH, d.Quarter ORDER BY st.StoreId, d.Date), 
		wqtd AS(PARTITION BY st.StoreId, d.YEAR, d.QUARTER ORDER BY st.StoreId, d.Date), 
		wytd AS(PARTITION BY st.StoreId, d.YEAR ORDER BY st.StoreId, d.Date), 
		r365td AS(PARTITION BY st.StoreId ORDER BY st.StoreId, d.Date ROWS BETWEEN 364 PRECEDING AND CURRENT ROW)
				 
)
UPDATE
    s
SET
    s.TotalMTD = totalOrders.TotalMTD,
    s.TotalQTD = totalOrders.TotalQTD,
    s.TotalYTD = totalOrders.TotalYTD,
    s.Rolling365 = totalOrders.Rolling365
FROM
	totalOrders
    INNER JOIN Fact.Sales AS s
        ON totalOrders.StoreId = s.StoreId

Real World