Exploring SSMS

I’ll cover top menu, briefly introduce Registered Servers window and mention the most frequent sections of Object Explorer.

Top menu

Now the most important (and underrated) menu is the Quick Launch in the top right corner. From there you can just start typing what you are after.

Going left to right, we have:

File

Here you’ll find the typical New, Open, Save and Exit options. Nothing too exciting here.

File menu

Edit

Typical text editing and navigation options found in most editors. I recommend to use the hotkeys as much as possible and then this menu will become obsolete.

There is one interesting option:

  • Cycle Clipboard Ring
    • By pressing Ctrl + Shift + V you can access text previously saved to clipboard.
Edit menu

View

From this menu you can open the toolbar type of windows and edit toolbars as well. These are the main tools you will use:

  • Object Explorer
  • Registered Servers
  • Properties Window

But since you will open them once and keep them persisted (or learn the hotkeys). This is another menu you won’t frequent.

View menu

Query

This option only becomes available when you have a New Query window opened.

As the name says – here you’ll find all query related commands.

But since the same commands are on the default toolbar just below the menu. We will ignore this menu most of the time as well.


Query menu
Query toolbar

Project and Debug

I’ll cover these two menus at once because I never use them. Since Project menu has no items (at least with no project opened). We’ll go straight to Debug menu then.

Debug has options start Debug, breakpoint and stepping through the code. But you never want to use this because when you start debugging, everyone else is blocked and waits for you and that is a huge problem. So never do it, there are better options how to debug.

Options

A useful menu after a while, I’ll cover few options:

SQL Server Profiler – this is one of the debugging tools I have been talking about, but it is almost legacy now – instead we will cover Extended Events in a future article.

Options menu

Database Engine Tuning Advisor – to be fair I’ve never used it. I only had to clean up after it.

Code Snippets Manager – this tool is somehow lacking and that’s why there are plenty of commercial tools that make work with Snippets and ItelliSense much easier.

Import and Export Settings… – in the next article I’ll describe my recommended settings and this option will make it a one time operation even when you switch workstations.

Options – Everything else is in there. Even though I rather use the Quick Launch menu.

Window

You can find some tab navigation here. But the most important option for me is the New Vertical Tab Group.

When I want to compare some code side by side or run code from separate sessions (for example blocking and deadlocking tests) this is my go to option.

Help

Not much to say here. Typical help section with About section, where you can find your current version of SSMS.


Registered Servers

If you are working in multi-server environment, this is where you’ll save shortcuts to your saved server.

In a future post, I’ll cover how to set a default database after connect and recommend a colour scheme so you won’t run DEV code on production by accident.


Object Explorer

Here you will find all server related info in a structured format. Sometimes to get to the settings you must right-click and select properties.

Server object has the following structure, I’ve omitted some settings that aren’t that common, so you’ll have to do some exploring as well. Also, some folders can be expanded only to be empty, so don’t get fooled by that.

  • Databases
    • Tables
      • Columns
      • Keys
      • Constraints
      • Triggers
      • Indexes
      • Statistics
    • Views
      • Columns
    • Synonyms
    • Programmability – this is where you will spend most of the time in the begining
      • Stored Procedures
      • Functions
        • Table-valued Functions
        • Scalar-valued Functions
      • Database Triggers
      • Assemblies
      • Types
      • Rules
      • Defaults
      • Plan Guides
      • Sequences
    • Query Store – must be enabled in a database settings
    • Service Broker
    • Storage
    • Security
      • Users
      • Roles

Lots of these options won’t be used so much after we learn some DMVs.


Looking forward

This post only mentioned the options and not much else. I will describe individual option when I get to cover them properly.

Next time we’ll start writing some very basic SQL code and after that I’ll throw in some pro tips for productivity, writting style and SSMS settings.

Database playground

Preparations

To get our own SQL up and running we are gonna need few things:

  1. SQL Server
  2. SQL Management studio (SSMS)
  3. Database filled with data

If you’re not preparing for a job in SQL and just want to try some SQL code, then perhaps this might be enough.

 

https://data.stackexchange.com/stackoverflow/query/new

 

It’s a web portal where you can write queries directly agains the sample Stack Overflow database.

 

Everyone else, read on.

 

 

1. SQL Server

This is the server we will connect to. Normally companies have it on separate machine, but we will install it locally.

 

We have a choice between Express and Developer edition. Both are free and since Developer edition has a bit more functionality, we’re gonna go with that.

 

Download link https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Installation

For speed we’re gonna go with the Basics installation. You can always change the details later.

1/3 Select the basic settings
1/3 Select the basic settings
2/3 Choose your instal location
2/3 Choose your instal location
3/3 Installation complete!
3/3 Installation complete!

2. SQL Management Studio (SSMS)

With this program we will connect to our newly installed SQL server. This is the tool you will use the most when writing SQL code. Always download and use the latest stable version, it doesn’t matter which version of SQL Server you’re using.

 

Download link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Installation

This installation is straightforward – no screenshots needed.

3. Database

We need some data to run our queries on.

Lucky for us, Microsoft provides us with that as well. We are gonna download Adventure Works database.

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

 

I’d recommend AdventureWorks2016_EXT.bak. You can choose any version, that is same or lower year than that of your SQL Server (ours is 2017).

Also be careful not to download the files that containt DW in their name – that means DataWarehouse and it has data stored differently than we need to.

Installation

This will require us to fire up SQL Management Studio (SSMS) and connect to our SQL Server.

I will write out the text, but there is a screenshot guide below.

  1. Open SSMS studio
  2. Connect to our SQL Server
    1. If you followed basic install it will be the name of your machine
  3. Open Object Explorer
    1. It might be opened as default
    2. If not use hotkey F8
  4. Expand the server, right click on the Database folder
  5. Choose option “Restore Database“
  6. In the popup on the “General” page select radio button “Device”
    1. Click on the ellipses …
    2. Click on “Add“
    3. Navigate to folder where you downloaded AdventureWorks2016_EXT.bak
  7. Back in the popup you should see the table populated.
  8. Click OK and it’s done, we have our database.
1/5 Connect to your SQL Server
1/5 Connect to your SQL Server
2/5 In Object Explorer right click on the Database, select Restore Database
2/5 In Object Explorer right click on the Database, select Restore Database
3/5 Select Device and click the ellipses
3/5 Select Device and click the ellipses
4/5 Click Add and navigate to folder with AdventureWorks database
4/5 Click Add and navigate to folder with AdventureWorks database
5/5 You should see the table populated
5/5 You should see the table populated

Looking forward

Next time I’ll write about basic navigation around the SSMS with some useful tips and tricks.