SQL Server 2016 T-SQL Enhancements

Few enhancements are worth discussing.

• DROP IF EXISTS

In old versions of SQL Server we had to check if the table or column exists before dropping it. In SQL Server 2016, this is made simple by making IF EXISTS optional part of DROP TABLE, DROP INDEX, etc.

DROP TABLE IF EXISTS dbo.Product

DROP TRIGGER IF EXISTS trProductInsert

DROP INDEX IF EXISTS Department.DeptID.trProductInsert

It works with the following objects;

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

For column use the following syntax;

ALTER TABLE Department DROP COLUMN IF EXISTS abc

• STRING_SPLIT

Prior to SQL Server, we had to create a Table value function to get the list of character separated values. MS have now made it the part of SQL Server built-in functions.

Select * from STRING_SPLIT('a,b,c',',')

Also works with the variable;

DECLARE @tags NVARCHAR(400) = a,b,,c,d' 

 

SELECT value 

FROM STRING_SPLIT(@tags, ',') 

WHERE RTRIM(value) <> '' 

Returns values are nvarchar if the input in nvarchar or nchar. Otherwise returns varchar. It accepts a single character as separator.

• SYSTEM_TIME

For system versioned tables, we can check the history values in the query;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08' And '2017-02-10';

The above query will give you all the updates made on the tblDepartment (with system versioning enabled) between the two given dates. You can also specify the date and time;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08 09:00:00.000' And '2017-02-10 10:00:00.000';

We will discuss the system version tables in another article.

• JSON

Create JSON string on the fly;

Select *

from Department

FOR JSON Path

Added functions are;

OPENJSON:

DECLARE @JSON NVARCHAR(4000)

SET @json = N'[{"DeptID":1,"DeptName":"Dept 1","ManagerID":1,"SysStartTime":"2017-02-09T08:54:49.6660045","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":2,"DeptName":"D2","ManagerID":1,"SysStartTime":"2017-02-09T08:54:18.6513263","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":3,"DeptName":"D3","ManagerID":1,"SysStartTime":"2017-02-09T09:03:01.9862457","SysEndTime":"9999-12-31T23:59:59.9999999"}]';

SELECT *

FROM OPENJSON( @json) a

We can also work with multiple records in a json string. In order to link the data in Jason strings to its respective Jason string;

SELECT *

FROM OPENJSON( @json) a

Cross Apply OPENJSON( a.value)

As you can see in the upper query, the cross apply is used to parse the complete data in the JSON string with multiple records.

Cycle Clipboard Ring SQL Server 2016

We have a new feature “Cycle Clipboard ring” in SQL Server management studio SSMS 13.0.16100.1. This new feature of SSMS allows you to toggle between what you have copied on the clipboard.

For example, if you copy something from one query window;

Use  DBNAME

Go

select * from Table1

Then copy another command from another query window;

Use  DBNAME2

Go

select * from TableName2

Now open a third query window and press CTRL+SHIFT+V; you will see one of the items from above. Keep holding CTRL+SHIFT and re-press “V” and you will see other item from the clipboard. This happens as the anything copied on the clipboard, it goes into the ring and it keeps rotating with the key combination on CTRL+SHIFT+V.

PS: Anything copied outside from SSMS before opening SSMS is also the part of this ring.

LEN and DATALENGTH

LEN function is very commonly used function in t-sql. One thing to note in this function that is ignores the trailing blanks;

Declare @v VarChar(5)

Set @v = 'ati '

Select Len(@v)

Same goes for CHAR and NVARCHAR.

To overcome this, use and character at the end and minus 1 from the length J

Declare @v VarChar(5)

Set @v = 'ati '

Set @v = @v + '?'

Select Len(@v)-1

Some people might use DATALENGTH function;

Declare @v VarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

But be alert as DATALENGTH function counts the bytes, not the length. If you change the data type of @v from varchar to nvarchar, the result will change;

Declare @v NVarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

Thanks for reading.

XACT_ABORT V/S TRY .. CATCH

Few days back we witnessed an issue that the SQL Server was running slow. We investigated and soon found out that there were uncommitted transactions on the server. We re-confirmed those transactions from the developers and resolved the issue. Someone tossed the idea to apply SET  XACT_ABORT ON in all SPs so that this situation may not occur again. That made me to write this article so that I can explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know that this options just takes the whole batch a one transaction. If any of the statement in the batch fails, the transaction of the whole batch is rolled back. Commits in case of success. Simple. But not that simple. You cannot control the flow of transaction with if XACT_ABORT is ON. TRY .. CATCH will not work. Even if you have the error log is implemented in CATCH block, it will not be saved as it will also get rolled backed. So, DB end error log is gone. In some cases you may need to continue with the rest of your code in case of an error, for example return a dataset. You cannot do this as well. The plus point you will never have an open transaction on your server. All transactions will either commit or rollback.

Another way to handle an open transaction issue is to apply TRY .. CATCH in all SPs. Ok, I know. There are few limitations of TRY .. CATCH. TRY .. CATCH do not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction in case of a schema error (table does not exists, etc ). Check the code below;

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

      

Execute the above statements. You will end up with an opened transaction.

You can also try this in a stored procedure. You will still end up in an open transaction.

Bow add SET XACT_ABORT ON;

       SET XACT_ABORT ON

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

Your transaction will be rolled back and you will find no open transaction.

CONCLUSION

XACT_ABORT ON solves the open transaction issue but at the cost of logging implemented in CATCH block (if you have any).

But “NonexistingTable” type of issues should not be in the production environment. Like why will I deploy a SP using a table that does not exists? Strange thing for me. You should have QA in place and your deployment scripts should be verified before running in the production environment.

 You decide.

SET DATEFIRST and SET LANGUAGE

This is a tricky part. SQL Server have language settings at the following levels;

1.       Server

2.       User Login

3.       Session

You need to be very clear for the language settings of the user / login as it may impact the date functions;

DECLARE @Today DATETIME; 

SET @Today = getdate(); 

 

SET LANGUAGE Italian; 

SELECT DATENAME(month, @Today) AS 'Month Name'; 

Select @@DATEFIRST

 

SET LANGUAGE us_English;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE British;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE us_English;

GO 

As you can see from the above code, I have set the language back to US English (English in SQL Server language drop downs) as I like to have it that way. You also see that @@DATEFIRST function returns different value for different languages. This value is set on the basis of;

1.       Language selected

2.       SET DATEFIRST option

You can change the language and still keep the First date of week same as in US English;

SET LANGUAGE us_English;

Select @@DATEFIRST

SET LANGUAGE British;

Select @@DATEFIRST

SET DATEFIRST 7

Select @@DATEFIRST

As you can see, US_English sets the @@DateFirst to 7 and British sets it to 1. But we have used SET DATEFIRST to change the language to British but kept @@DATEFIRST  to 7 using SET DATEFIRST option.

This is very important when you are working multilingual databases. Where ever you are using day of week values (in DatePart, etc), you should keep this change in mind as well. Either use one language throughout your database for Server, users and session. But if there might be any change to get the benefit of SQL Server multilingual support, you should use SET DATEFIRST into account when programming to keep all the calculations and checks aligned.