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
