This article is more than 1 year old

SELECT features FROM bumf... What's new in MS SQL Server 2016

Tasty new stuff from Microsoft – but you need Enterprise Edition for full use

Microsoft has released SQL Server 2016, adding new security features, improved query profiling, Hadoop integration, hybrid cloud capabilities, and R analytics to its database server, along with numerous other improvements.

Calling SQL Server a "database server" does not do justice to its scope. The main components are:

Database engine. Runs on Windows with a Linux version promised. Supports Transact-SQL, Microsoft's flavor of SQL supplemented with extensions specific to SQL Server. Includes high-availability features such as replication and failover.

Reporting Services. Lets you design and deploy reports via a web portal.

Analysis Services. Advanced data mining and reporting using multi-dimensional data models. Integrates with the Excel spreadsheet via the PowerPivot add-in.

R Services. New in SQL Server 2016, integrates the R programming language, used for statistical analysis, into SQL Server. Based on Microsoft's acquisition of Revolution Analytics.

Data Quality Services. Analysis tools to improve the quality of data by finding incorrect, incomplete, or duplicated data by reference to a knowledge base, which may be either internal or external.

PolyBase. New in SQL Server 2016. Integrates SQL Server with external data either in Hadoop or in Azure Blob storage.

Azure SQL. Microsoft's SQL Server-as-a-service, low maintenance with the convenience of auto-scaling, but supporting only a subset of the full SQL Server. Microsoft also recommends running SQL Server in an Azure Virtual Machine (VM) for full features, though at a higher cost.

SQL Data Warehouse. A new Azure service that supports databases of up to 60TB, versions 250GB to 1TB for Azure SQL, but optimized for data warehousing with massively parallel processing for queries but a more limited subset of T-SQL available.

It all adds up to a big product, or family of products. Everything is built in, Microsoft claims, unlike other vendors which charge extra for different modules. This is only true, though, if you purchase the Enterprise Edition, which does not come cheap.

SQL Server 2016 follows the 2014 edition, for which the big new feature was in-memory OLTP (online transaction processing), codenamed Hekaton, in effect an alternate database engine optimized for in-memory data and further enhanced by stored procedures compiled to native code, which when combined have potential for huge performance gains.

There is nothing quite so eye-catching in SQL Server 2016, though in-memory OLTP is actually more usable than before, with a much wider subset of T-SQL supported, including FOREIGN KEY, UNIQUE and CHECK constraints, and in native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.

Security features

Microsoft has added significant security features in SQL Server 2016. "Always Encrypted" lets you encrypt data all the way from server to client, since it depends on client-side keys. These are distributed to clients. A new Always Encrypted-enabled driver is required, but the encryption is transparent to applications.

Always Encrypted uses client-side decryption

Always Encrypted uses client-side decryption

The downside is that new drivers are required. Microsoft has updated its .NET, ODBC and JDBC drivers but there are some caveats; drivers vary in whether or not they support custom key store providers, for example, and not all data types have been tested with the current ODBC driver. Thorough testing is suggested before going into production with this.

Also new is row-level security. The idea is that data access can be restricted at the database engine level so that users only see what is relevant to them. For example, a sales person might only be allowed to see their own sales records, despite other records existing in the same tables. This is implemented using inline table functions that reference the user login, combined with a security policy. Care is needed, otherwise it is easy to create policies that allow users to create records they cannot read.

Dynamic Data Masking is another new feature. A typical use is with credit card numbers. The whole number must be stored, but many users only need to see the last few digits. You can specify this in the table definition and then only users with the UNMASK permission can see the complete number.

Next page: Query Store

More about

TIP US OFF

Send us news


Other stories you might like