Getting Started With SQL Server Management Studio (SSMS)

Published:26 August 2022 - 8 min. read

Nicholas Xuan Nguyen Image

Nicholas Xuan Nguyen

Read more tutorials by Nicholas Xuan Nguyen!

Meet Active Directory and Windows Server auditing, security and compliance needs with ManageEngine ADAudit Plus. Download Free Trial!

You’re not alone if you’ve ever felt overwhelmed working with SQL Server Management Studios (SMSS). Figuring out how to use all features SMSS offers can be tricky. But take it easy. This tutorial is just what you need to get started.

In this tutorial, you’ll learn the basics of working with SSMS, like connecting to a server, running queries, and creating databases.

Read on and take your SQL server management skills to the next level!

Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have the following.

Downloading and Installing SQL Server Management Studio (SSMS)

SSMS is a free integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SMSS provides a GUI to manage databases and objects on your server.

But before running queries or creating databases, you’ll first have to ensure SSMS is installed correctly. So write and test queries, and create and alter database schema on your local machine or in the cloud.

1. Open your favorite web browser, and head to SMSS’s download page.

2. Scroll down to the Download SSMS section and click the Free Download for SQL Server Management Studio (SSMS) hyperlink to start the download.

The file’s size is about 700MB, so the download process might take a few minutes to complete, depending on your internet connection.

Downloading SQL Server Management Studio (SSMS)
Downloading SQL Server Management Studio (SSMS)

3. Double-click the downloaded file to launch the SSMS installer.

4. Finally, click Install to start the installation process.

You can change the default installation directory if you want, but the default location is fine for most users.

Initiating installing SSMS
Initiating installing SSMS

SSMS loads, extracts, and installs the required files, which may take a few minutes.

Viewing SSMS installation progress
Viewing SSMS installation progress

Once the installation completes, you’ll see the Setup Completed screen below.

Congratulations! You’ve successfully installed SSMS on your machine.

Verifying installation is complete
Verifying installation is complete

Verifying the MySQL Server is Running, and TCP/IP Port is Open

You’ve just installed SSMS, but you need to connect to a database server before you can do SQL server management. For this tutorial, you’ll connect to a MySQL server via SSMS.

Trying to connect to a MySQL server at the moment would be a stretch. So, first things first. Ensure your MySQL server is running and your TCP/IP port is open.

1. Open a PowerShell as administrator for elevated privileges.

2. Next, run the following ping command to verify your TCP/IP port is open. Replace your-ip with your MySQL database server’s IP address.

./ping your-ip

The ping responses in the output below indicate your MySQL database server is reachable.

Verifying the MySQL database server is reachable
Verifying the MySQL database server is reachable

If you get a Request timed out error, like the one below, your database server is unreachable, and you need to check your network configuration.

3. Finally, run the below commands to check if the MySQL database server is running.

# Gets running services that contain the words "sql server"
Get-Service | Where {$_.status -eq 'running' -and $_.DisplayName -match "sql server*"}
# Verifies the MySQL (MSSQL15) folder exists
Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\mssql*" -Recurse -Include Errorlog |select-string "SQL Server is now ready for client connections.” 
Checking if the MySQL database server is running
Checking if the MySQL database server is running

If the database engine service isn’t running, launch the Windows Services, and restart the service, as shown below.

Starting the database engine service
Starting the database engine service

Connecting to the MySQL Database Server

Your MySQL database server is running, and that’s a good start. But the server doesn’t do much right now. To manage SQL databases, you’ll need to connect to your database server via SSMS.

Open SSMS, and you’ll be greeted with the Connect to Server dialog box shown below the first time you launch the tool.

Connect to your MySQL database server with the following:

  • Server name – Input your MySQL database server’s IP address or hostname. If you’re running SSMS on the same machine as your database server, you can put localhost as the server name instead.
  • Authentication – Select Windows Authentication to connect to the database server using Windows Authentication, which is more secure.
  • Click the Connect button to connect to the database server.
Connecting to the database server
Connecting to the database server

If you didn’t get the Connect to Server dialog box, click the Connect button (top-left) under the Object Explorer pane, and select Database Engine.

The Object Explorer is a tree view control that lets you browse objects in your database server — like File Explorer.

Initiating a database server connection
Initiating a database server connection

Once connected to the database server, you’ll see the Object Explorer populate with your database server and various objects within each database.

Verifying database server connection
Verifying database server connection

Creating a New Database

After connecting to your database server, it’s time to fill it up with databases and tables and insert data into tables. Having a central location (database) for all your data makes data fetching quicker when you need it.

To create a new database using SSMS:

1. In the Object Explorer panel, select the Databases node and click New Query at the toolbar, which opens a new query window (step two).

Opening a new query window
Opening a new query window

2. In the query window, enter the following T-SQL snippet, which checks if a database with the name ATADB already exists.

If the database exists, the statement does nothing. But if the database doesn’t exist, the statement creates a new database named ATADB.

-- Use the master database.
USE master

GO

-- Check if a database named ATADB exists.
IF NOT EXISTS (

  SELECT name

  FROM sys.databases

  WHERE name = N'ATADB'

  -- If the ATADB database exists, do nothing.
)

-- If the ATADB database doesn't exist, create the database.
CREATE DATABASE [ATADB]

GO

3. Press F5 or click Execute to run the query, as shown below.

Executing the T-SQL
Executing the T-SQL

After running the query, you’ll see a message that says Command(s) completed successfully if the database is created successfully. The Completion time is the time SSMS took to execute the query and create the database.

Confirming commands completed successfully
Confirming commands completed successfully

4. Finally, click on the refresh icon in the Object Explorer panel, and you’ll see the newly created database ATADB.

Verifying the newly-created database
Verifying the newly-created database

Creating a New Table

You’ve just created a database and know what’s next — a table. A table lets you store data, organized in columns and tables.

To create a new table in SSMS:

1. Copy and paste the following SQL snippet into the query window, which creates a table called Users in the ATADB database. This table will have four columns: CustomerId, Name, Location, and Email.

The first column (CustomerId) is the primary key of the table. A primary key is a column that uniquely identifies each row in the table. The other columns are just regular data columns.

Notice a data type associated with each column:

  • The CustomerId column is an INT data type used for storing whole numbers.
  • The Name, Location, and Email columns are all NVARCHAR data types that store text data.
-- Use the ATADB database
USE [ATADB]
-- Check if the Users table 
IF OBJECT_ID ('dbo.Users', 'U') IS NOT NULL
-- If the Users table exists, delete the table
DROP TABLE dbo.Users

GO

-- Create the Users table if it doesn't exist
CREATE TABLE dbo.Users

(
  -- Primary key column
  CustomerId INT NOT NULL PRIMARY KEY, 
  -- Regular columns
  Name [NVARCHAR](50) NOT NULL,
  Location [NVARCHAR](50) NOT NULL,
  Email [NVARCHAR](50) NOT NULL
);

GO

2. Execute the query to create the Users table if it doesn’t exist.

3. Lastly, click on the refresh icon again in the Object Explorer panel to see the newly created table (Users).

Verifying the newly-created table
Verifying the newly-created table

Inserting Data Into a Table

An empty table wouldn’t be useful, so why not insert data into your newly-created Users table? Inserting data into a table is just like copying and pasting text to a sheet’s cells. But with SSMS, you use the INSERT INTO query instead.

Populate the SQL snippet below to the query window to INSERT data INTO the Users table on each column respectively.

-- Insert data into the columns in the Users table
INSERT INTO dbo.Users

  ([CustomerId],[Name],[Location],[Email])

-- Set the values to add to the Users table
VALUES

  ( 1, N'Adam', N'US', N''),
  ( 2, N'Listek', N'India', N'[email protected]'),
  ( 3, N'Donna', N'Germany', N'[email protected]'),
  ( 4, N'Janet', N'United States', N'[email protected]')

GO

Execute the query and see SSMS inserts data into the Users table.

You can see below that the message says 4 rows affected. Jump to the following step to see the actual data.

Inserting data into the Users table
Inserting data into the Users table

Querying Data From a Table

You’ve successfully inserted data to your Users table, but how do you verify that? Execute an SQL snippet to view the actual data inserted into your Users table.

Put the following SQL snippet into the query window. This query will SELECT all (*) of the columns and rows from the Users table.

SELECT *
FROM dbo.Users;

Execute the query, and you’ll see the data from the Users table in the results pane, as shown below.

results pane
results pane

Conclusion

In this tutorial, you’ve learned how to install and use SQL Server Management Studio (SSMS) to create a database and table. You’ve inserted data into a table and realized how SSMS makes your life easier managing data in your database by verifying data in an actual table.

Why not use pre-built templates to quickly create databases to build on this newfound knowledge? Or perhaps create shortcut key bindings to work with SSMS easier?

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks

Looks like you're offline!