SQL Server 2016中引入的前5个T-SQL函数

One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.

在SQL Server发行版中要寻找的令人兴奋的事情之一是通常引入的新的内置T-SQL函数。 使用新功能的某些原因可能是因为SQL Server赶上了其他竞争对手，试图保持领先地位，或者只是响应Microsoft Connect站点对SQL Server功能的要求。 在本文中，我将带您了解SQL Server 2016中发布的前5个T-SQL函数。

存在的话 (DROP IF EXISTS)

There is a popular practice in T-SQL for checking for existence of SQL Server objects prior to creating them. Such a practice involves the usage of IF EXISTS and IF OBJECT_ID clauses as shown in Script 1.

在T-SQL中，有一种流行的做法是在创建SQL Server对象之前检查它们的存在。 这种做法涉及如脚本1所示使用IF EXISTS和IF OBJECT_ID子句。

 
IF EXISTS (SELECT * FROM SYS.schemas WHERE NAME = 'TMP')
BEGIN
	DROP SCHEMA TMP 
END
 
IF OBJECT_ID ('dbo.SQLShack', 'V') IS NOT NULL
BEGIN
	DROP VIEW dbo.SQLShack
END
 

The new DROP IF EXISTS clause further improves this practice by reducing the number of lines required to do the checks to a single line as shown in Script 2.

新的DROP IF EXISTS子句通过将执行检查所需的行数减少到单行，如脚本2所示，进一步改善了这种做法。

 
DROP SCHEMA IF EXISTS TMP 
DROP VIEW IF EXISTS dbo.SQLShack
 

Another benefit for using this new clause is that should the object you are attempting to drop not exist, you will not receive an error code.

使用此新子句的另一个好处是，如果要删除的对象不存在，则不会收到错误代码。

The examples provided in Script 1 and 2 make use of SCHEMA and VIEW object types, but you can also apply this new clause on the following object types:

脚本1和2提供的示例使用了SCHEMA和VIEW对象类型，但是您也可以将此新子句应用于以下对象类型：

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

汇总，过程，表，组件，角色，触发器，规则，类型，数据库，用户，默认，安全策略，视图，函数，顺序，索引，异步

Finally, you can also make use of this new clause when you alter the properties of your TABLE object. Script 3 shows that you can try to drop a column and constraint of a given table by using the DROP IF EXISTS clause. Just a reminder that although you will not receive an error message when you are dropping a column/constraint that doesn’t exist, you will still receive an error message if the table you are attempting to modify doesn’t exist.

最后，当您更改TABLE对象的属性时，也可以使用此new子句。 脚本3显示了您可以尝试使用DROP IF EXISTS子句删除给定表的列和约束。 提醒您，尽管在删除不存在的列/约束时您不会收到错误消息，但是如果您要修改的表不存在，您仍然会收到错误消息。

 
ALTER TABLE dbo.SQLShackTable DROP COLUMN IF EXISTS TEST
 

Msg 4902, Level 16, State 1, Line 11
Cannot find the object “dbo.SQLShackTable” because it does not exist or you do not have permissions.

Msg 4902，第16级，状态1，第11行
无法找到对象“ dbo.SQLShackTable”，因为该对象不存在或您没有权限。

 
ALTER TABLE dbo.SQLShackTable DROP CONSTRAINT IF EXISTS TEST
 

STRING_SPLIT (STRING_SPLIT)

One of the requirements you are likely to encounter in your T-SQL career is to break down a string into separate rows based on a given delimiter such as comma, space etc. Prior to the release of SQL Server 2016, many developers made used of user defined functions to split a string. Script 4 (taken from this blog) shows an example of user defined function that can be used to break down a string.

您在T-SQL生涯中可能会遇到的要求之一是根据给定的分隔符（例如逗号，空格等）将字符串分解为单独的行。在SQL Server 2016发行之前，许多开发人员都使用了用户定义的函数以分割字符串。 脚本4 （摘自该博客 ）显示了一个用户定义函数的示例，该函数可用于分解字符串。

 
CREATE FUNCTION [dbo].[fnSplitString] 
(@string NVARCHAR(MAX), @delimiter CHAR(1)) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX)) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1       
        INSERT INTO @output (splitdata) VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)        
    END 
    RETURN 
END
 

In Script 5 we make use of the function defined in Script 4 to split the string ‘001,002,003,004’.

在脚本5中，我们利用脚本4中定义的函数来拆分字符串'001,002,003,004' 。

 
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM [dbo].[fnSplitString] (@string,',')
 

