SQL Server 2016: Novinky T-SQL

Vydání nové verze SQL serveru přináší řadu změn. Pokusím se shrnout změny, které se týkají Transact-SQL.

Datumové funkce

DATEDIFF_BIG ( datepart , startdate , enddate )

Nová funkce, která vrací rozdíl dvou časových hodnot jako datový typ bigint.

SELECT DATEDIFF_BIG ( ms , '1900-01-01' , '2900-01-01' )

AT TIME ZONE

Je-li potřeba vrátit datum a čas včetně offsetu, lze použít:

SELECT GETUTCDATE()
AT TIME ZONE 'Central European Standard Time' AS Datum_s_offsetem

Přehled časových zón naleznete v tabulce sys.time_zone_info

Funkce pro práce s textovými řetězci

STRING_ESCAPE( text , type )

Funkce provede analýzu zadaného textu a v případě potřeby provede nahrazení nepovolených znaků escape sekvencemi platnými pro daný typ. Zatím je podpora pouze pro JSON.

SELECT STRING_ESCAPE('\\servername\c$\temp\soubor.txt', 'json') AS json_par

STRING_SPLIT ( string , separator )

Rozloží zadaný řetězec na části podle zvoleného oddělovače. Funkce se v dotazech chová jako tabulka.

DECLARE @veta varchar(100)
SET @veta = 'Rozlozeni dlouhe vety na jednotliva slova'
SELECT v.value FROM STRING_SPLIT (@veta , ' ') AS v

JSON

Nově se objevila podpora zpracování JSON formátu.

FOR JSON

Je klauzule, která umožňuje formátovat výsledek dotazu do JSON formátu:

