Blog: Using Create or Alter Statement in Microsoft SQL Server

Managing customer’s SQL Server applications, we discovered a significant performance decrease related to query execution time. We figured out that the reason for that was the execution of a script, which checks the existence of database objects before updating them.

The Problem

The original script looked as follows:

1
2
3
4
5
6
7
8
9
10
11
12
IF OBJECT_ID('dbo.data_xxxx') IS NULL
BEGIN
CREATE VIEW dbo.data_xxxx
AS
SELECT VCV.item1, VCV.item2 AS item2, VCV.item3 AS item3 FROM TABLE_A VCV
END
ELSE
BEGIN
ALTER VIEW dbo.data_xxxx
AS
SELECT VCV.item1, VCV.item2 AS item2, VCV.item3 AS item3 FROM TABLE_A VCV
END

Apparently, the developers created this script to emulate the behavior of the CREATE OR REPLACE statement from many other database platforms. As you may already know, the elder versions of Microsoft SQL Server don’t support this kind of statements. However, since SQL Server 2016 SP1, you can opt for using CREATE OR ALTER statement in Microsoft SQL Server.

The Solution

So, we upgraded customer’s databases to the latest version of SQL Server. And then we replaced the original script with the following code:

1
2
3
4
CREATE OR ALTER VIEW dbo.data_xxxx
AS
SELECT VCV.item1, VCV.item2 AS item2, VCV.item3 AS item3 FROM TABLE_A VCV
GO

This approach allows for faster code execution when it comes to thousands of calls of a script within a short period of time.

We also use this approach in database migration projects. Particularly, when we migrate customer’s databases to Microsoft SQL Server from Oracle, we leverage this new CREATE OR ALTER statement instead of Oracle’s CREATE OR REPLACE. You can apply this statement to the following objects:

  • Stored procedures
  • Functions
  • Triggers
  • Views

Please note that you can not apply this option to tables because it is not supported. Usually, we modify tables separately from the programmable objects as the tables include valuable data. The thing is that the tables require rather more careful and thorough conversion approach.