CREATE OR ALTER

One of the fundamentals that we learnt when learning SQL, that we use CREATE to create a database object, and ALTER if we needed to change its structure. It was simple and straightforward.

But it was too much of a problem writing a deployment script, where you had to check if the object existed, and then create or alter it based on the result. Or you could drop and re-create the object but that would make you lose all the permissions that have been set on the object. In short it tended to get a little messy. Or of course you could try a clever idea like this one. Putting it in as dynamic SQL was a clever alternative too,  but then you had difficult-to-read and difficult-to-maintain scripts.

SQL Server 2016’s Service Pack 1 now gives us the CREATE [OR ALTER] statement, with which you could create a database object or modify in just one go. It’s a feature been asked for, for quite a long time, and it’s finally here.

You can use CREATE OR ALTER on the following types of objects: Stored procedures, Functions, Views and Triggers. It would be nice to have this on tables too, but then we have the whole issue of tables being populated, and the effect that it would have on dependent tables would be all too complicated.

Here is a piece of code comparing usage:

THEN:

IF (SELECT OBJECT_ID(‘TestProcedure’)) IS NULL
EXECUTE(‘CREATE PROCEDURE TestProcedure AS PRINT ”Test”’)
GO

ALTER PROCEDURE TestProcedure
AS
BEGIN

PRINT ‘Here”s the real code I wanted to write’
END

NOW:

CREATE OR ALTER PROCEDURE TestProcedure
AS
BEGIN
PRINT ‘Here”s the real code I wanted to write’
END

 

Why I Love SQL Server 2016’s SP1

Service Pack 1 (SP1) for SQL Server 2016 pleasantly surprised a lot of us by coming out less than 6 months after SQL Server 2016 RTMed. What was even more pleasantly surprising were the several Enterprise-only features that have now been enabled for Standard and even Express editions with this release.

Two of my favorite features that have been enabled for Standard Edition are In-memory OLTP and ColumnStore. Why? In my line of work, there are lots of instances when I propose a business intelligence solution, that I have to envision a BI strategy for the organization that I deal with. This strategy of course, will not be a one time implementation. Business Intelligence is a journey, you would in a lot of cases need to start small, where budget is a primary concern, and you need to show the stakeholders value, and then based on the value you showcase you expand on technology, usage scenarios and more. And in these cases, value should come with costs that are not insane; Insane as in proposing an Enterprise Edition of SQL Server at the inception, just so that you could show value and performance.

And so now you know why I love Service Pack 1 for SQL Server 2016 — It helps me envision strategy with less cost.