The best overlooked addition to SQL 2016

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.

4 thoughts on “The best overlooked addition to SQL 2016

  1. I hate it. Breaks permissions when you drop something. Instead, check out:

    CREATE OR ALTER

    Works way better, supported on most commonly altered object types, and preserves permissions. Love it!

    Like

    • That’s a good point; but, this post was about tables, which aren’t included in the CREATE OR ALTER syntax. On another note, if you’re using CREATE you should include the permissions in the script anyhow, since the CREATE wouldn’t have permissions to begin with. There’s no doubt that CREATE OR ALTER is great; it just doesn’t fit here.

      My thinking is DROP IF EXISTS would work best for temp tables, which people often code IF EXISTS prior to creating them, in a proc.

      Like

      • Oh I agree, CREATE OR ALTER isn’t about tables – I’m just saying it’s so much better than DROP IF EXISTS, since D.I.E. has those permissions problems. Think third party environment: folks often add permissions outside of the creation script. I do wish they’d add CREATE OR ALTER support for tables, I just don’t find myself changing tables as often as I change code.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s