In this video, you'll learn all about some of the major features of SSMS,
or SQL Server Management Studio.
I'll assume you already have SSMS installed. If you don't, you can download it from the
Microsoft website. I'll have a separate video on how to download and install it.
You'll also need access to an SQL Server database. You can download SQL
Server Express Edition, or another edition, from the Microsoft website to use at home.
Or you can access a database you have at work. I'll have separate videos on this as well.
You can use the timestamps in the video description to see
what's included and to skip ahead to certain sections if you like.
Let's get into the demonstration.
Once you've got SSMS installed, open it. The first screen you'll see is a Connect to Database screen.
This is where you specify the details to connect to a database.
If you're running SQL Server Express on your computer, like I am, you may find that the
connection details are automatically set. If not, you can enter them here.
If you're accessing a database at work, you'll have to find the connection details,
such as the server name and username, and enter them here.
Once you enter the connection details, then click Connect.
You'll then be taken to the main SSMS window. This is what SQL Server Management Studio looks like.
There's a series of toolbars at the top of
the screen. We'll look into some of those buttons later.
On the left, we have the Object Explorer. This is pretty standard in SQL IDEs and shows a list
of objects on the server. You can expand each section to show different types of objects.
The main area you'll probably be working in is Databases.
Click on the plus to expand Databases, then System Databases. Under System Databases you'll see a few
databases that come with SQL Server. If you've already used SQL Server for a project at home,
or if this is on a server at work, you'll probably see more databases here.
A little later in the video we'll see how to create tables using SQL,
and we'll return to the Object Explorer to see the tables.
To get started with writing SQL we need to have a place to write the SQL.
In SSMS this is called a Query Window. This shows in the main area of SSMS,
but when we start the application we need to create a new window.
To do this, click on the New Query button on the toolbar, or press Ctrl + N.
A new editor window or tab opens here. The title of the tab is called SQLQuery then a number. It
also shows the connection name, which you can see if you hover the mouse over it.
It's also shown in the title bar of SSMS or Management Studio.
In the query window, we can enter some SQL.
We'll start with a simple Create Table statement, so we can see the table in the object explorer.
Here's a query to create a table called demo_table. Once it's written,
it's time to run it.
To run a query from the query window, click on the Execute button on the toolbar.
This is the one with the green triangle next to it.
Alternatively, you can press F5. This may be quicker once
you get used to working with Management Studio.
Once you run the query, the Messages panel will appear at the bottom of the screen.
It should say "command completed successfully".
We've just run our first query in Management Studio!
Let's view our new table in the object explorer.
We can expand the Databases then System Databases if it's not already expanded.
We see a range of databases that already exist.
How do we know which database the table was created in? Just above the Object Explorer,
on the toolbar, is a dropdown field. This is the database that the SQL query runs on,
and was the database that you ran the Create Table statement on.
In this example, it says master, so the table is created in the master database.
Expand the Master database, then Tables, then System Tables. You might see the table here,
but you probably won't. This is because we need to refresh the object explorer after creating a
table. To do this, click on the Refresh button at the top of the object explorer. Or, you can right
click on the Tables entry and select Refresh to just refresh the items under that Tables entry.
Once you do this, you should see the new table appear.
We have an entry here, under Tables and not System Tables, called demo_table. It
has a prefix of dbo as that's the default prefix for new tables.
Our table is now showing in Object Explorer.
You can expand the table to see more information about it,
such as the columns, keys, constraints, triggers, indexes, and statistics.
If you have multiple SQL statements in your query editor,
like these Insert statements here, you can run them all at once or individually.
To run a statement individually, highlight it and click Execute.
The Messages panel should say "1 row affected".
To run multiple statements, select the statements you want to run and click Execute.
The Messages panel should have two outputs that say "1 row affected".
You can run a Select statement on the table to see what's inside it. This
can be done by writing Select id from demo_table, highlighting the statement, and clicking Execute.
We can see the output of the query at the bottom of the screen in a panel called Results.
We see there is one column and three rows.
Now, you may have noticed while typing the SQL statement that some popups appeared. This is
a feature called intellisense, and is common in many IDEs. Let's have a closer look at it.
After you type a keyword such as SELECT, then a letter, then a list appears that shows what looks
like functions in SQL Server. We don't want those, so we can just type "id" for our column name.
Then, we enter FROM. We can start entering our table name here,
by typing in the start of demo_table. It will appear in the list, and you can
use the up and down arrows to select it and press Enter to add it to the query.
If it doesn't appear, you can press Ctrl + Space to trigger the intellisense,
which makes a popup appear. For example,
after entering FROM, you can press Ctrl + Space and a list of all available items appears.
Intellisense is a very handy feature that helps you write SQL code quicker.
SSMS highlights syntax errors in your query with a red underline. This happens if you
mistype a keyword, but it can also happen if you refer to a new table or a column in a new table.
Let's see an example of this.
We can create a new table called demo_error with a single column.
Once we run this statement, the table is created. Now, let's select from it.
If we select from the table, we may see the table name has a red underline
under it. It doesn't happen now, which I assume is because it's a recent version of Management Studio
and it may have detected that the create statement above will create the table.
But if you're running it in a fresh window or have an older version of SSMS, you may see the
red underline under a table, saying it does not exist, even though you just created it.
This happens because the intellisense feature uses a cache,
and the cache has not been updated to include the new table.
This is easily fixed though. To refresh the intellisense cache,
go to Edit, Intellisense, then Refresh Local Cache. Or you can press Ctrl + Shift + R.
The cache is refreshed and any red lines under new tables should disappear.
We've done all of our work so far in a single query window. Management Studio allows for
multiple query windows to be created. This can be helpful if you want to have separate
areas for different queries you're running or different areas of the database you're working on.
To create a new tab or a new query editor,
click the same New Query button we used earlier in the video.
A new tab appears at the top of the screen,
and a blank white window opens below it for you to enter your SQL.
You can switch back to your old tab by clicking on the tab name at the top.
You can add many query windows to Management Studio.
If you have a lot and want to switch between them, you can press Ctrl + Tab to cycle through them.
Or, you can go to the Window menu and select the tab you want to show at the bottom of the list.
Or you can click on the Window menu and then Windows at the bottom, which opens a new
dialog box with a list of all of your tabs. Select a tab and click Activate to show it.
If you're wondering if there's an easy way to show a better name for a tab,
we'll get to that shortly.
But first, here's another way you can open a new window for your SQL.
There may be times where you want to see an SQL script for working with a table,
such as a Create Table statement to create a similar table.
Management Studio allows you to do this by right clicking on an object in the Object Explorer,
then selecting "Script table as". You then have a range of statements that can be generated.
You can generate a Create Table, Alter, Drop, Drop and Create, Select, Insert,
Update, or Delete statement. If you click on or hover over any of these options,
you can generate the statement in a new query window, a new file, or to the clipboard.
Select New Query Editor Window, and a new tab is opened with an SQL script that does what you
selected. It's probably not the same script you used to create it, but it has the same effect.
This is handy for writing Select statements for tables with a lot of columns,
and for Insert statements. It's a great timesaving feature.
You can save your SQL editor windows as SQL files,
which lets you keep the queries you have written, and give a more meaningful name to your tabs.
To do this, click the Save button on the toolbar here, or go to File then Save, or press Ctrl + S.
You can then browse to a location to save your file, enter a filename, and click Save.
The filename is shown at the top of your tab. You can also see it
if you open the Windows list from the Window menu.
If you have an SQL file you've saved previously and want to open it, you can do this by going to
File, Open, then File. Or you can press Ctrl + O or click the Open icon on the toolbar.
Browse to the location of the file, select it, and click Open.
It's then opened and displayed on your screen.
Management Studio has a handy feature where you can dock windows,
or show two different tabs side by side.
To do this, right click on the tab name and select New Vertical Tab Group. You'll then
see the query docked to the side of the screen, with the original tabs remaining where they are.
This allows you to view two files at once.
You can click and drag on tab headers to drag them between the two groups.
Drag the tab until it appears as a dark blue shape, then release. The tab will then be moved.
If you want to return to a single group, right click on the tab and select Move to
Previous Tab Group. You'll then have a single group of tabs.
You can also right click on a tab and select New Horizontal Tab Group
if you would prefer to have the queries on top of each other instead of side by side.
By default, Management Studio will display the results of a Select query to a grid.
You can change this to show the results in a text format,
which is helpful if you want to display them in a blog post or an email or something.
You can also save them to a file if you want to store the results or send them somewhere.
To change how they are displayed, you can right click on the query window
and select Results To, then either Text, Grid, or File.
Or, you can click on any of these three buttons on the toolbar here.
The current method is highlighted, which should be Grid, but you can change it.
We can change it to text. We can then run a Select query and see the output here.
The rows are on separate lines and the table header is separated by dashes.
We can change it to File, and when we run the query we are prompted to enter
a filename and location. Enter a filename and select Save. It's saved as an RPT file,
which can be opened in a text editor or a browser such as Chrome, and you can see the output.
If you want to copy the results of an SQL query to Excel, you can do that pretty easily.
First, go back to Grid output mode as we just demonstrated. Run the query.
In the results, right click and click Select All.
Then, right click and select Copy with Headers if you want to include the column headers,
or Copy if you don't. I'll select Copy with Headers.
Open Excel, and paste into the worksheet. Your data is then added to the spreadsheet.
And that brings us to the end of this tutorial video on SQL Server Management Studio.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the ability
to script an SQL statement for a table from the object explorer,
or the ability to dock windows to a side to see two queries at once, or something else?
Thanks for watching.