SQL SERVER 2016 – IF EXISTS Function on SQL Azure Databases and More

The experimentation of working with the next release of SQL Server is always fun and when I wrote the blog T-SQL Enhancement “Drop if Exists” clause, lesser did I know such capabilities are already existing with Azure SQL DB. When I was presenting at one of the local user groups, I told most of the capabilities come to Cloud first, get test and the very reason that it scales without problem on cloud gives one the confidence that it would work in our enterprise. One of the attendees asked, has all features first introduced to cloud only? Let us learn about IF EXISTS Function on SQL Azure Databases and More.

That got me curious and I started to hunt to find is the rollout always to cloud first. I thought, let me experiment first and see. I took my DIE (DROP IF EXISTS) post to see if I can run it on SQLAzureDB to know if it worked seamlessly. First to my connection to the latest database I created.

As I was running the CTP2.0 of SQL Server 2016, I wanted to see what version was running on cloud already. It was a surprise that the production was already running the latest bits. It was a pleasant surprise and I know by the time you read this article and try out – maybe Microsoft would have upgraded to the latest bits too. So is the scale and agility that cloud brings to the table.

Now coming back to the core, I was now getting a feeling that my DIE (DROP IF EXISTS) code will work now perfectly. I went ahead with the following code and it executed just fine.

DROP TABLE IF EXISTS my_test
GO
DROP PROCEDURE IF EXISTS my_test_procedure
GO

Now, it was time to search for something interesting that I can do on cloud that was not available on-premise SQL Server. So I went to the documentation to search for something interesting. I found a new function DATEDIFF_BIG() that was interesting. Currently the DATETIME functions that we use with SQL Server cannot give the high precision values. Let me take a typical value of – number of nanoseconds in a year?

SELECT DATEDIFF_BIG(nanosecond, '2015-1-1 00:00:00.0000000', 
'2016-1-1 00:00:00.0000000')
GO

This returns “31536000000000000” which is not available with SQL Server 2016 on-premise version currently. It was a great learning to see the rate at which innovations happen on cloud. If you run this on an on-premise SQL Server – you will get the following error today.

Msg 195, Level 15, State 10, Line 1
‘DATEDIFF_BIG’ is not a recognized built-in function name.

I am curious to know, how many times have you wanted the higher level of precision when working with DATEDIFF functions? What are some of your usecases for the same? I would surely like to learn some from you. Do let me know via the comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)