Sql Server Management Studio Intro
This page will teach you some of the fundamentals of using Sql Server Management Studio. It is my sincere desire that you develop a passion for Database development and grow from your experience.
Before we begin with the tutorial, I'd like to take a moment to review a book that I purchased a long time ago. It's called Beginning Microsoft SQL Server 2008 Programming. This is a must have book. I have used this so many times when I was working as a Software Contractor.
Some of the great features within. One thing is that it provides a great analysis of how SQL Server Management Studio functions. If you ever wanted to know what those "other" menus do then this is a great reference. Wrox provides an overview of how Views, Stored Procedures, Users and Roles, and what type of data types are supported. There is a large section for this breaking them down in tables.
If you have also little experience with writing your own SQL code then the author provides excellent reviews of all of the query commands you need to get started. Also because it is supported by Microsoft you get the in-depth review of their expression examples. So look no further if you want to write your own update, delete, insert statements because that can be found in Chapter 3.
Chapter 4 is called JOINs with good reason. Here you will learn how to create your own joins (inner, outer, and union). Chapter 5 begins showing you how to create your own tables. Chapter 6 is for Database Analysts that want to build their own constraints to ensure your data qualifies for certain data integrity rules.
Skipping around a little here in Chapter 10 you will learn to create your own views, which our similiar to stored queries. I used them when I was testing data before. Chapter 12 walks you through how to create your own Stored Procedures. These are essentially scripts that help your manage queries in various places in a separate file. Chapter 15 deals with triggers, which is a certain type of stored procedure that communicates with specific events. Chapter 16 discusses XML which allows you to incorporate XML elements into your projects.
Chapter 18 gives you control over administration procedures. Here you will learn how to schedules jobs, backup data, perform some defragmentation, manage alerts, Policy Based Management, and do some Archiving.
Finally there is a large Appendix that provides an excellent review of the system functions that are common with SQL Server Management Studio. So I hope this guides you on your decision of what is the best tool to use for learning this application. I for one have chosen not to depart from this book, even after newer publishings because there is a WEALTH of information contained inside. There are 682 pages and 19 chapters.
To check out more please click on the image of the book on the left to visit Amazon.
Sql Server Management Studio Setup
After you have installed Sql Server Management Studio (SSMS) you will be presented with the connection screen below. If you don't require any special authentication (password, etc) then click on Connect to start Sql Server Management Studio.
Sql Server 2008
For this example, we will be using Sql Server 2008 since it works with the AdventureWorks database (which you can download at Microsoft's site or search it through Google.
Sql Server Object Explorer
To become accustomed to Sql Server Management Studio you will want to utilize the Object Explorer to your advantage. Notice the categories for Databases, Security, Server Objects, Replication, and Management. Your version may have more or less. The Databases folder is used to add your own databases. Security is used for creation of special logins for other users (known as Roles). For simplicity we will focus on the Databases folder for this tutorial. For now notice the folder in Databases called AdventureWorksLT2008. You can access this file online and use it to practice writing queries.
Sql Server Adding a Database
To begin writing your own SQL queries (used to search a database) you will need to first create your own database or add one. For this example we are going to add the AdventureWorksLT2008 database.
Next right click on the Databases folder and select Attach from the pop up menu. You will be presented with the dialog box below. Then click on the Add button. You will see the dialog box below. It is important to be sure your database file appears in the folder subdirectory MSSQL10.SQLEXPRESS/MSSQL/DATA for this to work. Finally click on the AdventuresWorksLT2008 and select OK.
Sql Server Attach Database Setup
You will now see the dialog box and in the Databases to Attach window you will see the folder/file location and the AdventureWorks database appears below in the "database details" window. Finally click on OK and the database will be added to the Databases folder.
You may encounter an error when trying to add the database. You can use the Message link in the top dialog window called Databases to attach. Below is an example error you may receive. I suggest using Google to research this error message. This one is obvious since a database with the same name already exists.
Sql Server Attach Database New Query
After closing the window you will be returned to the first window for Sql Server. You will now start writing your first Sql query. Click on the New Query. You will be first presented with a blank window where you can write your first query to find information in the database. Before we begin however, it is necessary to understand to have a basic grasp on writing queries.
Sql Query Tutorial
So the first command we will learn is how to read all of the columns in a database. Keep in mind that data is stored in Tables which are made up of rows. Each row indicates a different field in the database. So for now just type in select * from SalesLT.Address. This will read all of the columns in the database table called SalesLT.Address. Then click on the Execute button in the top row. Note: You can also highlight the entire query and press F5 to perform the same thing. Notice that this query returned 450 rows. You can see this in the bottom right corner.
Sql Query Reduce Rows
Next type in select top 100 * from SalesLt.Address. This will select only the top 100 rows instead of every row. You can change the 100 to any number you like to narrow down the rows returned. This comes in handy, especially if you are trying to return a massive amount of rows since this can take time to load all the data.
Sql Query Joining Tables
Next you will learn how to join tables together to create data for a specific query. This is necessary to create reports based on a certain category. To join tables it is necessary to join a table by it's primary key connected to the foreign key. Keep in mind for this join to be successful and avoid return bad data (known as NULL fields) you will need to be certain you have a grasp on these. I can't stress it enough to really understand this. Otherwise you will spend wasted hours trying to comprehend why your data doesn't look accurate.
Sql Query Understanding Inner Joins
There are a few things to note about this new query. The query shows as select * from SalesLT.Address ad inner join SalesLT.CustomerAddress ca on ca.AddressID = ad.AddressID
The lettering ad next to the Select statement is called an alias. This cuts down on additional typing when joining tables. You can type in inner join or even join followed by the table name that joins first. Next you will join the primary key by the foreign key. This section also uses an alias named ca (short for Customer Address). You can use any alias name you prefer. In this example an AddressID exits in both the Address and the CustomerAddress tables. Also notice that the query has now only returned 417 rows.
Sql Query Narrow rows
In this next example you will learn how to display only certain columns. Type in the query select AddressLine1, City, StateProvince, PostalCode from SalesLT.Address ad inner join SalesLT.CustomerAddress ca on ca.AddressID = ad.AddressID
This has creates a more generalized search for your query results since you now only see the rows AddressLine1, City, StateProvince, and PostalCode which are the exact fields you replaced the asterisk with.
Next you going to learn how to rename the columns type in this query select AddressLine1 as "Street", City, StateProvince as "State", PostalCode as "Zip" from SalesLT.Address ad inner join SalesLT.CustomerAddress ca on ca.AddressID = ad.AddressID
Executing the query this time will show you the newly named rows for "Street", "State", and "Zip". Notice that City wasn't necessary to change since it is obvious what that row is used for.
Sql Query Sort Rows
By adding a statement called Order By you will be able to organize the rows to your leisure. Type in the query select AddressLine1 as "Street", City, StateProvince as "State", PostalCode as "Zip" from SalesLT.Address ad inner join SalesLT.CustomerAddress ca on ca.AddressID = ad.AddressID order by State
The only variation in this new query is the order by statement. In this example everything is in alphabetical order by State since that was that column that was typed after order by. Please note you can order by any column you desire to obtain a more particular result set.