SQL Server->> T-SQL new features

1) TRUNCATE TABLE partition instead of full table

CREATE TABLEdbo. Truncatepartitiontest (PrtcolINT, Col2NVARCHAR( -)) on [myPS1](Prtcol)GOINSERTdbo. TruncatepartitiontestVALUES(1,'AAA'), ( One,'AAA'), ( -,'AAA'), (101,'AAA')GO--TRUNCATE partitions 1 to 2TRUNCATE TABLEdbo. Truncatepartitiontest with(Partitions (1  to 2));GOSELECT *  fromdbo. TruncatepartitiontestGOTRUNCATE TABLEdbo. TruncatepartitiontestGOINSERTdbo. TruncatepartitiontestVALUES(1,'AAA'), ( One,'AAA'), ( -,'AAA'), (101,'AAA')GO--TRUNCATE partition 1TRUNCATE TABLEdbo. Truncatepartitiontest with(Partitions (1));GOSELECT *  fromdbo. TruncatepartitiontestGOTRUNCATE TABLEdbo. TruncatepartitiontestGOINSERTdbo. TruncatepartitiontestVALUES(1,'AAA'), ( One,'AAA'), ( -,'AAA'), (101,'AAA')GO--TRUNCATE partitions 1 and 2TRUNCATE TABLEdbo. Truncatepartitiontest with(Partitions (1,2));GOSELECT *  fromdbo. TruncatepartitiontestGO

2) NEW query hint No_performance_spool

In order to experiment with this new query hint keyword, I made a comparison of the IO statistics with a query statement enabled and not enabled for this query hint.

Added No_performance_spool statement and IO statistics

SET STATISTICS  on GO SELECT *  from [dbo]. [vwpo] WHERE between ' 2012-01-01 '  and ' 2014-01-31 ' OPTION (No_performance_spool);  

(5448row (s) affected)Table 'Workfile'. ScanCount 0, logical Reads0, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0.Table 'worktable'. ScanCount 0, logical Reads0, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0.Table 'Address'. ScanCount 5, logical Reads49038, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0.Table 'PurchaseOrder'. ScanCount 5, logical Reads9267, physical reads0,Read-Ahead reads0, LOB logical Reads17936, LOB physical Reads0LobRead-Ahead reads0.

No query hint added

SET STATISTICS  on GO SELECT *  from [dbo]. [vwpo] WHERE between ' 2012-01-01 '  and ' 2014-01-31 ';  

(5448row (s) affected)Table 'Workfile'. ScanCount 0, logical Reads0, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0.Table 'worktable'. ScanCount 1, logical Reads24864, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0.Table 'tbladdress'. ScanCount 5, logical Reads the, physical reads0,Read-Ahead reads0, LOB logical Reads0, LOB physical Reads0LobRead-Ahead reads0. LOBRead-Ahead reads0.Table 'Tblpurchaseorder'. ScanCount 5, logical Reads9267, physical reads0,Read-Ahead reads0, LOB logical Reads17936, LOB physical Reads0LobRead-Ahead reads0.

In fact, the difference between the two in time is basically not, the biggest difference in practice is also obvious and conceivable. The spool operator is characterized by the creation of work table for use by subsequent operators. And since disabling the spool operator means not using work table, this is reflected in the IO statistics results above. But that doesn't mean it's a good thing. Not using work table means that you need to scan the table every time, and that's a lot of overhead. In this example, the difference is negligible because the table involved in the scan is small. In my opinion, this feature is rarely used in real-world scenarios. Can only say there is better than nothing. The only thing that would have been to execute the plan was to let SQL Server decide for itself. Microsoft claims that this query hint can greatly improve the performance of query statements using spool in high concurrency scenarios. I admit it. But, in my view, this is a palliative approach. The general use of the spool statement is likely to be due to the lack of an applicable index, so the root cause is probably the absence of an available index. Second, the application of this hint may result in too many table scans and not necessarily improve performance. What really needs to be considered is whether the review statement is likely to be rewritten to reduce complexity and change the execution plan so that the pre-built index can be used to eventually get the optimal execution plan and whether the stage data is needed to address high concurrency problems.

3) DROP IF EXISTS statement

A stored procedure that used to drop a table always needs if EXISTS (SELECT * from sys.objects WHERE name = "and ...), and now finally has a more concise approach to achieve.

DROP <table| procedure| view| function| trigger> IF EXISTS <name>

It can be used for the following database objects

AGGREGATE	PROCEDURE	TABLE
ASSEMBLY	ROLE	TRIGGER
VIEW	RULE	TYPE
DATABASE	SCHEMA	USER
DEFAULT	SECURITY POLICY	VIEW
FUNCTION	SEQUENCE
INDEX	Synonym

4) When using DBCC CHECKTABLE and DBCC CHECKDB such statements, you can use the MAXDOP statement to avoid excessive impact on system performance, because if there are business-critical jobs working and the server has few processor cores, DBCC CheckTable easily leads to frequent context switching.

5) You can use Sp_set_session_context to set the session-level context and use Session_context to view the context content of a session-level primary key.

EXEC ' user_id ' 4 ;   SELECT Session_context (N'user_id');  

6) The Sp_execute_external_script stored procedure supports the execution of r language scripts in SQL SERVER 2016 and the creation of a resource pool for the R language through the Create external RESOURCE pool

7) Compress and decompress functions can compress strings using the gzip compression algorithm and extract strings using the ZIP algorithm

8) The new function Datediff_bigint and DateDiff are the same, but the difference is that the return value is bigint, so that in the case of millisecond it is possible to support a longer time difference. The new function at time zone can output a DateTimeOffsetwith a timezone.

SELECT getdate '  China Standard Time ';  -- From select * from Sys.time_zone_info

9) Two new string functions String_split and String_escape. The former is a table function, input text and separators can be split into a table to return the string. The problem is that the delimiter supports only 1 character-length characters. The latter is to aid in JSON-formatted data escaping special characters, such as slashes.

10) New database option Mixed_page_allocation can specify whether a database is first assigned to a mixed partition by default, and a previous version of SQL SERVER 2016 is assigned a mixed partition first.

SQL Server->> T-SQL new features