Join Martin Guidry for an in-depth discussion in this video Basics of SQL Server Management Studio, part of SQL Server 2014 Essential Training.
- The next tool I'd like to look at is SQL Sever Management Studio. So off the Start screen, I'm gonna search for SQL Manager and that will bring up SQL Server 2014 Management Studio. I'll click on that. Management Studio is the most important tool in administering SQL Server, the one I use most commonly. I'd estimate maybe 90% of the tasks I perform are done through Management Studio. First it's going to ask me to connect to a specific instance of SQL Server.
The top dropdown is asking for a server type. Our choices are Database Engine, Analysis Services, Reporting Services, or Integration Services. In this case I'm looking for a Database Engine. Then it's asking me for the name of the specific instance I'm looking for. For me it's already been filled in. It's the name of my server/the name of the instance. If yours isn't filled in, you can type it in or if you go to the dropdown, the option on the bottom should be Browse for more.
In the Browse for Servers dialog, we'll first see the Local Servers tab where I can open up Database Engine and I see the name of my server. If I had multiple instances running locally I would see a list of all of them. This functionality is dependent on the SQL Browser Service, so your browser service will have to be installed and running in order for this to come up. We also have a tab for Network Servers. So if there were other SQL Servers on this same network, I could connect to them remotely.
My network doesn't have any so I won't see them. Again, this is dependent on the browser service being run on the remote machine. And it did find one but that's actually the local one, not a remote machine. So I want Local Server, and I want, again, the one that's the name of my machine/the name of the instance. I'll hit OK. Then it's asking me for authentication. If I go with Windows Authentication, I don't need to provide any additional information.
It will use the user name that I am logged into the server. It will not prompt me for a password, as long as I use the proper password to log in to the server. That information is cached and I don't have to type in the password again. If you prefer to use SQL Server Authentication, you can select that, and then you'll need to provide a valid login name and a valid password. But for now I'm gonna stick with Windows Authentication, which is what I typically do, and I'll hit Connect.
Now one thing you may notice, that for me in Management Studio my font is probably a little bigger than the font you have. So I'm gonna show you how to change these fonts to whatever you like. Off the Tools menu, the bottom choice is Options, I'll click on that. In the Options dialog I'll go to Environment, open that up, and about halfway down is Fonts and Colors. So in there I can change the fonts for a wide variety of things.
The top dropdown box allows me to select what I'm changing. So it could be the Text Editor, the printer, various other windows that I would like to change. We'll stick with Text Editor for now. I can change the font face, I can also change the size, the color of the text, and the color of the background, and I can also do this individually for different types of text. So it starts off with me editing the plain text.
I could scroll down here and also change the font for the error messages, change the font for a read-only situation, change the font for a task list, etc, etc. So I'll go back up and stick with plain text. Usually the only thing I change is the font size. I like to increase it slightly. I find 12 or 14 is a good number, but feel free to change this however you like. I know a lot of developers prefer a dark-colored background and light-colored text, rather than the default of light-colored text and a dark background.
While we're in this Options dialog box, let me show you one other thing that I like to change. In the Text Editor section, I like to go to All Languages and turn on line numbers. I just find it's easier to read if I can see the individual line numbers. So there's a little checkbox where you can turn on or turn off line numbers. It doesn't affect functionality, it's just a personal preference, I like to have it on. Once you've set those options to your liking, go ahead and click OK.
And now let's talk about some of these windows. Right now I have one window for Object Explorer on the left, and on the right I have a window for Properties. While dragging, if I move my cursor over one of those icons, it will dock that window in the appropriate place. So I can dock it to the bottom, top, left, or right. The default for Object Explorer is to be docked on the left. Most people tend to keep it that way. You'll also notice at the top of this window, a little pushpin.
When that pushpin is down the window will stay where it is. If you click on it once, it will move the pushpin, and now that window will autohide. So my Object Explorer has shrunk. It's budded up against the left side of the workspace. If I click on it again, I see it again, but if I click out of it, it will be hidden again. I usually like Object Explorer to stay where it is, so I'm gonna click on that pushpin once more, and now it will be pinned in place and it won't move around and autohide like that.
Using the X in the upper-right corner of this menu will close it, and now if I decide I need it again, off of the View menu I can go back to Object Explorer, and there it is, we have Object Explorer open again. There's other windows available to us off the View menu. We're gonna work with most of these in the course. For now we're working with Object Explorer and the Properties window, which is near the bottom.
I have that one open on the right-hand side. When we want to issue commands to SQL Server, we typically do that in a query. In this section, we saw the basics of connecting with Management Studio, changing the font to match our personal preference, and working with some of the windows. In the next section, we'll look at performing some administrative tasks with SQL Server Management Studio.
- Comparing SQL Server editions
- Installing SQL Server 2014
- Creating a database
- Creating tables and relationships between tables
- Writing SQL statements
- Creating views
- Using stored procedures and functions
- Working with XML
- Backing up and restoring a SQL Server database
- Monitoring SQL Server
- Authenticating users
- Importing and exporting data
- Indexing a database