Entity Framework Core 5 is a great ORM and I love how efficient and concise it is. With the migrations mechanism enabled, you can generate the next migration based on changes applied to your model. This is so cool, but when it comes to other database objects, you are on your own. I mean – you can still use migrations, but you have to figure out a SQL statement yourself. Let’s take a look at some cool statements you can use with the SQL Server database.

CREATE OR ALTER

This is a very powerful command that literally just check if a database object exists, and it alters it or creates a new one based on that fact.

So instead of writing a statement like this:

IF OBJECT_ID('UpdateProfilesCountry', 'P') IS NOT NULL
DROP PROC UpdateProfilesCountry
GO

CREATE PROCEDURE [dbo].[UpdateProfilesCountry]
    @StardId int
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > @StardId
END

I can use CREATE OR ALTER, like this:

CREATE OR ALTER PROCEDURE [dbo].[UpdateProfilesCountry]
    @StardId int
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > @StardId
END

Additionally, CREATE OR ALTER does not reset permissions, so you don’t need to re-apply them, which would be necessary with the first script.

CREATE OR ALTER can be used with the following objects:

• STORED PROCEDURES (including natively compiled)
• FUNCTIONS (Transact-SQL, including natively compiled)
• TRIGGERS
• VIEWS

Other database objects like tables or indexes cannot be handled with that statement. You can find more information about it in this Microsoft post.

Note: CREATE OR ALTER is available from SQL Server 2016 SP1.

DROP IF EXISTS

DROP IF EXISTS is a useful statement that can be used for many database objects. It will check if the object exists and if it does, it will drop it – all in one statement.

Instead of writing a statement like this:

IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL 
  DROP TABLE dbo.Products; 

Now we can write a simple one-liner:

DROP TABLE IF EXISTS dbo.Products

Clean and simple. DROP IF EXISTS will work for many database objects, like:

• AGGREGATE
• ASSEMBLY
• VIEW
• DATABASE
• DEFAULT
• FUNCTION
• INDEX
• PROCEDURE
• ROLE
• RULE
• SCHEMA
• SECURITY POLICY
• SEQUENCE
• SYNONYM
• TABLE
• TRIGGER
• TYPE
• USER
• VIEW

You can read more about it in this Microsoft post. Also Note: DROP IF EXISTS is available from SQL Server 2016.

Hope you like it, maybe you have some SQL statements you find useful at your work? Give me a shout or leave a comment. Cheers! 😉