First Queries in SQL Server Management Studio

Alright, I’ve got everything installed, I have the AdventureWorks database, SQL Server and some time this afternoon, so how can I make all this work? SQL Server Management Studio (SSMS), that’s how. When I log in, I can pick a database out of the left-hand pane (under Databases/AdventureWorks/Tables) and start querying a table. But wait, once I navigate to the Table I want (Person.Contact, I want to see a list of all the names in the database) there is a greyed out window to the right. I have to find New Query in the top left of the Menu and now I have an editor where I can run queries. I think my team would laugh at me for using a “Select *” query, so I will use this instead:

select FirstName, LastName from Person.Contact

Two things that I immediately notice – it is case sensitive, and there is Intellisense! When I click Execute, the query runs and returns 19972 rows with the first and last names. Perfect, just as expected.

Now, I must admit I did cheat on you all – I have been doing some research and learning this week, and I found a great site (run by one of my MVPs, Brent Ozar) called SQLServerPedia. They have some pretty cool videos on how to do things, and I was able to watch a few of them very quickly at odd times this week, so I have a few ideas on what to do from a real expert. One of the things I learned was that you can highlight the query and right-click it for some different options. Using this method, you can send the results of the query to a file, display them in a grid, etc., and also Deign Query in Editor, which brings up that old familiar Query Designer I have seen for years. This is cool, because it will help you if you want to pull fields from multiple tables. In this case, if I want to add the VacationHours to my current results, I can do that “graphically” in the Designer and I don’t have to be a SQL Query Master to figure it out (my SQL is very limited these days).

If I pull that up, I get just the one table, and there’s no “Add” button and I can’t just drag it over from the right hand pane into this window, which is not terribly intuitive, but if you go to whitespace on the Designer you can right-click (I’m sensing a theme here) and select Add Table, which will give you a list of all the available tables. The cool part about doing this is that now I can see the SQL Statement that this will generate below, I can tell you I never would have thought of using an Inner Join to do this, but perhaps I now have something to blog about next week. The query is:

SELECT Person.Contact.FirstName, Person.Contact.LastName, HumanResources.Employee.VacationHours

FROM Person.Contact INNER JOIN

                      HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID

This only yields 290 rows, so not everyone here has vacation hours I am guessing. Pretty cool stuff, I feel smarter already, and I think I can actually do some pretty useful stuff with this, not only to get data , but also to re-teach myself about SQL Queries. More to come next week!