Jeff Taylor

Principal Data Consultant

Fulton Analytics

What's New In SQL Server For The Developer

Jeff Taylor

Principal Data Consultant

Fulton Analytics

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

A Senior Data Engineer, Architect & Consultant. Friend Of Redgate. Over 25 years of experience specializing in performance tuning and critical data issues.

Currently serving as the President of the Jacksonville SQL Server Users Group and is an active board member of the Jacksonville Development Users Group.

jssug.org

Every 3rd Wednesday 6-8pm

Except for May, November and December

https://sqlsaturday.com

https://sqlsaturday.com/jax

Annual Free Data Conference

In May - (15th Anniversary in 2023)

Thank You Sponsors!

Jeff Taylor

Principal Data Consultant

Fulton Analytics

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

Contained Databases

Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
User authentication can be performed by the database. (This reduces the databases dependency on the logins of the SQL Server instance.)

Contained Databases

Contained database user with passwords - Contained database users with passwords are authenticated by the database.
Windows principals - Authorized Windows users and members of authorized Windows groups can connect directly to the database and do not need logins in the master database. The database trusts the authentication by Windows.

Contained user - There are two types of users for contained databases.

Contained Databases

Database Movement - No Orphaned SQL Users
Always On - Allows Users to directly connect to database
Initial Database Development - Developer must consider possible environmental impacts on the new database and program accordingly.
Database Administration - Maintaining database settings in the database, instead of in the master database, lets each database owner have more control over their database, without giving the database owner sysadmin permission.

Benefits

Contained Databases

Replication, change data capture, and change tracking.
Numbered procedures - (Overloading SPs has been deprecated)
Schema-bound objects that depend on built-in functions with collation changes.
Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

Limitations

Temporary stored procedures are currently permitted. Because temporary stored procedures breach containment, they are not expected to be supported in future versions of contained database.

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

Column Store Index

rowstore - A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data.
columnstore - A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.
columnstore index - A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. SQL Server supports both clustered and nonclustered columnstore indexes. Both use the same in-memory columnstore technology, but they do have differences in purpose and in features they support.

What is it?

Column Store Index

rowgroups and column segments - For high performance and high compression rates, the columnstore index slices the table into groups of rows, called row groups, and then compresses each row group in a column-wise manner. The number of rows in the row group must be large enough to improve compression rates, and small enough to benefit from in-memory operations.
row group - A rowgroup is a group of rows that are compressed into columnstore format at the same time.
column segment - A column segment is a column of data from within the rowgroup. A rowgroup usually contains the maximum number of rows per rowgroup which is 1,048,576 rows. Each rowgroup contains one column segment for every column in the table. Each column segment is compressed together and stored on physical media.

What is it?

Column Store Index

Non-Clustered - Not updatable
Clustered - Updatable (2014)
Cannot have more than 1024 columns.
A table with a nonclustered columnstore index can have unique constraints, primary key constraints, or foreign key constraints, but the constraints cannot be included in the nonclustered columnstore index.
Cannot be created on a view or indexed view.
Cannot include a sparse column.
Cannot be changed by using the ALTER INDEX statement. To change the nonclustered index, you must drop and re-create the columnstore index instead. You can use ALTER INDEX to disable and rebuild a columnstore index.
Cannot be created by using the INCLUDE keyword.
Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. (Sorting would eliminate many of the performance benefits.)

Restrictions/Limitations

Column Store Index

ntext
text
image
varchar(max) (SQL 2017)
nvarchar(max) (Can now use in SQL 2017)
rowversion
timestamp
sql_variant
CLR types (hierarchyid and spatial types)
xml
uniqueidentifier (2014)

Restrictions/Limitations

Columns that use any of the following data types cannot be included in a columnstore index:

Column Store Index

Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
Replication
Change tracking
Change data capture
Filestream

Restrictions/Limitations

Columnstore indexes cannot be combined with the following features:

Column Store Index

Example

