In previous post, we learnt basic introduction to SQL Server. In this post we will learn about SSMS (SQL Server Management Studio) software and how to use it. SSMS is a client tool which is used to connect and interact with SQL Server. Before working with SSMS tool, we should be aware that SQL Server is server software. In client server model, client sends request to server and the server software responds to the client request. We use SSMS as a client tool to interact with SQL server. Interaction is for the purpose of management of SQL Server. By using SSMS, we can control different features such as user management, security management, querying different database objects such as system tables, user defined tables, executing stored procedures etc.
Multiple instances of SSMS client tool can be used to interact with an instance of SQL server. If you have installed SSMS at your PC, you can run multiple instances of SSMS. Each instance of SSMS will have its separate session. In real-world production environment, SSMS client tools will be distributed across different client machines and they can interact with an instance of the SQL server simultaneously.
Connection with SQL server
To interact with SQL server, there must be connection between SSMS and SQL Server. We will learn how we can connect SSMS with SQL server. Needless to say that to connect SSMS with the SQL server, SSMS must be pre-installed on our system. Download SSMS from Microsoft site and install it at your system from this link if you have not installed it.
First of all, check that an instance of SQL Server is running on system because SSMS can connect with SQL Server only if it is running. If instance is running, open SSMS (SQL Server Management Studio); otherwise start an instance of SQL Server on the system. You can check the instance of SQL Server running at your system by opening the Services. If it is not in Running state then start it.
On running the SSMS, "Connect to Server" dialog box will open up as shown below. You have to fill the correct information to different text box controls to authenticate with SQL Server. Only authenticated user is allowed to login SQL Server because the server is a secure system.
Server Type: SQL Server Database Engine
Server name: Enter the server name that you want to connect with. Server name is an IP address or URL. At your local system, local host can be used as server name. Depending upon the instance of SQL Server running on system, you can decide about server name. In production environment, SQL Server will be installed at a server machine. At your local system, server name can be local host or MSSQLLocalDB etc.
LocalDB: At my system, an instance of SQL Server Express LocalDB is running. By default, access to the instance of LocalDB is limited to its owner. According to MSDN, LocalDB is a feature you select during SQL Server Express installation, and is available when you download the media. If you download the media, either choose Express Advanced or the LocalDB package. Visual Studio 2019 and 2022 customers should install SQL Server Express 2019.
The SqlLocalDB.exe can be searched in C:\Program Files\Microsoft SQL Server folder. At my system, it is available at C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe
Authentication: SQL Server is a secure system which needs correct credentials to connect with. There are different modes to authenticate to SQL Server.
- SQL Server Authentication: In this mode, enter valid username and password to connect with SQL server instance. This is known as SQL Server authentication mode to connect with.
- Windows Authentication: You can also use windows authentication mode to connect with SQL server. Connecting SSMS with SQL server using windows authentication is known as trusted connection. The best thing about windows authentication is that user doesn’t have to worry about username and password to connect with SQL Server. If you are using your personal computer then you don't have to worry about username and password, automatically the system will connect with SQL server in the trusted connection mode but in the business environment where active directory is used, the admin has to create different groups and users and so admin has to do some twitching to allow windows authentication to work for a user.
- Mixed Authentication: There is a third option to connect with SQL Server which is known as mixed mode. In this mode you can use either SQL server authentication or Windows authentication. I have used windows authentication to login. Note that If SQL Server is not running then connection will fail and will throw error as shown below.
After login, the first step is to explore all objects which are available in SQL Server. SSMS provides Object Explorer window to look at any object of the server. Press F8 to view Object Explorer if it is not visible. In SSMS, Object Explorer window looks like below snapshot.
Note that if SQL Server is freshly installed then only system databases and system database objects will be visible in Object Explorer.
Session in SSMS
When SSMS connects with SQL Server, session is created for the login. When user closes SSMS then session terminates for the login.
Running SQL Script in SSMS
Running SQL script is SSMS is fundamental skill. Press CTRL + N to open a SQL Query editor window. You can also click New Query button in Menu Bar for this. In the editor, write test script SELECT * FROM INFORMATION_SCHEMA.TABLES and press F5 or CTRL+E to execute the script. You can use Execute button to run the script. Before running a script, relevant database must be selected from dropdown. In this example, master database is selected.
Execution Plan of Query
The next skill in SSMS is to get the execution plan of a query. Press CTRL + L to get it. Look at the following result in snapshot for the above query.
Query Options
Query options can be set using by clicking ‘Query Options’ button in Menu Bar or by using Query > Query Options. Look at the following screenshot.
On clicking Query Options, we get Query Options dialog box which can be used to set the result or set ANSI settings etc.
Creating projects in SSMS
- To create a new project, press CTRL+SHIFT+N.
- View all projects in Solution Explorer.
A SQL Server script project has connections and queries apart from many other objects. You can create multiple script files in a project. A snapshot is given below.
Read more features at the link.
In the next post, we will learn about SQL Server System Databases.
Top comments (0)