SQL Server 2016 Community Technology Preview 3.1 is available
The SQL Server engineering team is pleased to announce the immediate availability of SQL Server 2016 November public preview release CTP 3.1.
To learn more about SQL Server 2016, visit the SQL Server 2016 preview page. To experience the new, exciting features in SQL Server 2016 and the new rapid release model, download the preview and start evaluating the impact these new innovations can have for your business.
Have questions? Join the discussion of the new SQL Server 2016 capabilities at MSDN and StackOverflow. If you run into an issue or would like to make a suggestion, you can let us know through Microsoft’s Connect tool. We look forward to hearing from you.
New In-Memory OLTP improvements in CTP3.1 include:
- Unique indexes in memory-optimized tables, to complement the support for unique constraints that was released in CTP3
- LOB data types varchar(max), nvarchar(max), and varbinary(max) in memory-optimized tables and natively compiled modules
- Indexes with NULLable key columns in memory-optimized tables
Example: Illustrate the new features using a memory-optimized table natively compiled stored procedure.
CREATE TABLE Sales.Store_inmem(
[BusinessEntityID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
[Name] nvarchar(200) NOT NULL,
[SalesPersonID] int NULL,
[Demographics] nvarchar(max) NULL, — LOB column
[ModifiedDate] datetime2 NOT NULL DEFAULT (sysdatetime()),
INDEX ix_SalesPersonID NONCLUSTERED (SalesPersonID), — index on Nullable column
INDEX ix_Name UNIQUE NONCLUSTERED (Name) — UNIQUE index
) WITH (MEMORY_OPTIMIZED=ON)
GO
CREATE PROCEDURE Sales.usp_InsertStore
@Name nvarchar(200) NOT NULL,
@SalesPersonID int = NULL,
@Demographics nvarchar(max) = NULL
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N’Dutch’)
INSERT Sales.Store_inmem (Name, SalesPersonID, Demographics)
VALUES (@Name, @SalesPersonID, @Demographics)
END
GO
DECLARE @Demographics nvarchar(max) = REPLICATE(N’LOBtest’, 10000)
EXECUTE Sales.usp_InsertStore @Name=‘test’, @Demographics=@Demographics
SELECT * FROM Sales.Store_inmem
GO
Programmability improvement – AT TIME ZONE clause
Support for AT TIME ZONE clause that allows customers to convert datetime values across different time zones, honoring time zone conversion rules. It can be used to appropriately present datetime values in applications that target international markets.
Syntax inputdate AT TIME ZONE timezone.
- Inputdate: An expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.
- Timezone: Name of the destination time zone in standard format as enumerated by Windows. Available time zones can be found by querying sys.time_zone_info.
Examples
SELECT SalesOrderID, OrderDate, TODATETIMEOFFSET (OrderDate, ‘-07:00’) as OrderDatePST,
TODATETIMEOFFSET (OrderDate, ‘-07:00’) AT TIME ZONE ‘Central European Standard Time’ AS OrderCETTime
FROM Sales.[SalesOrderHeader]
If inputdate is without provided offset information, function applies offset of the time zone assuming that value was originally provided in that time zone:
SELECT convert(datetime2, ‘2015-06-27 11:20:08.5783475’)
AT TIME ZONE ‘Central European Standard Time’ as CETTimeWithOffset
SELECT convert(datetime2, ‘2014-11-04 11:15:30.3214560’)
AT TIME ZONE ‘Central European Standard Time’ as CETTimeWithOffset
If inputdate contains offset, then function performs conversion to destination time zone:
SELECT convert(datetimeoffset, ‘2015-06-27 11:20:08.5783475 +02:00’)
AT TIME ZONE ‘Pacific Standard Time’ as PacificStandardTime
SELECT convert(datetimeoffset, ‘2014-11-04 10:15:30.3214560 +00:00’)
AT TIME ZONE ‘Pacific Standard Time’ as PacificStandardTime
SQL Server Analysis Services (SSAS) updates allow upgrading your existing models to 1200 compatibility level and a JSON editor for SSDT.
SQL Server PowerPivot and Reporting Services/Power View for SharePoint 2016 available now with CTP3.1! Make sure you use SharePoint Server 2016 Beta 2 available for download here. Earlier versions of SharePoint Server are not supported. You also need Office Online Server Preview because SharePoint Server no longer includes Excel Services. Excel Services, now called Excel Online Server, is now only available with Office Online Server. PowerPivot and Reporting Services/Power View for SharePoint 2016 therefore require Office Online Server.
For more information, see What’s New in SQL Server 2016 and SQL Server 2016 Release Notes.
