Top Five New Features in SQL Server 2016 CTP 3

The production release of SQL Server 2016 is expected sometime early next year but in the meantime Microsoft continues to add new features and capabilities to the SQL Server 2016 Community Technology Preview 3 (CTP3). The SQL Server 2016 CTP3 is freely available for testing and you can download it from SQL Server Evaluations. In this column let’s check out the top five new features that Microsoft added to the SQL Server 2016 CTP 3.

1. Support for unique indexes have been added to In-Memory OLTP – The original release of the In-Memory OLTP with SQL Server 2014 only supported two types of indexes: hash and range. Indexes on a memory-optimized table can now be unique indexes. To go along with this Microsoft also added support for unique constraints in CTP 3.

2. Support for Large object (LOB) types has been added to In-Memory OLTP – One of the limitations with the initial implementation of In-Memory OLTP was that not all data types were supported. Notably, the LOB data types were not allowed. With SQL Server 2016 CTP 3 Microsoft added support for the varchar(max), nvarchar(max), and varbinary(max) to be used with memory-optimized tables and natively compiled modules.

3. New COMPRESS and DECOMPRESS T-SQL functions – Another closely related new capability are the COMPRESS and DECOMPRESS functions which can store and retrieve data using the GZIP compression algorithm. Like you would expect the COMPRESS function compressed data before it is stored in a column or variable. It returns binary data. The DECOMPRESS function decompress binary data and returns that decompressed version of that data. You need to cast binary data to text if you compressed text data. You can see an example of using the new COMPRESS and DECOMPRESS functions in the following example.

CREATE TABLE Persons (
FirstName nvarchar(max),
LastName nvarchar(max),
CompressedInfo varbinary(max))

INSERT INTO Persons (FirstName, LastName, Compressedinfo)
 SELECT FirstName, LastName, COMPRESS(AdditionalContactInfo) FROM Person.Person

SELECT FirstName, LastName, DECOMPRESS(Compressedinfo) AS AdditionalContactInfo
 FROM Persons

4. The new Stretch Database capability is now compatible with AlwaysOn – Stretch Databases were a feature that was introduced in an earlier SQL Server 2016 CTP. Stretch Databases will allow you to dynamically extent your on-premise database to Azure enabling your frequently accessed data to stay on-premise and your infrequently accessed data to be moved to the cloud. With SQL Server CTP 3 you can enable the new stretch capability on a database that already belongs to an AlwaysOn availability group. You can also add a database that's stretch enabled to an AlwaysOn availability group.

5. SQL Server 2016 CTP3 Reporting Services supports integration with SharePoint 2013 and SharePoint 2016 – SQL Server is the relational database engine behind SharePoint and Microsoft is getting ready to release the new SharePoint 2016 versions next year as well. SQL Server 2016 CTP 3 provides support for the latest SharePoint 2016 IT Preview. You can download the preview from SharePoint 2016 IT Preview.