Module 1: SQL Server Management

Previous Tutorial - Next Tutorial

In this course, we will understand two tools in brief. The first one is SQL Server Management Studio, which is also known as SSMS, and the second one is SQLCMD. SQL Server Management Studio is the main interface tool to connect to the SQL server and is widely used by the application developer, database administrator, and data analyst.

The SQL server management studio interface looks almost like the visual studio IDE. Now here one thing to keep in mind is SQL Server Management Studio is the client tool, which is used to connect to the database server and it is not a database server itself. So sometimes many developers are confused with this concept.

We will use this SSMS throughout this course and another tool is SQLCMD. The SQLCMD is the utility that is used to execute t-SQL test statements, stored procedures, and SQL script files from the command line prompt. SQLCMD supports several arguments that help us on how to connect to our database instance and execute SQL queries by specifying the various options. We will understand each and every option in brief within a few minutes. But first, let us start with the SQL server management studio.

Before we go ahead, I'm assuming that you have SQL Server installed on your local machine with all the server and client tools. How to install SQL Server is out of scope for this course. This is the brief introduction of SQL Server Management Studio for Beginners. I just cover the basic UI components and a few simple tasks that you can do with it. Now let's start the SQL server management studio.

Click the Windows logo in the bottom left corner to bring up the program menu and then type SSMS and you will get something like this and then click on it.

Getting Started with SQL Management Studio

Now, after the management studio opens, you will be prompted to connect to a server. In the server name box, you can type either the name of your local computer or you can also type in the bracket local, and then it will ask you for authentication.

Here you can specify Windows authentication and SQL server authentication. So when you install the SQL server at the time, it will ask for authentication mode. Let see, if you select makes more, then you can select authentication using windows and SQL Server. So here I will select SQL Server, then I will specify the username and password.

Server Management Image

After I connect, the first thing we can see on the left-hand side is called Object Explorer. The Object Explorer detailed pan is displayed by default and is used to explore the database objects on the server. It is a tool that can be used to create databases, tables, and some other database related activity. Object Explorer is arranged in a standard review with different groups of objects such as database, security, server objects, etc. Now, on the top of the tree, there is a server name.

Server Management Explained Image

So here you can see the different database names. You can expand it further and then you can see the tables and views. You can expand it further and you can see the list of tables names. You can go ahead further and you can also see the name of these tables, and when you right-click on let's see if you want to see the property of this column, then you can right-click on it and go to the property and it will display the property of that particular column in detail.

Server Management Tool Image

Further, you can right-click on the table name, and here you can change the design of this table or you can fetch the top hundred rows from this table. So let's see, if I select this menu option, then it will select the top one thousand records from this category table. So what it will do for me is write, create the query and submit it to the database engine and it will parse and then it will fetch the results into this grid.

Server Management Tutorial Image

Now, if you notice that there are two tabs, the first one is the result tab and another one is the message tab. In the message tab, you will see the number of records it will fetch from the table. You can also see the error if you enter a wrong table and if you execute the query, it will give you the error message in the message tab.

So over here, what you see with this query is called query editor, or you can see a code editor. It provides the ability to open, edit, and create a new query. You can open the new query editor window over here when you click on a new query, it will open another query window.

Server Management Guide Image

SQL Management Studio Main Features

Here you can change the database name from the database menu. So let me select this database and I can write a query to fetch the TOP-10 records from categories. You can execute this query either by clicking on this execute or you can also press F5 to execute the result.

Server Management SQL Image

So when you execute this query by default, it will show the result into this grid. So when you mouse over this icon, you can see that result to the grid, so it will display the result into the grid. Now, If I want to display this result into the text format then if I select option result to text and when I execute this query, then it will give me the result in the text format. I can also execute the multiple queries in the same code editor windows.

Server Management Database Image

Now, when I press F5 then it will give me results into the grid. The first one is of the category table and the second one is from the product table. So there are many options you can explore over here in SQL server management studio.

Web API for developers Free Trial Offline SDK

SQL video tutorial:

Other useful tutorials: