SQL Server Reference Guide

We’re in a series that explains the main sample databases that you can use with SQL Server, and in this tutorial I’ll show you how to find, install and work with the first (and smallest) of the sample databases, pubs.

The pubs database has been around since the beginning of SQL Server. In fact, it was also available for the Sybase database product, which was the code-base for the original versions of SQL Server.

I always install the pubs database. It’s very small, easy to understand, and contains enough of the objects like tables, views and stored procedures that I need to do basic checks on. I normally back it up before I make any changes, run my tests, insert new objects, delete things and so on, and then restore it when I’m done.

 I use the pubs database (and all of the sample databases) not only to try out Transact-SQL (T-SQL) statements but also when I’m trying out a new feature or when I want to mess with the structure of my database, like adding or moving filegroups.

Finding and Installing the pubs Database

I’ve had pubs laying around on my systems for so long, it just gets updated from the last installation. But when I build a new system or I’m at a customer’s site, I need to get it again if they don’t have it installed.

If you have older media like SQL Server 7 lying around, you can get the  the  database from there, but I normally just go to the web to download it. Microsoft has moved most of its samples for SQL Server to “CodePlex” a web site you can find at the end of this tutorial. For pubs and Northwind, the location is slightly different, since they are older resources. You can find those here.

Once you get to the site, you’ll see the download button. On most systems you can run that, but on my laptop here at home I got a message stating that this package “couldn’t be opened”.  If, however, your installation goes fine, you’ll find the files it brings down in the C:\SQL Server 2000 Sample Databases directory created by the installer. In that directory you’ll find a file called instpubs.sql. Just open SQL Server Management Studio (SSMS) in SQL Server 2005 or higher, or the Query Analyzer (QA) in 2000 and lower, and run that file. It will install everything for you.

You can also install the pubs database by restoring it from a backup taken on another system. The code for that is quite simple. Sure, you can do this graphically as well by right-clicking the database name and then selecting the AllTasks…| Backup option from the menu, but the code is very simple to type or save. To back up the database to the TEMP directory, I simply type:

USE master;
   GO
   BACKUP DATABASE pubs TO DISK = ‘c:\temp\pubs.bak’
   WITH INIT;
   GO

I use the “WITH INIT” qualifier to overwrite the file if it is already there. You can have connections while the backup runs.

I play with the database, and if I make any changes I type this command to get it back:

USE master;
   GO
   RESTORE DATABASE pubs FROM DISK = ‘c:\temp\bak’
   WITH REPLACE;
   GO

The WITH REPLACE option overwrites the database that is there, and of course it’s important not to have any connections open to the database when you restore it.

If the file has been moved from another system where the drives don’t match the one I’m on now, I have to use the WITH MOVE option. Let’s assume that the system I was on previously had the database files (the mdf’s) running on the “f:” drive. I now want to move that file to the default for my system, so I’ll tell the system where to move the original files to now.

The key to understanding this process (and yes, even installing a database this way is a great way to learn) is that the system has a pair of values it uses to track the database. The first value is the logical name of the database — in this case, pubs. The second set of values is the name of the files associated with that database. Remember, you can have more than one set of files for a single database, using FileGroups. You can learn more about those here.

So let’s take a look at moving a backup from one system to another, where the drive letters are different. We’re dealing with two files in my case, pubs.mdf and pubs_log.ldf. I want to move those to the default directory for my installation, which is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

USE master;
   GO
   RESTORE DATABASE pubs FROM DISK = ‘c:\temp\bak’
   WITH REPLACE
   , MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf'
   , MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_log.ldf';
   GO

Another interesting method of installing a database is to attach it. Because SQL Server uses these name-value pairs, you can run the sp_detach_db stored procedure, which tells the master database to remove the entry for the database from its records. At that moment the database “goes away” — but the files are still where they have always been. You can then copy the files wherever you like and use the sp_attach_db stored procedure to tell the new system where the files are and what the database name is. The system will then just “adopt” the new files and tie them out to the name, recording that in the master database. This method is very simple and fast, but you need to remember that you have to detach the database before you attach it somewhere else. The pubs database is a great one to use for experimenting with this feature. You can detach it from the same system you attach it to. Note that in later versions of SQL Server, these stored procedures are deprecated, so you’ll want to read up on the CREATE DATABASE with the FOR ATTACH option, again in Books Online.

Whether you’ve installed the database using an installer, an upgrade, a restore or an attach operation, you need to check a few options for the database once you’re done. Although the system will use the database just fine after any of these operations, it’s important to set things up so that they will behave the way you expect. The best way to do this is to right-click the database name and then to select Properties from the menu that appears. The main options I think about here are the Recovery Model and the Compatibility Level. The Recovery Model sets how the database uses the transaction log, and how the backups and recoveries are affected. You can read more about that here. Normally I leave my test databases in the Simple Recovery Model, which takes less space and maintenance, unless that’s exactly what I’m testing. It’s a simple matter to change it from one model to another, so the Simple Model is often a good choice for what I’m working on.

The next option is trickier. In SQL Server there is an option on a database called the Compatibility Level. You can change that option to the currently installed version of SQL Server or a few levels lower. This option affects certain behaviors, such as identifier names and so on. Depending on what I’m testing, I change this level to behave the way I need it to. For instance, perhaps I’m testing some code I have in my current system against a new version of SQL Server. In that case I might leave the level at the version I’m on now to find out if I have any major issues in the code. Or perhaps I’m going to upgrade the database to a newer version but keep the code the same. In this case I change the Compatibility Level to the newer version and run my code against it.

Now that the database installed, I change the options to what I want and immediately run that backup I mentioned earlier. Now I’m ready for learning, experimenting and testing.

Exploring the Meta-Data

With the pubs database installed, I want to explore it a little. What I’m really after are things like the names and relationships of the tables, the structure of the views, and any stored procedures the database has. I then dig a little deeper and see the data types and other internals.

There are a few tools that we have available to discover more about the database. This is apart, of course, from just opening the graphical tools and wandering around through the objects, which isn’t always a bad idea anyway. I sometimes start with database diagrams.

In SQL Server 2000 and below, you’ll find the database diagrams in Enterprise Manager. In SQL Server 2005 and higher, you work with database diagrams in SQL Server Management Studio (SSMS). For this tutorial, I’ll stick with SSMS, but the ideas are largely the same between the two tools.

I don’t always use the database diagram tool in every situation. For one thing, if the database is large, it can take a lot of time to generate the diagram if there isn’t one already created, and even when that completes, it’s kind of hard to navigate. Another reason this tool is less than useful is that, prior to SQL Server 2008, it’s “live”. That means that if you make a change (even accidentally) in the diagram tool, you’ll commit it to the database. The final reason this isn’t a great tool is that it isn’t compliant – it doesn’t follow the conventions of a Chen or other Entity Relationship Diagram (ERD) standard.

So why use it at all? Well, for a small database like pubs, it’s adequate enough to show the table objects and their relationships. You can even right-click various parts of the diagram to show more information (like the column names and types) and print out the document.  So at least for the pubs database it’s useful to create and show a diagram.

To do that, I simply navigate to pubs and click the Database Diagrams node. I get a message stating that the system needs to create some meta-data to enable the diagrams for the database, and I answer yes to that. You might also get told that the database doesn’t have an owner — just navigate to the Security node and add your account as the database owner for that. When that’s complete, I add all the tables and related tables, and tell the system to create the diagram.

The next two methods are available in both pre and post SQL Server 2005. You can query the various system tables — I still do this from time to time, but in SQL Server 2005 they changed, which is the primary reason you shouldn’t rely on them. It’s better to go after views that show the system data. For SQL Server 2005 you can use the INFORMATION_SCHEMA views for tables and views:

SELECT * 
   FROM INFORMATION_SCHEMA.TABLES;
   GO
   And:
   SELECT * 
   FROM INFORMATION_SCHEMA.VIEWS;
   GO

Although you shouldn’t rely on the system tables between versions, there’s nothing like the old standby, sysobjects. Here is a query that asks the sysobjects table for the names of the objects, ordered by the types. I’ve added a few CASE statements to make the types friendlier — and this works in most every version of SQL Server:

SELECT name, xtype = 
   CASE xtype   
                   WHEN 'U' THEN 'User Table' 
                   WHEN 'C' THEN 'CHECK constraint' 
                   WHEN 'D' THEN 'Default or DEFAULT constraint' 
                   WHEN 'F' THEN 'FOREIGN KEY constraint' 
                   WHEN 'L' THEN 'Log' 
                   WHEN 'FN' THEN 'Scalar function' 
                   WHEN 'IF' THEN 'In-lined table-function' 
                   WHEN 'P' THEN 'Stored procedure' 
                   WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)' 
                   WHEN 'RF' THEN 'Replication filter stored procedure'
                   WHEN 'S' THEN 'System table'
                   WHEN 'TF' THEN 'Table function' 
                   WHEN 'TR' THEN 'Trigger' 
                   WHEN 'U' THEN 'User table' 
                   WHEN 'UQ' THEN 'UNIQUE constraint (type is K)' 
                   WHEN 'V' THEN 'View' 
                   WHEN 'X' THEN 'Extended stored procedure'
   END
   FROM
   sysobjects
   ORDER BY xtype DESC, name ASC;
   GO

