Many of the widely advertised and talked about features of SQL Server or other software products focus exclusively on the hip new thing as opposed to quality of life. I’ve even recently heard people complain to Microsoft that they only focus on new features instead of making existing ones better.

T-SQL is one of those that doesn’t fall into that category. Ten years ago with SQL 2008 we got a heap of “quality of life” updates to T-SQL with the += and inline set for variables. SQL 2016 gave us CREATE OR ALTER; but, that’s not all.

In my opinion, one of the best new TSQL features that no-one seems to talk about is the IF EXISTS on DROP TABLE. Checking if a table exists has been a widely diverse array of code. I’ve seen this written in at least 6 different ways and some of those perform very poorly.

One example of a very poor solution is using sys.objects or sys.all_objects:

IF EXISTS (SELECT OBJECT_ID FROM sys.all_objects WHERE name = ‘Test’ and type = ‘u’)

BEGIN

DROP TABLE Test;

END

ELSE

BEGIN

CREATE TABLE TEST( ID int, Val varchar(20))

END

Another option may be using OBJECT_ID

IF OBJECT_ID(‘Test’, ‘U’) IS NOT NULL

BEGIN

DROP TABLE Test;

END

ELSE

BEGIN

CREATE TABLE TEST( ID int, Val varchar(20))

END

The great thing about SQL 2016 is we can replace all those lines with two.

DROP TABLE IF EXISTS Test

CREATE TABLE TEST( ID int, Val varchar(20))

The best thing about this uniformity. By adding IF EXISTS to DROP there’s now one single way to perform this action and it’s optimized and very easy to read.

Are there any negatives? Yes. You can’t use this if your application runs on older versions of SQL Server.

What about databases running in an older compatibility mode on 2016? Not an issue! I ran the code above on my 2016 test instance in a database set to level 100 and everything worked just fine.

Thanks MSFT for continuing to make simple TSQL improvements that make our lives easier and more efficient.