5 useful SQL Server 2016 features

1. IF EXISTS

When we create or delete or alter any objects in SQL server versions before 2016, we have do use syntax like this

IF OBJECT_ID(‘[dbo].[V_ABC’) IS NOT NULL

BEGIN

DROP VIEW [dbo].[V_ABC];

END;

GO

With SQL server 2016, the syntax is much easier. And this applies to table, view, function, store procedure, etc.

DROP VIEW IF EXISTS [dbo].[V_ABC];

2. Split

I found split function especially useful when you have a column with values with you need to split into different categories. The new function called STRING_SPLIT comes very handy especially work with Cross Apply

Let’s say you have a service requests table, which has a column called REPORTING_SEGMENT and the value storied in each row of the report segment column contains multiple values. The requirement is to get the request counts for each REPORTING_SEGMENT.

SELECT value AS reporting_seg,

COUNT([ID]) AS countofrequests

FROM [Ad_Hoc].[dbo].[Service_Requests]

CROSS APPLY string_split([REPORTING_SEGMENT], ‘,’)

GROUP BY value;

3. Temporal tables

I found temporal tables are super valuable for auditing, SCD, history tracking purposes. Essentially, the SQL server is doing the job in the back-end which usually you will need to configure by yourself using SSIS package or Store Procedures. Also the history table of those temporal table are automatically configured with column store index and is compressed which provide fast read and saved storage space.

4. In-memory tables

With memory price becoming inexpensive compare years ago, now with SQL server 2016 in-memory table capability, we can use in-memory table which much faster for read and write.

5. Column-store indexes

Very glad to see that Microsoft implemented what the technology used in tabular Analysis service to SQL server. Which make query which depend highly on analytics (like aggregation) much faster than if use row-store indexes.