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! 😉
