SQL Server Management Studio – Part-2

In part-1 of the SQL Server Management Studio tutorial, I’ve explained about SSMS, connecting SQL Server with SQL Server Management Studio and about Object Explorer. Here in part-2, I’ll walk you through the below details in SSMS:

Contents of SQL Server Management Studio Tutorial Part-2

  • SQL Query editor window.
  • Changing windows layout.
  • Some editing tools.
  • Registered servers.
  • About templates, solutions and scripting projects.

Video Walk Through On SQL Server Management Studio – Part-2

SQL Query Editor Window

Query editor window is for writing, editing, executing and debugging SQL queries like select query, insert query, update query, stored procedures, views, indexes, etc..

SQL Server Management Studio Query Editor

New Query Window

  1. To open the query editor window press the New Query icon from the tool bar. A new query editor window will open in the document windows panel.
    SSMS New Query
  2. The query editor window connects to the database which was selected in the object explorer. Here, the editor window is connected to the database MtbDbTwo. This is because the database is already in selected state in the object explorer.
    SSMS Query Editor DB Connection
  3. If you want to change the database, then go to the database selection field in the tool bar and select another database.

Multiple Query Windows

  • You can open multiple query editor windows at the same time. Just press the New Query icon in the tool bar again to open another editor window.
    SSMS Multiple Query Editors

New Horizontal Tab Group & New Vertical Tab Group

  1. To see two query editor windows at the same time, use the Horizontal Tab Group or the Vertical Tab Group option.
    SSMS Query Editor Tab Groups
  2. For creating a horizontal tab group, go to the Windows menu and select New Horizontal Tab Group option. Now the two query windows are visible one below the other.
    SSMS Query Editor Horizontal Tab Group
  3. For creating a vertical tab group, go to the Windows menu and select New Vertical Tab Group option. Now the editor windows are visible side by side.
    SSMS Query Editor Vertical Tab Group

Docking And Undocking Query Editor Window

  1. For undocking editor window from the documents panel, go to the title tab of the editor window and double-click on it. The double-click action undocks the editor window.
    SSMS Query Editor Undocked
  2. You can make the undocked editor window full screen by double clicking the title bar again.
  3. To dock the editor window back to its original position, hold the title bar and drag it to the center of the screen, A docking icon appears at the center of the screen. Drag the mouse to the dock icon and leave it. The editor window will be docked to the documents panel.
    SSMS Query Editor Docking

Executing Query

  1. For executing a query, write a query in the query editor window. For example, you can write a select query to fetch the records from a table.
  2. Then press the Execute icon in the task bar.
    SSMS Query Execution
  3. The queries in the query editor window will be executed and the results are displayed in the result window below the query editor.

Adding Indentation

  1. For  indenting the lines in the query, select the lines of the query to be intended and press the Increase Indent icon in the tool bar.
    SSMS Query Editor Increase Indent
  2. To remove or decrease the indent, select the line again and press Decrease Indent icon. The decrease indent icon will be next to the increase indent icon.
    SSMS Query Editor Decrease Indent

Using Comments

  1. For commenting a line in the query, select the line and press the Comment icon in toolbar.
    SSMS Query Editor Comment
  2. For removing the comment, select the commented line and press the Uncomment icon.
    SSMS Query Editor UnComment

View Records In Table Without Writing Query

  • To see the records in the table without writing a select query, right-click the table name in the object explorer and choose Select Top 1000 Rows. This option will open a new query editor window and generate a SQL select query to fetch the top 1000 records and the result of the query in the result window below.
    SSMS Query Editor List Table Records

Scripting Tables

  1. To generate a table creation script from an existing table, go to the object explorer, expand the database, find the table, select it and right-click. From the right-click menu, select Script Table as option and then CREATE To and New Query Editor Window. This will generate a table creation script in a query editor window.
    SSMS Script Table Create
    SSMS Script Table Create Result
  2. To generate a drop table script; From the right-click menu select Script Table as option and then DROP To and New Query Editor Window. This will generate a drop table script in a query editor window.
    SSMS Script Table Drop
  3. Similarly here is another option to generate a script with both DROP and CREATE table query. For that, you have to use the DROP And CREATE To Option.
    SSMS Script Table Drop And Create
    SSMS Script Table Drop And Create Result

Scripting Other Database Objects

  • Just like scripting a table, you can also script stored procedure, view, etc. You can generate create script , alter scripts, drop script, etc. for Stored procedure, view and other objects.
    SSMS Script Stored Procedure

Design View Window

The design view window helps you to see the table design like the  column details and edit them or add new columns, keys, etc..

