What is the Server Management Studio?
SQL Server Management Studio is an IDE that provides a graphical interface for connecting and working with MS SQL server.
In this tutorial, you will learn
- Download and Install SQL Server Management Studio
- How to access “Management Studio”
- Access “Management studio” using Command line
- Introduction to Data Management Studio IDE
- SSMS Tips and Issues
- SSMS Versions and Updates
Step 1) Go to this link and click on Download SQL Server Management Studio 18.0 (preview 6)
Step 2) Once downloaded we will get a .exe file named as “SSMS-Setup-ENU.exe.” Double click on it
Step 3) Below window will appear. Click on ‘Install.’
Step 4) Installation will begin. Below screen will show Packages progress and Overall Progress.
Step 5) Once Completed, Setup will show the below screen with “Setup Completed” message.
Now, we’re ready to open “Management Studio. Go To Start Menu>Programs>Microsoft SQL Server Tools 18> Microsoft SQL Server Management Studio 18.
Below ‘Connect to Server’ screen will appear. Server Name defaults to the name selected while installing MS SQL server.
- Server type: This is an option to select one out of four available MS SQL services option. We will be working on ‘Database Engine’ for creating and working with Database. Other Server type includes Analysis, Reporting & Integration Services.
- Server name: This is Server’s name where MS SQL Server is installed and need to establish the connection with that server. Generally, we use the server name as “Machine name\Instance.” Here Instance is the name given to SQL Server instance while SQL server installation.
- Authentication: This is defaulted to “Windows Authentication” if we use “Windows Authentication” during SQL Server Installation. Else, if we select ‘Mixed Mode (Windows Authentication & Windows Authentication)’ then Authentication will be defaulted to “SQL Server Installation.”
- User name\Password: If Authentication is selected other than “Windows Authentication” like “SQL server Installation” then these two fields will be required.
Click on ‘Connect.’ Now you will be connected to ‘Data Management Studio.’
Alternatively, we can also open SQL Server Management Studio using Window Command line. You need to have full path of ssms.exe. Below is default location and file name:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE Exe name: ssms.exe
Here, are the steps to access via command line:
- Open Command Prompt by typing CMD in Search window.
- Type entire path followed by ssms.exe as shown below and Press ‘Enter’.
‘Connect to Server’ screen will appear in a similar way as described in the previous section.
Below is the start screen for Data Management Studio.
Let’s discuss each section in detail.
1) Object Explorer
The Object Explorer provides a tree view of the database objects contained in the server. This section shows all the Databases, Security, Server Object for quick reference. To view the components of each object, just click the + icon located to the left of the object which will expand it.
2) Databases Selection Dropdown
This dropdown allows the user to select the Database in which we will be running our queries.
3) Query Editor
Here we can write all our queries. MS SQL server provides interactive suggestions for tables, columns, etc. for easy queries creations and much more.
4) Execute button
This button will finally execute the query and return the results.
- Management Studio is a standalone product. It does not correspond to any specific version of SQL Server. For example, we can use SMMS Version 18 with SQL Server 2017, SQL Server 2016 as well.
- Large codes reduce readability. Use comments for better Readability. Put “–” in front of any line to comment it out.
- Group comment: We can comment out the group of line by selecting them all and clicking on Icon shown in below image.
- Unexpected shutdown, a system failure can cause unexpected data loss. Set ‘Autorecover’ option checked to minimize data loss. We can even customized time interval to AutoRecover data and the number of days this information to be saved before deleting it.Tools> Options>Environment>AutoRecover
In case of failure, the popup window will appear with the name
We can click and select the above-highlighted icon & run any Query. We will be prompt to specify the location where the file can be saved future reference.
SSMS first version was with SQL server 2005. SSMS remains an integral part of SQL server with SQL Server 2008, SQL Server 2012 and SQL Server 2016.In 2016, Microsoft started the first numerical series of versioning with the SSMS version number as 16.3. All series include 16.x, 17.x and 18.0 as version numbers.
Latest 18.0 series have the following versions: 18.0 (Preview 4), 18.0 (Preview 5) and 18.0 (Preview 6)
Until Dec 18, 2018 latest version is SSMS 18.0 (Preview 6)
More updates on 18.0 series can be found here – https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms?view=sql-server-2017
SSMS supported operating systems
Below table shows SQL server edition and its corresponding supported Operating system. ‘Y’ means Supported and ‘N’ Means Not Supported.
- Server Management Studio provides a Graphical interface for connecting and working with MS SQL Server.
- It provides Object Explorer, Query editor as a major window to start with.
Let’s understand each of the above fields