What is SQL Server Management Studio (SSMS)?

1. IntelliSense - SSMS includes IntelliSense, a code completion feature that can save you time and improve your code accuracy by automatically suggesting completion options as you type.

   • For example, when you start typing a SELECT statement, IntelliSense will automatically display a list of available columns and tables. This can save you time and effort by reducing the need to manually look up and type out column and table names. IntelliSense also helps ensure that your code is formatted correctly and follows best practices, as it will automatically adjust the indentation and capitalization of your code as you write.

2. Code formatting - SSMS allows you to easily format your code according to your preferred style, including options for indentation, whitespace, and capitalization. To format your code in SSMS, you can use the following options:

   • Select the text you want to format, then go to the Edit menu and select “Advanced” followed by “Format Selection”. This will format the selected text according to the default code formatting options.

   • You can also use the shortcuts Ctrl + K and Ctrl + F to format the selected text.

   • To format an entire script, go to the Edit menu and select “Advanced” followed by “Format Document”. This will format the entire script according to the default code formatting options.

   • You can also use the shortcuts Ctrl + K and Ctrl + D to format the entire document.

3. Object Explorer- SSMS provides a comprehensive Object Explorer that allows you to browse and manage the objects in your database, including tables, views, stored procedures, functions, and more. You can perform various tasks such as creating and modifying objects, executing scripts, and managing security.

   • One of the key features of the Object Explorer is the tree view, which displays a hierarchical structure of the objects in your SQL Server instance, organized by the server, database, and object type. You can expand and collapse the nodes in the tree to browse the different objects, and use the search pane to find specific objects.

   • In addition to the tree view, the Object Explorer also has a detail pane that displays the details of the selected object, such as the properties, columns, and data for a table. You can use the detail pane to view and edit the properties of an object, or to see the data contained in a table or view.

   • Object Explorer is a vital part of SSMS, providing a central location for managing and working with the objects in your SQL Server instance. It can significantly improve your productivity and efficiency when working with SQL Servers.

4. Query Editor - The Query Editor in SSMS allows you to design and execute queries and view and modify queries easily. It provides a code editor with features such as syntax highlighting, code formatting, and IntelliSense to help you write and edit your scripts more efficiently. To access the Query Editor in SSMS, you can click the “New Query” button in the toolbar or use the shortcut Ctrl + N. This will open a new query window in the SSMS workspace. You can then type or paste in your Transact-SQL code and use the various features of the Query Editor to help you write and troubleshoot your code. The Query Editor has several features that can be accessed from the toolbar or the Query menu:

   • Execute - This allows you to run your script and see the results in the result pane.

   • IntelliSense - As I explained in the 1st point, it helps us give query suggestions and ensures that the code is formatted correctly.

   • Code formatting - This adjusts the layout and appearance of your code to make it easier to read and understand.

   • Debugging - Allows you to step through your code, set breakpoints, and watch variables to troubleshoot issues in your scripts.

   • Find and Replace - This allows you to search for and replace specific text in your script.

5. Customization - SSMS allows you to customize the environment to suit your needs, including options for changing the color scheme and adding custom scripts and templates.

How to connect an SQL Server instance in Management Studio(SSMS)?

To connect to an SQL Server instance using Management Studio (SSMS) follow below steps:

Step 1. Open SQL Server Management Studio.

Open SSMS by clicking the Start button and searching for “SQL Server Management Studio” as shown in firgure-6.

Figure-6

Step 2. Connect to SQL Server.

After opening SSMS, the “Connect to Server” window will open by default. If this window does not open, click on the Connect to Server icon in the Object Explorer tab, which is located on the left side as shown in figure-7.

Figure-7

Step 3. Fill required field and click on connect button. First, select Server type as “Database Engine” from the dropdown list. Then type the Server name that you want to connect. If you don't know server name, then read this blog for more details- How to Server Name on which SQL Server is installed. You can also enter the IP address of the server or use a local connection by specifying “.”

For Authentication: Select the authentication method that you want to use to connect to the server. If you select “SQL Server Authentication”, you will need to enter a username and password. If you select “Windows Authentication”, you will be logged in using your current Windows credentials. Then click “Connect” to connect to the server.

