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.
Download
Downloading the SQL Server Express Edition
The Microsoft SQL Server Express edition can be downloaded from the official website.
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.
Downloading SQL Server Management Studio
If you work with SQL Server Express Edition, you should install SQL Server Management Studio (SSMS), as it is the default tool to operate SQL Servers.
The installation file of SSMS is available at the official portal.
On the webpage, click the Download SQL Server Management Studio (SSMS) link:
The application file is named SSMS-Setup-ENU.
Installation
Installing the SQL Server Express Edition
Here’s a step-by-step process of how to install the SQL Server Express Edition:
Step 1. Execute the file SQL2019-SSEI-Expr. to begin the SQL Server Express Edition installation process.
Step 2. Choose the option. There are three of them:
- Basic
- Custom
- Download Media.
Select Basic:
Step 3. Accept the terms. Read the license agreement carefully and click Accept if you agree with all conditions.
Step 4. Specify the location. Define 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.
Step 5. Finish the installation process. 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.
Step 6. Connect to your SQL Server Express instance. 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.
To see the list of all default databases in the instance, execute the following script:
1> select name from sys.databases2> 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 see a window with the list of all services and their statuses. If your SQL Server Express instance is running, the status will be displayed accordingly (Running).
Installing SQL Server Management Studio
Step 1. Restart your system to complete the installation of SSMS:
Step 2. Open the downloaded SSMS-Setup-ENU application.
Step 3. Define the location. In the new window, specify the download location for your SSMS or leave the default one. Click Install.
Once the installation completes, the system will report it:
Step 4. Finish the installation process and 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 and the SQL database is downloaded, 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.
How to Update SQL Server Express
You can update your SQL Server Express to a newer version. To do that, follow these steps:
Step 1. Go to the SQL Server Installation Center window and click Update from a previous version of SQL Server.
Step 2. Wait till the process of updates search is finished and click Next.
Step 3. Accept the license terms and Privacy Statement and click Next.
Step 4. Choose the SQL Server Express instance that you want to update and click Next.
Step 5. Agree to install Microsoft R Open and Python by choosing Accept and then click Next to continue.
Step 6. Specify the server accounts and collation configuration and click Next.
Step 7. In the Full-text Update window, select the Rebuild option and click Next.
Step 8. Click Close after the update process is finished.
Additionally, if you have Visual Studio Express downloaded, you can update it to a newer version, too.
How to Uninstall SQL Server Express
To uninstall SQL Server Express, do the following:
Step 1. Navigate to Start > Settings > Apps and choose Microsoft SQL Server 2019 from the app list.
Step 2. Click Uninstall.
Step 3. In the Uninstall window, choose the Remove option.
Step 4. In the drop-down menu, choose the SQL Server Express instance that you want to remove and click Next.
Step 5. Select the particular features to be uninstalled or All features if you want to remove all of them and click Next.
Step 6. Confirm uninstalling the features you’ve chosen by clicking Remove.
Step 7. Click Close after the uninstall process is finished.
Conclusion
This article described the processes of installing, updating, and uninstalling the SQL Server Express edition along with SQL Server Management Studio. SQL Server Express edition is a free, lightweight, 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
Tags: sql server, sql server express Last modified: September 23, 2021
