[SQL SERVER]SQL2016-TSQL增強

2016-12-08

1387
0
SQL Server 2016
2020-11-29

來看看SQL2016的TSQL有那些增強

TRUNCATE TABLE:可針對分區資料表的某一分區進行truncate

TRUNCATE TABLE sale.CustomerTransactions   
WITH (PARTITIONS (2, 4, 6 TO 8));

DROP IF EXISTS: 物件存在就刪除

-- 舊寫法 for Function
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[dbo].[fun_onSales]') AND type = N'FN')
DROP FUNCTION [dbo].[fun_onSales]
GO

-- 新寫法 for Function
DROP FUNCTION IF EXISTS [dbo].[fun_onSales]
GO

-- 舊寫法 for Column
IF EXISTS(SELECT * FROM SYS.columns WHERE name='PackedByPersonID' AND  
          OBJECT_ID = OBJECT_ID('[dbo].[Invoices]'))
ALTER TABLE [dbo].[Invoices] DROP COLUMN [PackedByPersonID]
GO

-- 新寫法 for Column
ALTER TABLE [dbo].[Invoices] DROP COLUMN IF EXISTS [PackedByPersonID];
GO

-- 舊寫法 for Store Procedure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
            OBJECT_ID(N'[Integration].[GetCityUpdates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Integration].[GetCityUpdates]
GO

-- 新寫法 for Store Procedure
DROP PROCEDURE IF EXISTS [Integration].[GetCityUpdates]
GO
-- 舊寫法 for Table
IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[Invoices]') AND type in (N'U'))
DROP TABLE [dbo].[Invoices]
GO

-- 新寫法 for Table
DROP TABLE IF EXISTS [dbo].[Invoices]
GO
…等。

ALTER TABLE WITH (ONLINE = ON | OFF):線上模式修改資料表欄位、資料型別、欄位定序、null設定、資料長度..等，

修改過程可允許查詢正常執行，該模式主要減少停機和影響系統時間，但要注意一些操作完成後，需要手動更新統計值。

ALTER TABLE [dbo].[Invoices] 
ALTER COLUMN BillToCustomerID int 
WITH (ONLINE = ON)

NO_PERFORMANCE_SPOOL:禁止執行計畫中使用spool 操作。一些情況當spool使用tempdb時，

將會降低查詢效能，透過該提示某些情況可改善效能。

select * from [dbo].[Invoices]
option(NO_PERFORMANCE_SPOOL)

USE HINT(SP1):提供查詢處理可使用的提示選項，主要是用來取代QUERYTRACEON，

因為QUERYTRACEON需要sysadmin權限。

select * from [dbo].[Invoices]
option(recompile, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))

sys.dm_exec_valid_use_hints可查到所有支援提示

Database Cloning: 複製資料庫schema and metadata, statistics,cloning of CLR,

Filestream/Filetable, Hekaton and Query Store objects並不包含資料。

DBCC CLONEDATABASE  (source_database_name, target_database_name)                         --Default CLONE WITH SCHEMA, STATISTICS and QUERYSTORE metadata.
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS      --SCHEMA AND QUERY STORE ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_QUERYSTORE      --SCHEMA AND STATISTICS ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS,NO_QUERYSTORE    --SCHEMA ONLY CLONE
Ps:SQL2014 SP2也支援。

FORMATMESSAGE:早期版本message都參考sys.messages，現在你可以自訂你的訊息。

SELECT text FROM sys.messages WHERE message_id = 20009 AND language_id = 1033;  
DECLARE @var1 VARCHAR(200);   
SELECT @var1 = FORMATMESSAGE(20009, 'rico', 'sherry');  
SELECT @var1;

declare @mystr varchar(100)
set @mystr=FORMATMESSAGE('I am %s, Hello %s','Rico','SQL2016')
select @mystr

DBCC CHECKTABLE (Transact-SQL), DBCC CHECKDB (Transact-SQL),

and DBCC CHECKFILEGROUP (Transact-SQL) :支援 MAXDOP 選項。

DBCC CHECKTABLE ('dbo.Invoices') 
WITH MAXDOP=4;

管理session: [SQL SERVER]SQL2016-管理session context。

CREATE OR ALTER(SP1):支援Stored Procedures, Triggers, User–Defined Functions, and Views

--以前寫法
Drop PORC What_DB_is_this
CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

--新寫法
CREATE or ALTER PROC What_DB_is_this     
AS   
SELECT DB_NAME()+':rico' AS ThisDB;

sys.dm_exec_query_stats DMV改善:新增24個欄位，針對Degree of Parallelism(dop)、

Memory Consumption、Parallel Threads有更詳細資料，更方便DBA了解查詢所使用資源情況。

JSON支援: [SQL SERVER]SQL2016-JSON(1)、[SQL SERVER]SQL2016-JSON(2)、[SQL SERVER]SQL2016-JSON(3)。

新資料物件: [SQL SERVER]SQL2016-新資料物件。

壓縮和解壓縮function: [SQL SERVER]SQL2016-壓縮、解壓縮新函式。

新字串function: STRING_SPLIT (Transact-SQL) and STRING_ESCAPE (Transact-SQL) 。

STRING_SPLIT:拆分字串
SELECT value FROM STRING_SPLIT('Lorem,ipsum,dolor,sit,amet.', ',');

Note:null將返回空資料。

SELECT top 10 [CustomerID]
      ,[CustomerName], value  
FROM [Sales].[Customers]  
CROSS APPLY STRING_SPLIT([WebsiteURL], '/');

STRING_ESCAPE:編碼特殊字元(only JSON)，並返回nvarchar(max)資料型別。

SELECT STRING_ESCAPE('" \\ \	/
', 'json') AS escapedText;

declare @name  nvarchar(20)='rico"chen'
declare @surname  nvarchar(20)='hung hsiang chen
'
declare @json nvarchar(max)
SET @json = FORMATMESSAGE('{ "id": %d,"name": "%s", "surname": "%s" }',   
    1, STRING_ESCAPE(@name,'json'), STRING_ESCAPE(@surname,'json') );  
select @json