Installing and Configuring SQL Server Express Edition

SQL Server is Microsoft’s premier database management system that we can use to develop relational databases. It also offers support for graph databases. Depending on the services offered, SQL Server comes in various editions, such as Enterprise, Standard, Personal, Developer, Express, etc.

In this article, we’ll deal with the SQL Server Express Edition, a free edition of SQL server commonly used for developing desktop, web, and small-scale server applications. We’ll clarify how to download, install, and configure it. Along the way, we’ll also illustrate how to install and use SQL Server Management Studio which is an easy-to-use GUI-based tool for managing SQL Server databases.

Downloading and Installing the SQL Server Express Edition

You can download the SQL Server Express edition from the official website:

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

On the webpage, you will see several downloading options for different versions of SQL Server. Scroll down the page for the Express edition and click Download Now.

Note: At the time of writing this article, the SQL Server 2019 Express is the latest SQL Server Express edition.

You will download the application file named “SQL2019-SSEI-Expr.” When completed, execute this file to begin the installation process.

It starts with the dialogue box with the three options: Basic, Custom, and Download Media. Select Basic:

The next window contains the license agreement.  Read it carefully and click Accept if you agree with all conditions.

Before the installation starts, you need to specify the directory where to install the instance of your Microsoft SQL Server Express edition. I have accepted the default installation location, but you can change it. Click Install.

The installer will first download all modules required for installing the SQL Server Express edition and install those modules.

You can track the progress as shown below and pause the installation if needed by clicking the Pause button.

Once the installation completes successfully, you should see the window reporting the SQL Server instance name, the name of the administrator of the instance, the connection string for the database, and the SQL Server instance version.

It also presents you with four options: Connect Now, Customize, Install SSMS, and Close. If you do not want to do anything else at the moment, simply click Close. If you want to customize your installation with authentication settings, instance names, etc., click Customize.

The Install SSMS button relates to installing SQL Server Management Studio. We’ll deal with it further.

At this point, simply click Connect Now. A command line interface will appear. You can use it to connect to your SQL Server Express instance and execute queries over it.

First, let’s see the list of all default databases in the instance. Execute the following script:

1> select name from sys.databases
2> go

The output shows 4 default databases: master, tempdb, model, and msdb.

To ensure that the newly installed SQL Server Express instance is up and running, go to the search bar and type Services:

Then you will at once see the window with list of all services and their statuses. If your SQL Server Express instance is running, the status will be displayed accordingly (Running).

Downloading and Installing SQL Server Management Studio

If you work with SQL Server Express Edition, you should install SQL Server Management Studio (SSMS), as it the default tool to operate SQL Servers.

The installation file of SSMS is available at the official portal:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

On the webpage, click the Download SQL Server Management Studio (SSMS) link:

You are going to download the application file named SSMS-Setup-ENU. When done, execute this file. It might be needed to restart your system to complete the installation of SSMS:

After restarting, open the downloaded the SSMS-Setup-ENU application again.

In the new window, specify the download location for your SSMS or leave the default location. Click Install.

Once the installation completes, the system will report it:

Click Close.

Executing queries with SQL Server Management Studio

Go to the Windows search bar and type SQL Server Management. The Microsoft SQL Server Management Studio 18 icon appears at the top of the list. Click on it to launch the Studio.

SSMS will open and present the dashboard.

It will automatically detect the SQL Server Instance running on your system. Click Connect:

Once your SSMS is connected to the SQL Server instance, you should see the following dashboard with the list of all databases existing on the instance, along with server objects, security settings, and other management options.

To execute queries with SSMS against your SQL Server Express Instance, New Query on the top. In the text field, enter your query and click Execute right under the New Query button.

Let’s create a simple database. Enter the following code and click Execute:

CREATE DATABASE MyNewDB;

If your query is successful, the system will report it:

Expand the Databases section in the Object Explorer pane of SSMS, and you’ll see your newly created MyNewDB database there.

You can add tables, columns, and data to this database using traditional SQL queries.

Conclusion

This article described the processes of installing the SQL Server Express edition along with SQL Server Management Studio. SQL Server Express edition is a free, light-weight and easy-to-use database management system developed by Microsoft, and this makes it ideal for all newcomers to database programming and administration.

SQL Server Express is also useful for developing desktop applications, storing smaller amounts of business intelligence data, or creating small-scale web applications with no heavy traffic or huge data involved.

Read also

SQL Server Express Limitations and Use Cases