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.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: