SQL Server: Do More with Your Data

Author by Jes Borland

On November 16, Microsoft kicked off Connect(); 2016, a conference aimed at developers. I tuned into the keynote and heard many great announcements.

Scott Guthrie outlined Microsoft’s current mission for developers: Any developer, an application, on any platform. The first demo was building a Node.js application that connected to a MongoDB database and deployed to a server running Linux – all from a Mac. Microsoft is serious about their mission!

Visual Studio 2017’s release candidate, Visual Studio for Mac preview, and Visual Studio Mobile Center preview were announced. In Azure, Functions are now generally available, as is Data Lake Analytics.

There were also two major SQL Server announcements – SQL Server 2016 SP1 and SQL Server v.Next.

SQL Server 2016 SP1

SQL Server was released in June 2016 and has been widely adopted since. It’s the only relational database system that can currently be deployed on-premises, to their cloud, and to a competitor’s cloud service. SP1 was announced, which is much sooner than previous SPs have come out.

The most important change in SP1 is a consistent programming experience across all editions – features such as In-Memory OLTP, columnstore indexes, and table partitioning that had previously been available only in Enterprise edition are being made available in Standard, Web, Express, and Local DB! Why is this a big deal? Programmers can now decide to use a feature without worrying about the consequences of moving away from Enterprise edition. Features can be tested on smaller, less-critical workloads before being used in a mission-critical application. This also enables a more-consistent programming interface with Azure SQL Database, in which most features are available regardless of which service tier you choose.

Another benefit is that more security features are available in more editions. Data security should be taken seriously, and with the ability to use row-level security, dynamic data masking, fine-grained auditing, and even Always Encrypted in most editions, your data can be more secure than ever.

The breakdown of features can be found on the SQL Server Release Services blog.

There are other features added, also! T-SQL now supports CREATE OR ALTER with views, stored procedures, functions, and triggers, reducing programming time and effort. DROP TABLE is now supported in transactional replication. There are two new DMFs - sys.dm_exec_query_statistics_xml and sys.dm_db_incremental_stats_properties.

The full list of fixes and additions can be found in the SP1 release notes, which I encourage you to read!

SQL Server v.Next

Although the current version has been out for less than six months, Microsoft is hard at work on  v.Next.

The largest and most exciting change is that it can be installed on Linux. This opens up another market, and makes SQL Server more attractive to enterprises that run both Windows and Linux. With the consistent surface programming experience, all features should be available when using Linux, as well. During the keynote, there was a demo of creating a columnstore index on a Standard edition SQL Server running on Linux!

Another big change is the ability to scale out SSIS. A package will be distributed across multiple “worker” servers for execution.

Improvements are being made to existing feature, too. In-Memory tables are getting sp_spaceused and sp_rename, along with CASE and TOP (N) WITH TIES. Clustered columnstore indexes will support LOB data types. There is also a new function, STRING_AGG, which will concatenate a row’s values.

Don’t Wait to Upgrade

Now is a great time to plan your upgrade to SQL Server 2016 if you haven’t already; and to update to SP1 if you have. Microsoft is working to make SQL Server more inclusive, with exciting new features.

Want to know how to migrate to 2016? Contact me today!


Jes Borland

Senior SQL Server Engineer