Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Let's take a look at SQL Server Management Studio. This will be our main toolbox for working with a SQL Server, and since I'm going to find it under my SQL Server 2008 R2 programs, I'm going to right-click and just pin this to my Start menu. It makes it easier to get to. Now this tool is not intended for the end user. It is an administration tool. It will help us create and change databases, diagram them, report on them. There is a lot of stuff it can do. Now there is no real point in opening SQL Server Management Studio without connecting to a SQL Server instance.
So that's the first thing you'll be asked to do. And again, you can connect this to different SQL Servers as long as you know where they are and what credentials to use to get access to them. I am connecting to the local machine here so I am just going to use a dot for the server name. I had installed it using Windows Authentication and I had named myself as the administrator of this instance. So once I connect, I'm in and I can do anything. Now the most obvious section and the most useful initially is this area over here on the left.
It's called the Object Explorer, but the name is not really important. This is the section that lets you drill down into different parts of the SQL Server instance you're connected to. Not just the databases and the data inside them but administrative stuff too. What are the users and the user identities that SQL Server knows about? What roles does the database understand? How for example would you say that a user called Bob should be allowed to edit the Orders database but not allowed to edit the HR database? That stuff is all defined in here.
Now when you're clicking around here, what you can actually do is see a little more information by selecting the Object Explorer Details pane from the View window and when I have say my databases selected, I don't just see the names of them but I can see their owner and their collation if I find that useful. But more than anything you will find that right-clicking is your friend in SQL Server Management Studio. You can select pretty much any of these levels. Right-click an object in them and you'll see a collection of context-sensitive options.
If you, for example, select a database like the AdventureWorks database, you can drill down into Tasks and see that you can take it offline or copy the database or import data or back it up or restore it. One of the common things you are likely to do with databases, you'll be curious of how much space they take up. So you'll find that right-clicking on the database allows you to go to Reports where you have a whole bunch of standard reports that says Disk Usage, how much disk usage is this database taking up.
Drill back down into Reports and I'll find things like Disk Usage by Top Tables, generating lots of really useful information for that. On the other hand, if you start to drill down inside the databases themselves and actually start to drill down into the tables, the things that hold your information, the right-click choices here will be different. You'll have things like select the top 1000 rows or even edit the top 200 rows. You can even right-click the Tables and say Design, allowing you to change the table and change what it actually means to be this data and we'll see all of this a little later on.
I do encourage you to explore around the Object Explorer for a few minutes if you haven't already. Now of course as with any advanced development environment, take care when you're doing this. You are working directly with the database instance and if you decide to tell SQL Server Management Studio to delete a database for example or change a table, it will say "Well, you're the boss" and it will do it. One example of the place to take care might be, for example, Database Diagrams.
If you start working with some of these sample databases you'll see a Database Diagrams section and this is very tempting to play around with. Typically the first time you would click them you may be prompted that the database doesn't have everything in place to use this. Do you want to create that? I'm going to say yes I do. Let me close the couple of reports I have opened. What that then allows me to do is make a new database diagram based on the actual defined pieces of that database. In this case, I'm doing it on the database called AdventureWorksLT and I see that I have a whole bunch of tables here and I can just start clicking Add to add them to my diagram.
Maybe I'll err on the side of just adding everything right now. If I close that, I see that I've got this rather large diagram going on here. In fact, up at the top of the screen I can shrink that down. Let's say it's 50%. It's still a little big so I can drag a few things around if I so desire. But what I'm trying to show is the point that this is really-really good stuff and the ability to diagram your database reading from the actual structure of it is a great feature but again, be careful.
It's very easy to say look at a diagram like this and realize that you don't need to show everything, you'll right- click one of these options and you'll see that there is the option Remove from Diagram, right next to Delete Tables from Database. And the end result of selecting these things would be very different. So again, take care. Some great features, but be careful with them. Of course, everything that we are actually doing with this SQL Server Management Studio interface is generating commands that talk to the SQL Server instance and if you are someone who lives and breathes command line operations, you can actually open up something called SQLCMD, just typing in SQL command, and that will open a command prompt to allowing to talk to the SQL Server instance.
I could do a couple simple things here. I am going to say use AdventureWorks... And all I'm doing is typing a couple of simple commands to shift the SQL Server Instance to look at the AdventureWorks database and then show me the top 10 first names from one of the tables.
Now I'm not intending to spend any significant time in the command line prompt here in this course. We're going to use the graphical interface you can always take what you get there and put it in the command line later. However, we still are going to write a fair amount of code ourselves. We are just going to do it in SQL Server Management Studio, which is a much more pleasant way to do that. It does also have some helpers for us. If I go to the View menu and select something called the Template Explorer, I am going to see this section appear over here on the right.
This is hundreds of pieces of boilerplate code we can use for the most common tasks in SQL Server. Everything from creating a database for example, which starts to script it out there, we just go and fill in the blanks, all the way to advanced database needs such as creating partition schemes, which can be a bit more complex. Now if the idea of writing a lot of this code fills you with dread, we will see that quite a bit of our creation can also be done using the graphical user interface, but you will always be required to kind of come back and write a few commands that will be executed by SQL Server Management Studio.
We will of course be using a lot more pieces of this application as and when we need them, but with these core ideas to get started we can move on and start exploring one more tool.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69969 Viewers
80 Video lessons · 127384 Viewers
52 Video lessons · 62342 Viewers
59 Video lessons · 48035 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.