The result of Script 5 execution are shown in Figure 1.

脚本5执行的结果如图1所示。

Similar to the DROP IF EXISTS clause, the STRING_SPLIT function has been introduced to make your T-SQL development experience easier as you don’t have to worry about writing long lines of codes as workarounds for missing string split functionality in SQL Server. This new function works similar to the user defined function workaround in that it returns a table with split values represented as rows. Script 6 uses the same sample data shown in Script 5 instead we have replaced the user defined function with the new STRING_SPLIT function.

与DROP IF EXISTS子句类似，引入了STRING_SPLIT函数可以使您的T-SQL开发体验更加轻松，因为您不必担心编写较长的代码行作为解决SQL Server中缺少字符串拆分功能的解决方法。 此新函数的工作方式类似于用户定义的函数，因为它返回一个表，其中拆分值表示为行。 脚本6使用与脚本5中所示相同的示例数据，但我们已用新的STRING_SPLIT函数替换了用户定义的函数。

 
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM STRING_SPLIT (@string,',')
 

The results of Script 6 execution are similar to those displayed in Figure 1.

脚本6执行的结果与图1中显示的结果相似。

STRING_ESCAPE (STRING_ESCAPE)

One of the primary functions of a database system such as SQL Server is to store and retrieve data in its original form. Some data’s original form may consist of special characters as shown in Text 1 which makes use of several special characters such as single quotes, double quotes, forward slashes and a horizontal tab.

数据库系统（例如SQL Server）的主要功能之一就是以其原始形式存储和检索数据。 某些数据的原始格式可能由特殊字符组成，如文本1所示，该文本使用了几个特殊字符，例如单引号，双引号，正斜杠和水平制表符。

‘Jane”s birthday is “11/05/1975”

简的生日是“ 1975年11月5日”

In order to store Text 1 data in SQL Server and be able to retrieve the original format as JSON data, we would have to apply escape characters. The new STRING_ESCAPE is used to preserve original text by applying escape characters according to JSON notation. The new function takes in a text to be formatted and returns a formatted output that is of JSON type.

为了将Text 1数据存储在SQL Server中并能够将原始格式作为JSON数据进行检索，我们必须应用转义字符 。 新的STRING_ESCAPE用于通过根据JSON表示法应用转义字符来保留原始文本。 新函数接收要格式化的文本，并返回JSON类型的格式化输出。

To illustrate the usage of this new function, we have stored the aforementioned text into a @test1 variable as shown in Script 7.

为了说明此新功能的用法，如脚本7所示，我们已将上述文本存储到@ test1变量中。

 
  DECLARE @test1 VARCHAR(800) = 'Jane''s birthday is	"11/05/1975"'
 

Then, we defined an @json variable to store a concatenated JSON string.

然后，我们定义了一个@json变量来存储一个串联的JSON字符串。

 
  DECLARE @json_val VARCHAR(800) = '[{"Name":"Jane Doe","Gender":"Female","DOB":"'+@test1+'"}]'
 

Figure 2 shows the results of a select statement against the @json variable.

图2显示了针对@json变量的select语句的结果。

 
  SELECT @json_val
 

However, when we validate the content returned in Figure 2 using the ISJSON function, we can see that zero (0) is returned which indicates the content is not of JSON type.

但是，当我们使用ISJSON函数验证图2中返回的内容时，我们可以看到返回了零（0），这表明该内容不是JSON类型。

 
  SELECT ISJSON(@json_val) [isjson]
 

The new STRING_ESCAPE allows us to handle the format of Text 1 better. As shown in Figure 4, we have revised our script to use the STRING_ESCAPE function and this results into a valid JSON data.

新的STRING_ESCAPE使我们能够更好地处理文本1的格式。 如图4所示，我们修改了脚本以使用STRING_ESCAPE函数，并将其结果转换为有效的JSON数据。

 
  DECLARE @json_val VARCHAR(800) = '[{"Name":"Jane Doe","Gender":"Female","DOB":"'+STRING_ESCAPE(@test1, 'JSON')+'"}]'
  SELECT @json_val
 

 
  SELECT ISJSON(@json_val) [isjson]
 

DATEDIFF_BIG (DATEDIFF_BIG)

Many of the KPIs (Key Performance Indicators) in Business Intelligence solutions involves determining how long it takes to complete tasks i.e. how long it takes to process an insurance claim based on the date the claim was logged versus the date it was completed. Since, its release in SQL Server 2008, the DATEDIFF function has been quite useful towards calculating such KPIs as shown in Script 9.

