DEV Community: Ajeet

SQL Server Management Studio (SSMS)

Ajeet — Mon, 19 Feb 2024 21:49:58 +0000

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.

Introduction to SQL Server

Ajeet — Sat, 17 Feb 2024 16:03:16 +0000

In this post we will get basic understanding about SQL Server. Before that we will get understanding about clients and servers and their types in brief.
Server is software that processes the request sent by client software. The machine on which server software is installed is known as server machine and the machine on which client software is installed is known as client machine. When client sends a request to the server then server processes the request, if the server is running, and sends the response back to the client machine. This request-response cycle is known as communication between client and server.

There are different types of server software used for different purposes; for example, database server software, web server software, email server software etc. For each type of server software, we require specific type of client software; for example, for web server we need client software called Browser. Similarly for email server we use email clients. For example, Gmail is an email server running at an IP address. Microsoft Outlook is an email client. Email clients use protocol like IMAP or POP3.
SQL Server is database software developed by Microsoft. In market there is other database software such as MySQL, MS Access, PostgreSQL etc. In this post we will focus on SQL Server and its functionality, irrespective of its versions.
Database software is used to manage different databases created inside the database server and their objects. To understand a database server we should first of all understand what database is.

Databases are created and managed by database server. A database is a container to store data. Different types of data can be stored in a database; they are known as database objects. In a database, data is stored, fundamentally, inside tables and to manage the data of tables database server creates other database objects such as stored procedures, index, Rules, Roles etc. You can create N number of databases in SQL Server and each database will have database objects.
Before going ahead we look at the distinction between file database server and client server databases. In a file server database, data is stored in files and retrieved from it. When any change is made to the data then the file is opened by client application and data is written into it. To display existing data, application opens the file and read the data. In case of file database server, multiple users can write to the same data file.
In a client server database data is still stored in files. Access to the files is controlled by a master program known as server. If client application wants to make use of the existing data, the client application sends a request to the server application. The server finds the proper data and sends it back to the client.
Similarly when the client wants to write some data into file then it sends request to the server. The server processes the request and writes the data into the file. So we find that there is a controller program that is database server software which takes the responsibility of reading data from file and writing data into file. The client applications send requests and it is the responsibility of the server to decide processing these requests.
The server software acts as a gatekeeper of clients' requests and enforces different kind of policies such as data integrity, data security, user management etc.
In client server architecture, server software e.g. database server resides at server machine and through the network, client machine communicates with the server machine. The network bandwidth and other such factors affect the communication.
The client server database includes feature of logging of transactions which allows recovery of data on disk failure or network error. This is not available in the file server database.

Types of database
There are different types of databases available in the market based on different architectures and principles. There are different database models such as network database model, hierarchical database model, relational database model etc. The latest database model is relational database in which data is managed inside tables.
OLAP vs. OLTP
OLTP database mainly focuses on transactions processing; for example, insertion, deletion and updating of data. OLAP database mainly focuses on static data which are read to analyze the market trend etc. we will discuss about relational database in some other post which is based on relational principles.
SQL Server is relational database server
In a relational database, data are stored in tables based on relational database principles given by EF Codd. SQL Server is a relational database. Its core component is a database engine which is based on relational principles. The SQL Server database engine has query processor to execute a query and storage engine to store data securely. The storage engine uses file manager, lock manager, transaction manager and many utilities programs e.g. DBCC.
The query processor includes programs like Parser, Optimizer, SQL Manager, Database Manager and Query Executor.
Parser parses the SQL script and Optimizer provides query hints to optimize the script execution.
SQL Server Editions
Microsoft provides different editions of SQL Server.

The SQL Server Enterprise Edition is used in enterprise environment. It includes all features of SQL Server. The SQL Server Developer Edition has fewer features than Enterprise Edition. The SQL Server Express Edition is free edition.
Versions of SQL Server **
Microsoft has released different versions of SQL Server editions over long period of time. The SQL Server Community is the latest edition.
**Download SQL Server
Go to this link to download SQL Server latest edition.

In next post, we will learn about SQL Server Management Studio (SSMS).