In the last part of this series I wrote about SQL Server Management Studio (SSMS), the interface that enables you to work with SQL Server objects such as databases and tables. The program enables you to do quite a bit with a SQL Server installation. You can create entire databases and manipulate all the objects within them just through context menus.
Having a graphical interface is nice but the real work of SQL Server is done through commands issued to the service which the menu options in a program like SSMS often do for you. If you really want to be knowledgeable about SQL Server, it’s important to learn the syntax of these commands and how to write and issue them on your own.
The Database Language
SQL Server uses Structured Query Language (SQL) and Transact-SQL (T-SQL) to communicate with the database service. SQL is actually a subset of T-SQL and is not limited to Microsoft SQL Server. Implementations of the language are found in many popular database systems including MySQL and Microsoft Access although there may be subtle differences in syntax.
SQL includes commands to work with data at the table level. With SQL, you can read data from one or more tables in a variety of ways as well as insert, update and delete data. These commands are very close to real English and look something like this;
SELECT * FROM Customers DELETE FROM Books WHERE BookID = 9 UPDATE Employees SET Status = 'Former' WHERE EmployeeID = 10
SQL can also be used to create tables, indexes and other database objects and grant permissions on these objects to various users and groups. SQL operations are generally performed one statement at a time. T-SQL is an expansion of SQL which enables the programmer to create entire procedures that are run directly on the server and include an indefinite number of individual SQL statements or T-SQL commands. These stored procedures can include flow control statements such as IF … ELSE and WHILE which grant decision making abilities to the code. The procedures can respond to errors, undo or roll back changes when necessary and use input, output and internal variables during their operations. While SQL operates at the table level, T-SQL operates at the database level, enabling the creation of entire databases with all the objects and permissions necessary.
In addition to stored procedures, SQL and T-SQL are used in the writing of scripts which are used to make a series of changes within a database, often as part of software deployment. Scripts can include commands to create or alter stored procedures in addition to the other changes. These scripts can be be run within SQL Server Management Studio and other SQL Server tools. The advantage with SSMS is that it provides an easy way to edit the script or to run selected parts of it if necessary. I’ll write more about both SQL and T-SQL later but for now, I want to show you the basics of running a script in SSMS.
Downloading the AdventureWorks Database
In the last chapter, I showed you how to create a basic database through SSMS but you probably still don’t have any significant databases to play around with and learn from. Fortunately, Microsoft provides sample databases. One of these is the AdventureWorks database, a customer and product database based around a fictional company. On a desktop database like Microsoft Access, you would just download a database file and open it in the program. There is a way to do that in SQL Server but the Microsoft download I’ll be using here is instead packaged as a script with a collection of data files which provides us with a good opportunity to learn more about running scripts on SQL Server.
To download the database, go to the link below and download the AdventureWorks 2008 OLTP Script.ZIP file. It’s just over 20 MB so it might take a few minutes.
Download AdventureWorks Database (will open in separate window)
If, for any reason, that page is not available, check out the main page here and look for a download that is marked as an OLTP Script.
After you’ve downloaded the database, unzip all the files to a location on your computer. I recommend a directory directly under the C: root directory because you’ll need to type the address of the location later so it’s best to keep it simple.
(Click on image for full-size view.)
You’ll see that the file contains over 70 files, mostly CSV files which contain the data to be imported into the new database. Once you have Management Studio open and have connected to your instance of SQL Server Express, you can open the “instawdb.sql” script file from this directory using the File –> Open menu or Ctrl-O. The script will appear in the right side of the SSMS window.
This is a very large script of over 7700 lines and it’s written so that if there are any errors, which there might be, it can simply be run over again. This script will not make any changes outside of SQL Server and will not affect any other data on your computer. It simply creates a new database within your instance of SQL Server Express and all the necessary objects within that database. It then imports the data from the CSV files into the appropriate tables. While the database was created for SQL Server 2008, it will work in both the 2008 and 2012 versions.
(Article continues after ad)
Before you run the script, there are a couple of changes that you need to make for it to run properly. First, as instructed in the script, Management Studio needs to be running in SQLCMD mode which enables the program to run certain commands that it couldn’t otherwise. You can set this mode by selecting “SQLCMD mode” from the Query menu in Management Studio. When you’ve done this, you’ll see the “on error exit” line change so that it has a colored background as shown in the screenshot below.
The next thing to do is to make a couple of minor changes in the script itself. In the following instructions, I’ll be referring to line numbers. If you look at the very bottom of the SSMS window, you’ll see a status bar that shows line and column numbers on the right. You can use these numbers for reference as you scroll down to the necessary lines or you can use the Edit –> Go to… menu (Ctrl-G) to enter a line number to find in the script.
These line numbers should be treated as approximate. This script is subject to revisions and the one that you download might not exactly match the one that I used for this article.
Near the top of the script, around line 37, you’ll see two lines that start with “:setvar”. These lines set variables that the script will use to reference the correct directories. You need to uncomment these lines by removing the double dashes (“–“) in front of them. Code lines can be commented in scripts by using these double dashes at the beginning of the line or by using /* and */ at the beginning and end of a block of text.
Once you uncomment these lines, you’ll need to change the path statements to match the appropriate paths on your system. The first line shows the data directory for your instance of SQL Server. If you’re using anything but LocalDB, It’s probably under your Program Files directory and might look like this:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\
You can verify this path by right-clicking on the server instance name in SQL Server Management Studio, selecting Properties and then selecting the Database Settings page. The path will be shown at the bottom under the Database default locations section.
If you’re using LocalDB, then you need to create your own directory to hold the database files. This can be any directory on your system that SQL Server can access. For more information, please refer to Part V of this series which provides more information on LocalDB.
Verify the correct path by navigating to your own data directory and place that directory address in the statement as shown. Be sure to include the backslash at the end of the address because the script will be attaching file names to this path during its operation.
The second line tells the script where the script files are located on your system so that it can find the CSV files and import the data. Replace the existing path with the location that you used to unpack the files.
There are a couple of elements in the script that use SQL Server’s full text indexing feature. If you have not installed this feature, the script will fail when it gets to these points in the process. In that case, there are some lines in the script that you need to comment out so that it will run normally. You can do this by selecting the appropriate lines and using Ctrl-C to comment them or by manually placing two dashes at the beginning of each line.
- Comment out the CREATE FULLTEXT CATALOG and INDEX commands near lines 3502 through 3523.
- Comment out the uspSearchCandidateResumes stored procedure that demonstrates full text index searching. This procedure is located near lines 5880 through 5933.
If you are installing the database for use with SQL Server Express LocalDB, the other necessary change in the script is the SqlSamplesDatabasePath variable. Normally, this path would go under Program Files to the SQL Server Data directory. Because LocalDB buries this directory a few levels deep under your Users directory, it might be better to set it to another directory that you can find easier such as your Documents directory.
Once you make the necessary changes, you’re ready to run the script. One thing to remember about running script items in SSMS is that if you have any parts of the script selected, those are the only parts that will be run. In this case, you want to run the entire script. You can do this by selecting the entire script (Ctrl-A) or by selecting nothing at all. Then press F5 to run the script or click on the Execute toolbar icon. Then wait. This is a long script and it carries out a lot of operations, including importing all that data so it’s going to take a minute. The yellow bar at the bottom of the script window contains a timer that will show you how long it’s been running.
(Click on image for full-size view.)
The script contains statements that print out reports of its progress to the screen but the results screen shown in the bottom half of the screenshot above is not updated until the script stops for some reason. Any messages in red mean that errors occurred. You can actually double-click on these errors and the offending line will be shown. For any errors, refer back to my instructions above or search Google or MSDN with the text of the error and “AdventureWorks”. If you can’t find an answer, you can always e-mail me here and I’ll offer whatever advice I can. Once you fix any errors, you can re-run the script since it will overwrite its previous work.
If all goes well, you should see a results page reporting the statistics for the
(Click on image for full-size view.)
Exploring the Database
Once the database is installed correctly, there’s nothing left to do but start exploring it. The AdventureWorks database has almost 70 tables in it, many of which are related to each other in one or more ways. If you expand the Tables item under the database, you’ll see that most of the tables are named according to the department they fall under. I would also recommend keeping the database script as a reference that you can explore as you learn about T-SQL. One of the great things about the help system in SQL Server is that you can click on any of the keywords within the script and press F1 to bring up a help page which will often give you a full description and detail on that keyword.
Doing queries against the tables within the AdventureWorks database is fairly simple; right-click on the database itself and select New Query from the context menu. A blank query window will open where you can enter the query. I’d recommend starting with SELECT queries if you’re not very familiar with SQL Then press F5 to run your new query. Remember that if you have multiple queries within the window, you can select a specific query to run it separately from the rest. Click on the screenshot below for some sample queries that you can try out.
(Click to view full size image.)
If you are familiar with relational database principles, Microsoft also offers a diagram of the database which shows all the relationships between the tables. You can download the diagram on this page or go to Microsoft.com and search for “AdventureWorks Database Diagram”. There are two versions, one for HTML and one for Microsoft Visio. The Visio version is the one I would recommend and if you don’t have Visio, you can also download a free Visio viewer program from Microsoft.
If you don’t know about relational databases or data normalization yet, you can check out the following links which provide an explanation of the basic principles.
Description of the Data Normalization Basics – Microsoft.com
SQL Server Tutorials – Microsoft.com
Also check out the Drewslair.com articles on SQL basics that I referred to earlier which will give you enough SQL knowledge to get you started in querying the tables within the database.
(This article is updated from an article series previously published by Comeau Software Solutions.)