商业智能解决方案中的许多KPI（关键绩效指标）都涉及根据记录的日期与完成的日期来确定完成任务所需的时间，即处理保险索赔所需的时间。 自从DATEDIFF函数在SQL Server 2008中发布以来，它对于计算此类KPI（如脚本9所示）非常有用。

 
  SELECT DATEDIFF(MONTH,GETDATE()-100, GETDATE())
 

However, the limitation with DATEDIFF is that it returns an INT data type which is only limited to a range of 2,147,483,647. This range is not an issue when your DATEDIFF looks at differences in Days, Months, and Years. However, when trying to get differences in MILLISECOND you are likely to run into a SQL Server 535 error code as the returned value is too large to be held into an integer data type.

但是，DATEDIFF的局限性在于它返回的INT数据类型仅限于2,147,483,647的范围。 当您的DATEDIFF查看天，月和年的差异时，此范围不是问题。 但是，当尝试获取MILLISECOND中的差异时，由于返回值太大而无法保存为整数数据类型，您可能会遇到SQL Server 535错误代码。

 
  SELECT DATEDIFF(MILLISECOND,GETDATE()-100, GETDATE())
 

Msg 535, Level 16, State 0, Line 2
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

消息535，级别16，状态0，第2行
datediff函数导致溢出。 分隔两个日期/时间实例的日期部分的数量太大。 尝试将datediff与不太精确的datepart一起使用。

Luckily, in SQL Server 2016 we have a new function called DATEDIFF_BIG which can be used to return big integer values. Now, if we alter Script 9 to use DATEDIFF_BIG as shown in below, the statement successfully returns 8640000000.

幸运的是，在SQL Server 2016中，我们有一个名为DATEDIFF_BIG的新函数，该函数可用于返回大整数值。 现在，如果我们将脚本9更改为使用DATEDIFF_BIG，如下所示，该语句将成功返回8640000000。

 
  SELECT DATEDIFF_BIG(MILLISECOND,GETDATE()-100, GETDATE())
 

时区 (AT TIME ZONE)

There are instances whereby SQL Server data-driven applications (i.e. websites, intranets etc.) are accessed by clients around the world. A typical requirement in such applications may be to display the date retrieved from SQL Server according to clients’ time zones. In the past, implementing such a requirement entailed querying windows registry using Registry Extended Stored Procedures (i.e. MASTER.dbo.xp_regread) or 3^rd party solutions such as IANA time zones.

在某些情况下，世界各地的客户端都可以访问SQL Server数据驱动的应用程序（即网站，Intranet等）。 在此类应用程序中，典型的要求可能是根据客户端的时区显示从SQL Server检索到的日期。 在过去，实现这样的要求entailed查询使用注册表扩展存储过程（即MASTER.dbo.xp_regread）或^第三方解决方案，如Windows注册表IANA时区 。

Well the time to has come to throw away the workarounds as SQL Server 2016 introduces the AT TIME ZONE function which easily converts a given date into a datetimeoffset value of any time zone. Script 12 illustrates the application of this new function whereby the same current date (returned by the GETDATE function) is shown differently according to the specified time zones (the time zone used in the first SELECT statement is my default time zone, which in my case is South Africa Standard Time).

SQL Server 2016引入了AT TIME ZONE函数，该函数可以轻松地将给定日期转换为任何时区的datetimeoffset值，因此该放弃解决方法了。 脚本12演示了此新功能的应用，其中根据指定的时区（在第一个SELECT语句中使用的时区是我的默认时区），以不同的方式显示了相同的当前日期（由GETDATE函数返回）。是南非标准时间 ）。

 
  SELECT GETDATE() [Current Time]
UNION ALL
SELECT CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE 'Central European Standard Time'
UNION ALL
SELECT CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE 'Pacific Standard Time'
 

The results of Script 12 execution are shown in Figure 5.

脚本12执行的结果如图5所示。

For a complete list of time zones supported by this new function, browse the sys.time_zone_info system view.

有关此新功能支持的时区的完整列表，请浏览sys.time_zone_info系统视图。

结论 (Conclusion)

As you could have guessed by now, the underlying theme of my Top 5 new functions is convenience as these new functions are giving developers an ability to continue their T-SQL development with ease and without worry of maintaining obsolete workarounds functions.

正如您现在可能已经猜到的那样，我的前5个新功能的基本主题是便利，因为这些新功能使开发人员能够轻松继续进行T-SQL开发，而不必担心维护过时的变通方法。

翻译自: https://www.sqlshack.com/top-5-t-sql-functions-introduced-sql-server-2016/