Thursday, March 30, 2017

Developer Friendly T-SQL Enhancements in SQL Server 2016 and vNext

Both SQL Server 2016 and vNext versions have introduced many new features. T-SQL language too gets new features in each SQL Serer versions. Below two enhancements to T-SQL language is very important for DBAs and Database Developers who writes T-SQL heavily.

  1. CREATE OR ALTER

You can create SQL Server objects like below by using CREATE OR ALTER statement.

    CREATE OR ALTER PROC dbo.TestProc
    AS
    BEGIN
       SELECT @@VERSION
END

This way, you can create or alter stored procedure, function, view, and trigger.  However, this does not apply for table object.  This saves developers time tremendously   because it avoids numerous validation steps.

  1. DROP IF EXISTS

The new DROP EXISTS statements also avoids some conditional logic that you need to include to check the existence of the object before it drops.

DROP PROC IF EXISTS dbo.TestProc

This statement works with many SQL Server objects. See the below figure;

Source: MSDN

So if you’ve SQL Server 2016 or vNext in your environments, you can start using these new features right away.

Cheers!

No comments:

Post a Comment

Understanding Skewed Data in SQL Server

My latest article.  http://bit.ly/2qd4rtl Understanding Skewed Data in SQL Server bit.ly Introduction I re...