Two T-SQL features that I appreciate

We occasionally get cool improvements in T-SQL in newer versions of SQL Server. Here’s a short post on some of the improvements that I like a lot.

DROP [x] IF EXISTS

Introduced in SQL Server 2016.

When I first saw this command, I was like, awesome! This makes deploy scripts or scripts with temp tables easier.

Now queries with temp tables are super easy to write. Just write, for example:

DROP TABLE IF EXISTS #test_table

SELECT *
into #test_table
FROM sys.dm_exec_requests

Boom! The query is instantly re-runnable without a DROP TABLE command at the very end of the statement. Here’s the results on my test machine, and I can run this query over and over without it failing.

But wait, there’s more. What if you wanted to create a nonclustered index with the same idea? (Keep in mind that dropping an index can be a blocking operation)

DROP INDEX IF EXISTS ix_test1 ON Posts

CREATE NONCLUSTERED INDEX ix_test1 ON Posts(OwnerUserId)  

Again, you get the same result every time you run:

CREATE OR ALTER [x]

This feature is available in SQL Server 2016 SP1 and above.

This is possibly the most useful addition for code deploys. Instead of having to check if the object exists in your code deployer logic, just use create or alter like so:

CREATE OR ALTER PROCEDURE sp_Test as
BEGIN
SELECT 1
END
GO

Every time you run this code, it completes successfully.

I’m using create or alter often, it just makes life easier. Stay tuned!