Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1

Earlier today Microsoft released the first CTP of the next version of SQL Server, I have a post about that here SQL Server 2016 SP1 released, SQL Server vNext available for download

I downloaded and installed SQL Server on a VM (But only after the VM decided to install Windows 10 anniversary update first). I played around with some of the new things, let's take a look

Version
If you select @@VERSION, you get the following back

Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)   Nov  1 2016 23:24:39   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor)

The compatibility for this version of SQL Server is 140

STRING_AGG

This is a brand new string function in SQL Server, If you have used MySQL then the STRING_AGG is similar to the GROUP_CONCAT function, however you can't use DISTINCT like you can in MySQL

The function STRING_AGG concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. This function is available beginning with SQL Server 2016 SP1

Here is a sample query

SELECT STRING_AGG (name, ',') AS csv 
FROM master..spt_values
WHERE type = 'A'; 

Here is the output

rpc,pub,sub,dist,dpub,rpc out,data access,collation compatible,system,use remote collation,lazy schema validation,remote proc transaction promotion

Nothing special of course but at least you don't have to write your own version and thinking about removing the last comma

A better example would be if you wanted to get all the columns for a table next to the table name
You would think the query would be like this

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
ORDER BY 1

However that gives you the following error

Msg 8120, Level 16, State 1, Line 41
Column 'sys.tables.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You need to add a GROUP BY to the query

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

Here is what you get back

That looks much better, I can see myself using this a lot in the future

In memory OLTP/Columnstore


According to the documentation sp_spaceused is now supported for in-memory tables.

I tried it out and with SQL Server 2016 I get back 0 rows and 0 KB, with vNext I get back rows but NULL for everything else

  name rows reserved data index_size unused

ShoppingCart 0                   0 KB 0 KB 0 KB 0 KB  -- 2016

ShoppingCart 53248              NULL NULL NULL NULL -- vNext

Not sure what is going on there.

You can now add a columnstore index to a table that has a varchar(max) data type in vNext

CREATE TABLE t_bulkload (
accountkey int not null,
accountdescription varchar (max),
accounttype char(500),
AccountCodeAlternatekey int)
GO

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

Running that on SQL Server 2016 gives the following error

Msg 35343, Level 16, State 1, Line 39
The statement failed. Column 'accountdescription' has a data type that cannot participate in a columnstore index. Omit column 'accountdescription'.

Running that same code on SQL Server vNext 2016 gives the following warning

Warning: Using Lob types (NVARCHAR(MAX), VARCHAR(MAX), and VARBINARY(MAX)) with Clustered Columnstore Index is in public preview. Do not use it on production data without backup during public preview period.


Here are some more new things in regard to in memory tables

• sp_spaceused is now supported for in-memory tables.
• sp_rename is now supported for native modules.
• CASE statements are now supported for native modules.
• The limitation of 8 indexes on in-memory tables has been removed.
• TOP (N) WITH TIES is now supported in native modules.
• ALTER TABLE against in-memory tables is now substantially faster in some cases.
• Transaction redo In-memory tables is now done in parallel. This substantially reduces the time to do failovers or in some cases restarts.
• In-memory checkpoint files can now be stored on Azure Storage. This provides equivalent capabilities to MDF compared to LDF files, which already have this capability.

As a final note on in memory OLTP, I must say that I ran everything in SQL Server 2016 and also in SQL Server vNext, running the stuff on the vNext instance seemed faster to me.

sys.dm_os_host_info

This is a new OS related system DMV and it returns one row that displays operating system version information.

SELECT * FROM sys.dm_os_host_info;  

Here is the output

host_platform	host_distribution	host_release	host_service_pack_level	host_sku	os_language_version
Windows	Windows 10 Pro	6.3		48	1033

Here is a sample result set on Linux:

host_platform	host_distribution	host_release	host_service_pack_level	host_sku	os_language_version
Linux	Ubuntu	16.04		NULL	1033

Here is what it looks like in SSMS

That is all for now..... you can find all the new stuff on MSDN https://msdn.microsoft.com/en-US/library/mt788653(SQL.130).aspx