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 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.

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