SQL Server Management Studio (SSMS) Basics

SQL Server Management Studio (SSMS) is one of the most important tools you’ll use with SQL Server. It provides a user-interface for common database tasks. Although not comprehensive, here are a few of the more common tasks:

• Create databases.
• Design tables and other database objects.
• Add, update, or delete data from databases.
• Backup and Restore databases.
• Run queries.

Install SQL Server Management Studio

If you have SQL Server installed, there is a good chance that you already have SSMS installed. The easiest way to tell is to look at the Start Menu. On the Start Menu, select All Programs, expand Microsoft SQL Server [YOURVERSION], and then look for SQL Server Management Studio. If you have it, skip down to the next section.

If you don’t have it, here are some quick points:

1. First, make sure you’ve installed SQL Server. It helps if you understand how to investigate what version(s) of SQL Server are installed on your machine.
2. If you do have SQL Server installed, but you still don’t see the SQL Server Management Studio link, then it is possible that you installed the database engine without the tools. This is easily solved by running the SQL Server setup again (for the same version) and adding features to your existing database instance. The feature should be called “Management Tools”.



Connect to your SQL Server Instance

Click “SQL Server Management Studio” on the start menu to launch it. This will bring up a connection dialog like this:

The Server name field allows you to specify your SQL Server instance. This is the instance that contains and manages your databases. If you don’t know what instances are on your machine, it’s easy to find out. For example, if you just installed the express edition, that instance is “.\SQLEXPRESS” by default. If it was another edition, you could have installed a different named instance in the form of “.\YOURNAMEDINSTANCE”. Or you could have installed the default instance, which is simply “.” or “YOURMACHINENAME” or “localhost”. Note that if your SQL Server is remote (and you have access), you just substitute the remote machine name. For example, if my machine name was MYREMOTEMACHINE, then the connection to SQLEXPRESS on that machine would be MYREMOTEMACHINE\SQLEXPRESS, and the connection to the default instance would just be MYREMOTEMACHINE.

Make sure the Server type is set to Database Engine. The Authentication setting controls the type of security you use to connect to SQL Server. By default SQL Server uses Windows Authentication and adds your user as a system administrator during installation. So that should work. But there will be other times where you may have to use SQL Server Authentication with a user name and password. Security will be discussed more in a future video.

Exploring the Object Explorer

Once you connect, you’ll see the Object Explorer appear in the left-hand pane. When you’re first getting started with SSMS, my best tip is to learn by exploration. And the best way to explore is to right-click on the various nodes in the tree. For example, if you right-click on Databases, you’ll immediately see the “New Database…” option. Even if you don’t understand all of the options, you won’t ruin anything by clicking on the tasks and looking at the various dialogs. I find that this type of exploration can give you a sense of the capabilities of a tool before you ever start formally learning through other sources.

One quick warning. If you’ve used the steps above to connect to a SQL Server database that you don’t own, then you do want to be more careful with where you click. That’s why it’s good to learn on a local installation of SQL Server first.

Here is a very simplified list of tasks you can try from here:

1. Create a database:
   1. In the Object Explorer, right-click on “Databases”.
   2. Select “New Database..”.
   3. Give the Database a Name.
   4. Press OK.
2. Create a table:
   1. Expand your new database in Object Explorer.
   2. Right-click on “Tables”.
   3. Select “New Table…”.
   4. Specify the columns of the table.
   5. Close the design window and save the changes.
   6. Give the table a name in the “Choose Name” dialog, and press OK.
3. Add data to a table:
   1. Right-click on your new table.
   2. Select “Edit Top 200 Rows…”.
   3. Add rows of data to the table.
   4. Close the editing window.
4. Select data in a table:
   1. Right-click on your new table.
   2. Select “Select Top 1000 Rows…”.
   3. View the results.

Again, the attempt here is not to be thorough or detailed but to show there are a lot of easy ways to quickly get started with SSMS just by exploring the UI.

Learning More

Here are some other resources if you want to learn more about working with SQL Server Management Studio. If you come across a great resource for learning SSMS, please comment this post with it to help others.

• SQL Server Management Studio documentation on the MSDN Library.
• Tutorial on managing SQL Server 2005 with SSMS on Technet.