Table In Design View

  1. To see the table in design view, go to Object Explorer, select the table name, right-click and select Design.
  2. The design view window will open in the documents panel.
  3. In the design view, you can alter the column properties, keys and other details. You can even add columns to the table.
    SSMS Table Design View

Registered Servers

Another important window in SQL Server Management Studio is the Registered Servers Window. This window helps to register SQL Server details and credentials. There are several advantages in registering a server.

SSMS Registered Servers

Advantages Of Registered Servers

  • Registering the SQL Servers will store the connection information in SSMS.
  • It helps to find if a registered server is running.
  • You can easily connect Object Explorer and Query Editor to a registered server.
  • You can create multiple groups of servers.
  • Provide user-friendly names for registered servers.
  • You can Export & Import registered server groups to another SSMS client.

Registering A SQL Server

  1. To launch the registered servers window, go to View menu and select Registered Servers.
    SSMS Registered Servers Open
  2. The window will open in the left panel above the object explorer.
  3. To register a SQL Server, Right click Local Server Groups folder and select New Server Registration.
    SSMS Registered Servers Registration
  4. In the New Server Registration window, enter the server name and instance name and enter the login credentials and press Save button to register. Before saving , you can test the connection details by pressing the Test button.
    SSMS Registered Servers New Registration
  5. To remove the server from the registered servers list, right-click the server and choose Delete.
    SSMS Registered Servers Delete

Changing Windows Layout

In SQL Server Management Studio, you can change the layout of the windows for your convenience, by closing a window or setting the window to auto hide or by re-sizing the window.

  1. To open the closed window, go to View menu and select the window you want to open.
  2. To make the window auto hide, just press the Pin icon at the top right corner just before the close icon.
    SSMS Layout Auto Hide
  3. To view the auto hide window just move the cursor over the tab with the window name.
    SSMS Layout View Auto Hide
  4. To undock a window, just drag the title bar of the window.
  5. To dock again, drag the window (by holding the title bar) to the center of the screen, the docking option box will appear. You can choose the docking position from the docking box.

Setting Startup Options

You can choose which window to open during start-up.

  1. To set this startup option go to Tools menu >> Options >> Environment >> Startup.
  2. From the startup field you can choose whether to open Object Explorer alone or Query Window alone or together or even empty.
    SSMS Startup Options

Restoring The Default SQL Server Management Studio Layout

  • To restore windows to their original locations, go to the Window menu, and select Reset Window Layout.
    SSMS Reset Windows Layout

Templates

Templates are the files containing SQL Scripts. These template scripts can help you to create objects in a database. Using templates you can create objects like database, tables, stored procedures, views, indexes, triggers, logins, users, functions, etc.. All the templates are listed in the Template Explorer window.

  1. To open the template explorer, go to View menu and select Template Explorer.
    SSMS Opening Template Explorer
  2. The Template Explorer window will open at the right side of the documents window.
  3. From the template explorer window you can choose the template of the SQL Script you want by double clicking the template. The template will open in a new query editor window. SSMS Templates
  4. You can replace the template parameters with your value and use the template for creating your scripts.

Projects And Solutions

To save the query file to a project, you have to create a project under the solution. For this purpose you have to use the solution explorer.

  1. To open a solution explorer go to View menu and select Solution Explorer.
    SSMS Solution Explorer Launch
  2. The Solution Explorer window will open at the right side of the documents panel.
    SSMS Solution Explorer
  3. In the Solution Explorer, you can see the auto generated solution called Solution 1. The current query editor window is represented as a file under the Miscellaneous Files folder. The name of the file is auto generated. There is no project created in the solution yet.
    SSMS Solution Explorer Files
  4. Before creating a project in the solution, save the current query file.
  5. To save the file, press the Save icon at the tool bar. A file dialog will open and allow you to save the file.
  6. After saving the file, create a project.
  7. Creating Project / Solution:
    • To create a project, right-click the solution in the solution explorer. In the right-click menu, select Add and then New Project….
      SSMS New Project
    • In the Add New Project window, select SQL Server Scripts project type, and enter the name of the project. I’m naming it as “TestScripts”. If needed you can change the location as well. Finally press Ok.
      SSMS New Project Creation
    • The new project is now listed in the solution explorer.
    • To move the existing file to the project folder, just drag and drop the query file. Dragging and dropping will create a copy of the query file under the project folder. You can remove the original file now.
      SSMS Solution And project
  8. Saving Project / Solution:
    • To save the project and the solution, press the multi save icon at the task bar. The file dialog will open and allow you to save the solution. You can change the name of the solution now.
  9. Opening Project / Solution:
    • To open the solution again,  you can launch the SSMS and open the solution from the Recent Projects and Solutions from File menu, or from the file dialog through File menu.

Reference

 


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.