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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 |
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:
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.
|
1 2 3 |
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.
|
1 2 3 |
ALTER TABLE dbo.SQLShackTable DROP CONSTRAINT IF EXISTS TEST |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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’.
|
1 2 3 4 |
DECLARE @string VARCHAR(800) = '001,002,003,004' SELECT * FROM [dbo].[fnSplitString] (@string,',') |
The result of Script 5 execution are shown in Figure 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.
|
1 2 3 4 |
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.
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.
‘Jane”s birthday is “11/05/1975”
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.
To illustrate the usage of this new function, we have stored the aforementioned text into a @test1 variable as shown in Script 7.
|
1 2 3 |
DECLARE @test1 VARCHAR(800) = 'Jane''s birthday is "11/05/1975"' |
Then, we defined an @json variable to store a concatenated JSON string.
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 |
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.
|
1 2 3 4 |
DECLARE @json_val VARCHAR(800) = '[{"Name":"Jane Doe","Gender":"Female","DOB":"'+STRING_ESCAPE(@test1, 'JSON')+'"}]' SELECT @json_val |
|
1 2 3 |
SELECT ISJSON(@json_val) [isjson] |
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.
|
1 2 3 |
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.
|
1 2 3 |
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.
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.
|
1 2 3 |
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 3rd party solutions such as IANA time zones.
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).
|
1 2 3 4 5 6 7 |
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.
For a complete list of time zones supported by this new function, browse the sys.time_zone_info system view.
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.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu