SQL Server Management Studio

SQL Server Management Studio (SSMS) is an integrated development environment (IDE) used to manage SQL infrastructure, from SQL Server to Azure SQL databases. It provides tools and resources to configure, manage, administer and query SQL server and its components.

SQL Server Management Studio

Versions

#

This table shows the different versions of SQL Server Management Studio with its release year and download link.

Version Release Year Download Link
SQL Server Management Studio 18 (Latest) 2019 Direct Download
SQL Server Management Studio 17 2017 Direct Download
SQL Server Management Studio 16 2016 Direct Download
SQL Server Management Studio 2014 2014 Direct Download
SQL Server Management Studio 2012 2012 Direct Download
SQL Server Management Studio 2008 R2 2010 Direct Download
SQL Server Management Studio 2008 2008 Direct Download
SQL Server Management Studio 2005 2005 --

Components

SSMS is an IDE used to access, configure, develop, monitor, and manage all components of SQL Server, Azure SQL, and Azure Synapse Analytics. It provides a graphical tools with many rich script editors that provides access to all developers and database administrators all the tools they need.

Object Explorer

#

Object Explorer provides a hierarchical user interface used to view and manage SQL server objects.

This pane displays a tabular view of object instances and allows you to search through specific objects.

The capability of this component varies on the type of server but it includes development and management features on all server types.

Object Explorer Tasks
Task Description
Opens the Object Explorer and configure how the explorer will behave
Connects Object Explorer to an instance in Database Engine, Analysis Services, Reporting Services, and Integration Services
Manage objects in the Object Explorer hierarchy nodes
Provides a user interface in tabular view to manage SQL server objects
Run custom reports in SQL Server Management Studio

Template Explorer

#

Templates are files containing SQL scripts that helps you create database objects.

The Template Explorer displays all the templates which can be used in the code editor.

Custom templates can also be added using this component.

Template Explorer Tasks
Task Description
Opens a template and incorporate it into code editor window
Opens a template and replace its parameter values in the code editor

Solution Explorer

#

The Solution Explorer provides container called Projects that is used to manage database scripts, queries, database connections, and files.

A solution may have one or more projects, files, and metadata.

Solutions and projects contain scripts, queries, connection information, and files needed to create a database solution.

Solution Explorer Tasks
Task Description
Collects and manage one or more projects in a solution
Create a project and add items such as scripts and connections
Provides information regarding the SQL Server Management Studio files to manage solutions
Implements source control on queries and scripts

Visual Database Tools

#

The Visual Database Tools include visual designers used to build Transact-SQL queries, tables, and database diagrams.

Visual Database Tools Tasks
Task Description
Design database diagram
Design tables
Design SQL queries

Query Editor

#

The Query Editor is used as a basic editor to edit text files.

It includes a language service which defines the syntax of SQL Server languages.

It also implements IntelliSense and debugging.

This is used to build scripts and statements using the Database Engine Query Editor.

Aside from writing scripts and queries, it can also run scripts containing SQL statements.

Query Editor Tasks
Task Description
Open code editor in SSMS
Configure code editor in terms of line numbering and IntelliSense options
Manage view mode such as word wrapping, splitting windows or tabs
Set text formatting options such as hidden text and indentation
Implements incremental search and go to, to navigate through the code editor
Drag and drop scripts from one location to another
Set bookmarks to codes
View and use basic features in the MDX Query Editor
View and use basic features in the DMX Query Editor
View and use basic features in the XML/A Query Editor
Use sqlcmd features in Database Engine Query Editor
Use code snippets in the editor
Use the Transact-SQL debugger to step through codes and view debugging information such as paramaters and values

Features of SSMS

  • Database management
  • Object management
  • User management
  • Compatibility with SQL Server, Azure SQL, Amazon RDs for MS Server
  • Supports Unicode
  • Log Viewer
  • Activity Monitor
  • Resource Governor
  • Jobs Management
  • Database Designer
  • DB Compare and Synchronize
  • Backup and Restoration
  • Attach and Detach databases
  • Shrinking databases
  • Database Statistics
  • Query Builder
  • Script Editor
  • SQL Formatter
  • Debugger
  • Data Generator
  • Report Designer
  • Version Control
  • Database Documenter
  • Import and Export Data
  • Migration and Conversion

Download SSMS

1. Download the SSMS from Microsoft
You may download the latest SQL Server Management Studio and once downloaded, you will get an .exe file named SSMS-Setup-ENU.exe. Double click on this file to start installation.

2. Install the SSMS on your system
After double clicking the SSMS-Setup-ENU.exe file, a window will appear as shown below. Click on the Install button to begin installation.

Install SQL Server Management Studio

3. Wait for the installation progress
After beginning the installation progress, the Package Progress and Overall Progress will begin. Wait for it to be completed.

Install SQL Server Management Studio

4. Complete the SSMS Installation
After the progress is completed, the window showing Setup Completed message will be prompted.

Install SQL Server Management Studio

This ends the installation, you are now ready to use the SQL Server Management Studio.


Accessing SSMS

After you have successfully installed the SQL Server Management Studio, you can launch it right away from your programs. It will be named as Microsoft SQL Server Management Studio 18. After opening this application, a window will show up that asks for your server connection.

Access SQL Server Management Studio
  • Server type - Allows you to select which MS SQL service option to use. To create and manage database, you have to select Database Engine. The other types include Analysis Services, Reporting Services, Integration Services, and Azure-SSIS Integration Runtime.
  • Server name - The server where the MS SQL Server is installed and where the server connection should be established. Generally, the server name is Machine name\Instance. The instance is created during the installtion.
  • Authentication - This is set to default Windows Authentication which is specified during the installation. If you selected Mixed Mode then the authentication will be set to default as SQL Server Authentication.
  • Username and Password - If Windows or SQL Server Authentication is selected, these two fields are required to be filled in unless you left the password empty. These credentials are also set during the installation.

After setting all the configurations, click the button Connect and you will be connected to the Data Management Studio where you can already access all the components of SMSS.


Querying with SMSS

Once an SQL Server Connection succeeded, you will be able to view and manage objects within the Object Explorer where you may view the server name, SQL version, username, and other server objects depending on the server type.

SQL Server Management Studio Object Explorer

You may now start writing and executing scripts on the SMSS. Let's start by writing a query to create a new database in the SQL Server. A new query can be added by right clicking the server instance on the Object Explorer and selecting New Query or simply click the New Query on the tool ribbon.

Write Queries in SQL Server Management Studio

After clicking either options, the Query Editor will show. You can now start writing your queries on this tab. In this case, we will write a query to create a new database named DB_CUSTOMER_SANDBOX.

CREATE DATABASE DB_CUSTOMER_SANDBOX

After writing the query, to apply the changes, we need to run the script. Click on the Execute button on the tool ribbon or click F5 on your keyboard to execute the script.

Execute Queries in SQL Server Management Studio

On the Object Explorer, you may now be able to see the new database is added. Click on the Databases to expand it and see the list of databases in the SQL Server.

View Databases in SQL Server Management Studio

Once you click on the database name, all the database objects will be shown including Diagrams, Tables, Views, External Resources, and more. By clicking any of the objects, the data within will be shown. In here, we created a table inside the new database and wants to view it including its columns.

View Database Objects in SQL Server Management Studio

You may also like



Guides


vsn 3.1