SELECT
TAB.ServerName,
TAB.InstanceName,
(TAB.ServerName + '\' + TAB.InstanceName) AS SqlServer,
TAB.[Folders.DataFolder],
TAB.[Folders.LogFolder],
TAB.[Folders.BackupFolder]
FROM (
SELECT
'server01' AS ServerName,
'SQL2016_01' AS InstanceName,
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_01\MSSQL\DATA\' AS [Folders.DataFolder],
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_01\MSSQL\DATA\' AS [Folders.LogFolder],
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_01\MSSQL\Backup' AS [Folders.BackupFolder]
UNION
SELECT
'server01' AS ServerName,
'SQL2016_02' AS InstanceName,
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_02\MSSQL\DATA\' AS [Folders.DataFolder],
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_02\MSSQL\DATA\' AS [Folders.LogFolder],
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_02\MSSQL\Backup' AS [Folders.BackupFolder]

) AS TAB
FOR JSON PATH, ROOT ('Servers')

Výstupem je JSON:

{
"Servers":[
{
"ServerName":"server01",
"InstanceName":"SQL2016_01",
"SqlServer":"server01\\SQL2016_01",
"Folders":{
"DataFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\DATA\\",
"LogFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\DATA\\",
"BackupFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\Backup"
}
},
{
"ServerName":"server01",
"InstanceName":"SQL2016_02",
"SqlServer":"server01\\SQL2016_02",
"Folders":{
"DataFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\DATA\\",
"LogFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\DATA\\",
"BackupFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\Backup"
}
}
]
}

OPENJSON( jsonExpression [ , path ] )

Převede JSON na tabulku

DECLARE @JSON varchar(MAX)

SET @JSON = '{"Servers":[{"ServerName":"server01","InstanceName":"SQL2016_01","SqlServer":"server01\\SQL2016_01","Folders":{"DataFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\DATA\\","LogFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\DATA\\","BackupFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_01\\MSSQL\\Backup"}},{"ServerName":"server01","InstanceName":"SQL2016_02","SqlServer":"server01\\SQL2016_02","Folders":{"DataFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\DATA\\","LogFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\DATA\\","BackupFolder":"C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016_02\\MSSQL\\Backup"}}]}'

if (ISJSON(@JSON) > 0) BEGIN

SELECT * FROM
OPENJSON ( @JSON, '$.Servers' )
WITH (
ServerName   varchar(20) '$.ServerName' ,
InstanceName varchar(20) '$.InstanceName',
SqlServer varchar(20)    '$.SqlServer',
DataFolder varchar(255)   '$.Folders.DataFolder'  ,
LogFolder varchar(255)   '$.Folders.LogFolder'  ,
BackupFolder varchar(255)   '$.Folders.BackupFolder'
)
END

ISJSON ( expression )

Provede validaci JSONu a vrátí 0 v případě, že se nejedná o validní JSON

JSON_VALUE ( expression , path )

Vrátí hodnotu konkrétního stringu v JSONu.

SELECT JSON_VALUE ( @JSON, '$.Servers[0].Folders.DataFolder' )

JSON_QUERY ( expression [ , path ] )

Tato funkce vrací objekt narozdíl od předchozí funkce, která vrací naopak hodnotu

SELECT JSON_QUERY ( @JSON, '$.Servers[0].Folders' )

JSON_MODIFY ( expression , path , newValue )

Pomocí této funkce lze měnit hodnotu stringu v JSONu. Funkce vrací změněný JSON.

SELECT JSON_MODIFY ( @JSON, '$.Servers[0].Folders.DataFolder', 'nova_hodnota')

Systémové funkce

COMPRESS ( expression ) a DECOMPRESS ( expression )

Nově přibyla možnost komprimovat a dekomprimovat hodnoty sloupce. V případě velkých textů uložených v textovém sloupci lze ušetřit místo. Ke komprimaci se používá algoritmus GZIP.

DECLARE @Text varchar(MAX)

SET @Text = 'Text, ktery bude komprimovan'

DECLARE @TEMP_TABLE TABLE(
Hodnota varchar(100),
CHodnota varbinary(MAX)
)

INSERT INTO @TEMP_TABLE VALUES (@Text, COMPRESS(@Text))

SELECT Hodnota,CAST(DECOMPRESS(CHodnota) AS varchar(MAX)) AS DekompHodnota FROM @TEMP_TABLE

CURRENT_TRANSACTION_ID()

Vrátí ID aktivní transakce. Srovnejte vrácené hodnoty před a po odkomentování zakomentovaných řádků.

DECLARE @ctid1 bigint
DECLARE @ctid2 bigint

--BEGIN TRANSACTION t1

SET @ctid1 = CURRENT_TRANSACTION_ID();
WAITFOR DELAY '00:00:02';
SET @ctid2 = CURRENT_TRANSACTION_ID();

SELECT @ctid1, @ctid2

--COMMIT TRAN t1;

SESSION_CONTEXT( key )

Vrací hodnotu klíče v kontextu aktivního připojení. Pomocí kontextu si lze ukládat různé sdílené hodnoty platné pro dané připojení.

Nastavení databáze

ALTER DATABASE SCOPED CONFIGURATION

Microsoft rozšířil možnosti nastavení platné pouze pro jednu konkrétní databázi o nové položky:

  • CLEAR PROCEDURE_CACHE
  • MAXDOP
  • LEGACY_CARDINALITY_ESTIMATION
  • PARAMETER_SNIFFING
  • QUERY_OPTIMIZER_HOTFIXES

Ostatní

CREATE TABLE, ALTER TABLE

S novou verzí se též objevily nové možnosti tabulky. Doporučuji přečíst si nápovědu.

TRUNCATE TABLE

Nově přibyla možnost odstranit data pouze z jedné konkrétní části tabulky, je-li použit partitioning.

CREATE DATABASE SCOPED CREDENTIAL

Vytvoří oprávnění pro databázi, které může být použito pro přístup k externím zdrojům (např. Hadoop, Azure Storage Blob, atp.)

CREATE EXTERNAL RESOURCE POOL, ALTER EXTERNAL RESOURCE POOL, DROP EXTERNAL RESOURCE POOL

Pro potřeby R serveru existuje management External Resource Pool. Detailnější informace lze nalézt v nápovědě.

SERVERPROPERTY

Přibyla nová vlastnost serveru: IsPolybaseInstalled

10.8.2016 Tomáš Jurke MSSQL