If the connection is successful, you will see the Object Explorer window displaying the list of databases and other objects on the server as shown in below figure-8.

Figure-8

Note: You may need to install SSMS on your computer before you can use it to connect to a SQL Server instance.

How to Create New Database in SQL Server?

To create a new database in SQL Server using SQL Server Management Studio (SSMS) follow the below steps.

Step 1. Right-click on the “Databases” folder.

In the Object Explorer panel, expand the server node and right-click on the “Databases” folder and Select “New Database” from the context menu as shown in below figure-9

Figure-9

Step 2. Enter Database name.

In the “New Database” dialog box, enter a name for the database in the “Database name” field. Optionally, you can specify the owner of the database and change the default file locations for the data and log files. Click “OK” to create the database as shown in below figure-10

Figure-10

Alternatively, you can use the following SQL statement to create a new database:

CREATE DATABASE database_name;

Replace “database_name” with the desired name for the database.

How to create a new table in a database using SSMS?

To create a new Table in SQL Server Database using SQL Server Management Studio (SSMS) follow the below steps.

Step 1. Create table in SQL Database.

In the Object Explorer panel, expand the server node then expand the Database node on which you want to create a table and right-click on the “Tables” folder and Select “New” from the context menu then another context menu open then select “Table” as shown in below figure-11

Figure-11

Step 2. Enter Column name and Data Type.

Enter Column name, Data Type, check allow nullable if you want that column is allowed to have a null value. To update more details, we can update from column properties like primary key, Identity, Foreign key and indexes etc. as shown in figure-13. If you would like to learn more about SQL Data types, then follow this article.

Figure-12

Step 3. Save the table.

After filling all details, click on save button as shown in figure-13, or you can also use the shortcut Ctrl + S to save the table.

Figure-13

Step 4. Enter table name.

Enter the table name, then click ok button to save the table with given name as shown in figure-14.

Figure-14

Alternatively, you can use the following SQL command to create a new table:

CREATE TABLE Customers (
  CustomerID int NOT NULL PRIMARY KEY,
  Name varchar(255) NOT NULL,
  Email varchar(255) NULL,
  Country varchar(255) NOT NULL
);

You can customize above SQL statement based on your requirement. If you want to add more column, then you add in above SQL statement.

If you would like to alter Columns DataType Without Dropping Table in SQL then click on this link and follow this article.

How to insert data into a table using SSMS?

To insert data into a table in SQL Server Management Studio (SSMS), you can use the INSERT statement. Here is the basic syntax for the INSERT statement:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, suppose you want to insert a few rows into the Customers table that has four columns (CustomerID, Name, Email, Country), you can use the following INSERT statement:

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (1, 'Deepak', '[email protected]', 'India');

How to run a SELECT statement to retrieve data from a database using SSMS?

To run a SELECT statement to retrieve data from a database using SQL Server Management Studio (SSMS) follow the below steps.

Step 1. Open query window.

Open the query window in SSMS by clicking on New Query button as shown in the below figure-15.

Figure-15

Step 2. Change default Database (Master) to required Database.

By using the below SQL statement, you can change the default database (Master) to the required database from your table exists from where you want to retrieve the table data. Here in my case I would like to fetch the data from CSharpCorner Database.

USE CSharpCorner;

After executing the above SQL statement default database has been changed from Master to required that is CSharpCorner Database as shown in below figure-16.

Figure-16

Step 3. Type your SELECT statement.

Now type SELECT statement to retrieve the data as given below SQL statement.

SELECT * FROM [Table Name];

Here in my case I want to retrieve the data from Table_1 and the results of the SELECT statement will be displayed in the results pane at the bottom of the window as shown in below figure-17.

SELECT * FROM Table_1;

Figure-17

Alternatively, you can right-click on table name then select Top 1000 rows as shown in below figure-18

Figure-18

then it automatically writes a SQL statement and executes to get the top 1000 rows from this selected table as shown in below figure-19.

Figure-19

Conclusion

Here, we have learned what SSMS is, how to download and install SSMS on Windows machines, what are useful features of SSMS, how to connect SQL instance in SSMS, how to create Database, how to create tables in a database, how to insert the data into table and how to retrieve the data from table using SSMS.

If you require any clarification/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL or to explore more technologies.

Thanks for reading, and I hope you like it.