CREATE CLUSTERED COLUMNSTORE INDEX [CCSI_Log] ON [dbo].[LogCS] 
WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [FGData]
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCS_Master] ON [dbo].[LogCS]
(
    [Id],
    [Date],
    [Thread],
    [Level],
    [Logger],
    [NestedContext],
    [Message],
    [Exception],
    [HostName],
    [Thread_Context_ID]
)WITH (DROP_EXISTING = OFF) ON [FGCS]
GO

ALTER TABLE [dbo].[LogCS] REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE)
GO

Column Store Index

Performance Results

SELECT COUNT(*) AS TotalTableCount FROM dbo.Log;

Table 'Log'. Scan count 5, logical reads 40778, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 2
Milliseconds To Execute: 1826

Clustered Column Store Index
Table 'LogCS'. Scan count 5, logical reads 135, physical reads 0, read-ahead reads 0, 
lob logical reads 718, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 46

Test 1 - Log and LogCS tables contain 30,479,827 records.

Execute Query using clustered columnstore index.

SELECT COUNT(*) AS TotalTableCount FROM dbo.LogCS;

Table has Primary Key Clustered Index and two Non-Clustered Indexes

Column Store Index

Performance Results

SELECT COUNT(*) AS LoggerCount FROM dbo.Log WHERE Logger = 'some value';

No Index
Table 'Log'. Scan count 5, logical reads 40850, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 2
Milliseconds To Execute: 2040

Non-Clustered Column Store Index
Table 'LogCS'. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 410, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 106

Test 2 - Log and LogCS tables contain 30,479,827 records.

Execute Query on column with no index.

Execute Query using clustered columnstore index.

SELECT COUNT(*) AS LoggerCount FROM dbo.LogCS WHERE Logger = 'some value';

Column Store Index

Performance Results

SELECT COUNT(*) AS LevelCount FROM dbo.Log WHERE Level = 'Error';

Non-Clustered Index
Table 'Log'. Scan count 5, logical reads 18306, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 1
Milliseconds To Execute: 510

Non-Clustered Column Store Index
Table 'LogCS'. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 390, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 56

Test 3 - Log and LogCS tables contain 30,479,827 records.

Execute Query on column with non-clustered index.

Execute Query using clustered columnstore index.

SELECT COUNT(*) AS LevelCount FROM dbo.LogCS WHERE Level = 'Error';

Column Store Index

Space

63.1% Space Savings

89.7% Space Savings

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

Stretch Database

Archive/Performance

Analytics

Real-Time

PolyBase/R

Data Warehousing

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

QueryStore

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

QUERYSTORE

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 = [dbo].[MyTemporalTable_history]))
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

Updates

256GB Limit changed to 2TB
Indexes can use any collation. (Performance still best using BIN2, however)
ALTER TABLE can be used on memory-optimized tables to add, drop or alter columns, or to add, drop or rebuild indexes. (Rebuilding indexes will require 2x memory)
Parallel Plans - Certain operations that use hash indexes can be performed in parallel, if they’re not used in a natively compiled procedure.
TDE now supported for In-Memory OLTP.
Multiple Log Readers for system for both recovery and checkpoint.

In-Memory OLTP

Updates

Improved scaling with a 4-socket machine.
AlwaysOn - Delay of data visibility of in-memory OLTP on the secondary replica limitation is now removed.
The garbage collection algorithms have been improved to provide greater scalability. With sufficient processing power, the garbage collection for your DML operations will happen almost instantaneously.
Unsupported Data Types - datetimeoffset, geography, geometry, hierarchyid, rowversion, xml, sql_variant, User-Defined Types.
(See supported data types https://msdn.microsoft.com/en-us/library/dn133179.aspx)

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.

Questions?

Resources

Email: Jeff@JeffTaylor.io

Twitter: @reviewmydb

Blog: https://j efftaylor.io/blog

Contact

SQL Server 2022

RC1

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, ',', 1) 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(START = 1, STOP = 10);

Generate_Series

Start/Stop/Step

SELECT 
    value
FROM 
    GENERATE_SERIES(START = 1, STOP = 50, STEP = 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 = @start, STOP = @stop, STEP = @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"}

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

Questions?

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Resources

Email: Jeff@JeffTaylor.io

Twitter: @reviewmydb

Blog: https://j efftaylor.io/blog