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
If you select @@VERSION, you get the following back
Microsoft SQL Server vNext (CTP1) - 220.127.116.11 (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_AGGThis 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
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
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
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
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_spaceusedis now supported for in-memory tables.
sp_renameis now supported for native modules.
CASEstatements are now supported for native modules.
- The limitation of 8 indexes on in-memory tables has been removed.
TOP (N) WITH TIESis now supported in native modules.
ALTER TABLEagainst 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.
sys.dm_os_host_infoThis is a new OS related system DMV and it returns one row that displays operating system version information.
Here is the output
Here is a sample result set on Linux:
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