SQL Server 2016 – Two things developers should know

Hello Friends,

There are lots of new features and improvement launched by Microsoft in SQL Server 2016. There are two small but really interesting things for the T-SQL Developers, DBA Developers who are involve in writing T-SQL code.

Situation: Let’s think about a situation where we are creating a new object (for example –  Stored Procedure) on Development server. I am creating it for the first time so I have written the code with Create Stored Procedure  syntax and it worked fine. Now I want to make some modifications to this code then I have written the code with Alter Stored Procedure syntax and again it worked fine. Now after successful development this code will go to the next level of servers like QA Server. Latest code will be taken from the repository i.e. Alter Stored Procedure syntax. We don’t have this object in QA environment so alter statement will be failed. To overcome from such situation generally we write the code like:

IF OBJECTPROPERTY(OBJECT_ID(N'dbo.usp_GetCustomerInfo'), N'IsProcedure') = 1
	DROP PROCEDURE dbo.usp_GetCustomerInfo;
GO
CREATE PROCEDURE dbo.usp_GetCustomerInfo
AS
..

Here, you can see that we are using two line code to drop the procedure. In first line we are checking the existence of the object, if object exists then drop the object in second line. After this new object can be created that might be the new object that we are creating first time or it might be the object we want to modify with drop and create.

If the object already exists then it will be dropped with the execution of two line of code. In SQL Server 2016, this task can be written in a single line and it will also minimize the number of line in your code as:

DROP PROCEDURE IF EXISTS dbo.usp_GetCustomerInfo;
GO
CREATE PROCEDURE dbo.usp_GetCustomerInfo
AS
..

Here, you can see that we are using single line code to drop the procedure and in next line we are creating the object to achieve the altering of this object. Now In SQL Server 2016, I can achieve the same using the single line of code as shown below:

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerInfo
AS
..

Now, we can achieve the same thing without writing the drop object statement. You can see the same logic has been implemented using a single line of code. Isn’t it really interesting?

PS: Create or Alter syntax is supported for stored procedures, Functions, triggers and views.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook