Tag Archives: ms sql server

Useful SQL statements when writing EF Core 5 migrations

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.


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

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

I can use CREATE OR ALTER, like this:

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

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)

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


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

  • VIEW
  • ROLE
  • RULE
  • 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! 😉 

Set up a SQL Server in a docker container

You might wonder, why would I do need to create a docker image with SQL Server? If I were to set up the infrastructure for test or production environment, I would set up a SQL Server in Azure. To automate this process I would follow infrastructure as a code a pattern, creating terraform script and deployment pipeline for it. That is a different case.

Scenarios where that can be useful:

  • you spend 3 days setting up a database to work with your app and you would like to have a back-up of this state
  • you are a front-end Mac user, that doesn’t really like to configure something with a Microsoft label 😉
  • you want to share a particular MS SQL database state with all developers, with a different setup
  • you need that DB just for a quick task and pulling that docker image will take only a few minutes

What do you need

Please mind the fact that I’m a w Windows user.

  • a docker implementation – I’m using Docker Desktop
  • Azure Data Studio, or any other tool, to connect to MS SQL Server
  • Docker Hub account, or any other docker repository

Creating a docker container

First of all, let’s check if Docker is installed on your machine. Just type `docker –version` and you should see something similar to this.

Now let’s pull a SQL Server docker image from Microsoft, this one is Server 2017 developer edition:

docker pull mcr.microsoft.com/mssql/server:2017-latest

Now let’s check what docker images I have on my machine.

I already had a SQL Server docker image, so I didn’t have to download it for the second time.

Now let’s create and run a docker container with the command:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=myPass123" -p 1433:1433 --name primeHotelDb -d mcr.microsoft.com/mssql/server:2017-latest

A few comments on what is happening here:

  • we are creating a docker container with the name primeHotelDb
  • we are using a mcr.microsoft.com/mssql/server:2017-latest docker image
  • we are passing ACCEPT_EULA and SA_PASSWORD parameters
  • this will set up a password myPass123 for the SQL Server for user sa
  • SQL Server will be available on localhost on port 1433, which is a first of the two passed in

Now, let’s check that we can connect to this SQL Server, I’m using an Azure Data Studio, which is a handy tool and I must admit – much quicker then SQL Server Management Studio.

The connection was successful, let’s now make some changes and save it, so that we would know later on, that the state of this container was persisted.

As you can see, I created an empty HotelDB database.

Preserving the container state

The easiest way to preserve the state of a container is to send a docker repository. I choose Docker Hub cause it comes with Docker Desktop installation and it’s free for community use. 

But first, we need to:

  • commit changes made in our container to a new docker image
  • we need to name our image michalbialecki/prime-hotel-db

This is because my Docker Hub account is michalbialecki and it will accept repositories only in that format. This can be done with a single command

docker commit primeHotelDb michalbialecki/prime-hotel-db

Now let’s check how my docker images look like.

Apart from Microsoft’s original image, there is my new image named michalbialecki/prime-hotel-db.

Let’s send this image to Docker Hub. To do that we need to log in.

docker login --username=michalbialecki

And after providing a valid password you should see something like this:

Next thing we need to do it to push the image to Docker Hub.

docker push michalbialecki/prime-hotel-db

And our docker image will be pushed to the Docker Hub repository.

After the process finish, I can go to the browser and check my Docker Hub account.

Yay! There is my image! In Docker Hub I can add collaborators, so they will be able to push images to my repository as well.

Now let’s check that it really works. Let’s remove my hotel container and repository from my machine.

docker stop primeHotelDb
docker rm primeHotelDb

Now if you list your container, there should be no primeHotelDb (docker ps -a).

The next command will remove the docker image.

docker rmi michalbialecki/prime-hotel-db

You can check, that this image was removed (docker images).

The next thing to do is to pull a docker image from Docker Hub.

docker pull michalbialecki/prime-hotel-db

Now let’s create a new image and run the container under the name primeHotelDbV2.

docker run -p 1433:1433 --name primeHotelDbV2 michalbialecki/prime-hotel-db

Then if I connect to the container with Azure Data Studio, I will see, that my HotelDb exists. It means, that changes done to my container were persisted!


In this article, we learned how to:

  • set up a docker image with SQL Server inside
  • connect to an instance of SQL Server in a container
  • save you local changes to a new docker image
  • push and share docker image with Docker Hub  

As you could see, docker is nothing to be afraid of. It is a great way to host apps regardless of the operating system. It is also a great way to work with SQL Server without the need to install and configure everything yourself.

Hope you found that useful, cheers!