For SQL Server 2005 and higher, you can use the new system catalog views. They are much easier to work with, and you can find their names by running this query in any database:

SELECT name 
   FROM sys.sysobjects
   WHERE name LIKE 'sys%' 
   AND xtype = 'V'
   ORDER BY name ASC;
   GO

With those names in hand, you can go after the particular objects you want to learn more about, such as sys.sysobjects — which actually closely mirrors the sysobjects tables from the earlier versions! In fact, many of these new catalog views do just that. Just query each one to find out what they contain. I’ll cover those fully in another tutorial.

Main Samples for pubs

Alright — we’ve installed the pubs database and taken a look at its objects and relationships. Let’s take a look at a few basic examples you can use with this database to see data and play around with it. I’ll build on these examples as we go — this is just so you can see what you can do with this small database. Note that all of Books Online for SQL 6.5 — 2000 included pubs examples, so you can go there for a much richer set of examples to play with.

The keys here are the relationships. By knowing what objects are joined and how they are related, you can form your own selects, inserts, updates and deletes. We’ll keep it simple for now. As a bonus, can you figure out how to delete all books written by an author?

USE pubs;
   GO
   
   /* Simple select */
   SELECT * 
   FROM authors
   ORDER BY au_lname;
   GO
   
   /* SELECT with JOIN
   Authors and their titles. 
   Remember, authors can write more
   than one book, so there are 
   three tables involved */
   SELECT authors.au_fname
   , authors.au_lname
   , titles.title
   FROM authors
   INNER JOIN titleauthor
   ON authors.au_id = titleauthor.au_id
   INNER JOIN titles
   ON titleauthor.title_id = titles.title_id
   ORDER BY authors.au_lname
   
   /* INSERT a new author */
   INSERT INTO [pubs].[dbo].[authors]
            ([au_id]
            ,[au_lname]
            ,[au_fname]
            ,[phone]
            ,[address]
            ,[city]
            ,[state]
            ,[zip]
            ,[contract])
      VALUES
            ('123-12-1234'
            ,'Woody'
            ,'Buck'
            ,'123-123-1234'
            ,'123 Sunny Side Ave'
            ,'Tampa'
            ,'FL'
            ,'12345'
            ,1)
   GO
   
   /* Give that new author a book */
   INSERT INTO [pubs].[dbo].[titles]
            ([title_id]
            ,[title]
            ,[type]
            ,[pub_id]
            ,[price]
            ,[advance]
            ,[royalty]
            ,[ytd_sales]
            ,[notes]
            ,[pubdate])
      VALUES
            ('BW1234'
            ,'Why living in Florida is great!'
            ,'psychology '
            ,'0736'
            ,25
            ,1234567889
            ,100
            ,23456789
            ,'What an amazing guy. We have to get more books from him!'
            ,'05/05/1985')
   GO
   
   /* Now tie the book to the author */
   INSERT INTO [pubs].[dbo].[titleauthor]
            ([au_id]
            ,[title_id]
            ,[au_ord]
            ,[royaltyper])
      VALUES
            ('123-12-1234'
            ,'BW1234'
            ,1
            ,100)
   GO
   /* Now you can run the SELECT with JOIN 
   query to see the new book and author */
   
   /* UPDATE an Book's info */
   UPDATE titles
   SET title = 'Why living in Florida is extremely awesome!'
   WHERE title_id = 'BW1234';
   GO
   
   /* DELETE an author's link to their books */
   DELETE FROM titleauthor
   WHERE titleauthor.au_id IN
   (SELECT au_id FROM authors
   WHERE au_lname = 'Woody' 
   AND au_fname='Buck')
   /* Now get rid of the author */
   DELETE from authors
   WHERE au_lname = 'Woody' 
   AND au_fname='Buck';
   GO

InformIT Articles and Sample Chapters

An oldie but a goodie — This sample chapter from all the way back in SQL Server 7.0 talks about restoring the pubs database.

Books and eBooks

Here’s a great book that uses pubs to teach you about Visual Basic for Applications.

Online Resources

This is the link for the SQL Server 2000 sample databases (pubs and Northwind). 

This is the link for the SQL Server 2005 sample databases (all the AdventureWorks flavors).  

This is the link for the SQL Server 2008 sample databases.