Insider Q&A

I just installed SQL Server, but I’ve never used a relational database before. Where do I start?

You’ve probably already considered taking a training class, either through a Microsoft Certified Technical Education Center (CTEC) or an independent training organization (Windows 2000 Magazine subscribers, see "Instructor-Led Training," March 2001). If you’ve rejected that idea, I suggest you think about it again. There's so much to learn about SQL Server that it's very difficult for a beginner to know what's necessary to get started and what might be valuable later on. A formal class can get you going in the right direction, distilling the key concepts and showing you in lab sessions how to use important tools. You might find that subsequent classes are unnecessary if you’re a self-directed learner, but you can't beat a week-long formal training class for giving you the most for your time.

If training isn’t possible, examine SQL Server Books Online (BOL), which installs with SQL Server. This documentation is one of the best features of SQL Server. Start your reading with the Database Developer’s Companion section if you’re using SQL Server 6.5; for SQL Server 7.0 and SQL Server 2000, read the Accessing and Changing Relational Data section. Be sure to try all the examples, using the Interactive ISQL/w tool that comes with SQL Server 6.5 or the Query Analyzer tool in SQL Server 7.0 or 2000. (You can copy examples from the document and paste them right into the tool’s query window.) You’ll soon be eager to start creating your own tables and writing queries that you find meaningful.

I installed SQL Server 6.5 and specified that the master database should be 25MB. However, when I used Enterprise Manager to examine my master database, it was only 15MB. What happened?

You might be confusing the Master Device with the master database—an easy thing to do. SQL Server devices are Windows NT OS files that SQL Server initializes. If you look in your Windows Explorer you should see a file called SQL65datamaster.dat, which should be 25MB. That's your master device. SQL Server divides this space into pages that are always 2KB each.

SQL Server uses some of this space at installation time to build three databases: master, model, and tempdb. So, master is the name of a device and a database. The master database contains system tables, which hold information about your SQL Server configuration, the databases on your SQL Server, and the devices available to your SQL Server.

After the creation of these databases, you’ll probably still have space available on your master device because in the default installation, the master database uses 15MB, the model database uses 1MB, and the tempdb database uses 2MB. This leaves 7MB of remaining space. If you install the sample Pubs database, which uses 3MB of the master device, you’ll have 4MB of remaining space. You can use the extra space to increase the size of any existing database or to build a new database.

I’m the owner of a SQL Server database, for which I created a user named Joe. I gave Joe permission to create tables, and he created a table named Inventory. Joe then gave public permission to select from his table. However, when I issue the command

SELECT * FROM inventory

I get the following message:

Msg 208, Level 16, State 1Invalid object name 'inventory'.

However, when I list all the objects in my database, I see a table named Inventory. Why don’t I have permission to select from this table?

The message isn’t reporting a permission problem. If the problem were permission-related, you would see a message like the following:

Msg 229, Level 14, State 1SELECT permission denied on object inventory ...

The full name for a SQL Server object includes both the database name and the name of the Database Object Owner (DBOO). For example, if you wanted to look at the rows in the Titles table in the Pubs database, you would have to know that DBO owns the table (the owner of a database always has the user name DBO within his or her own database). You could use the following query to retrieve the rows in the Titles table:

SELECT * FROM pubs.dbo.titles

You can use two ways to find the DBOO. You can issue the sp_help command to see the owner of each object listed right after the object name, or you can use Enterprise Manager to expand the database, its objects, and its tables; you'll see the owner of each table after the table name.

Here are several shortcuts to using the full three-part naming convention for accessing SQL Server objects. If you're using the database that contains the object you want to access, you can leave the database name off the three-part name, leaving you a two-part name. If the DBOO is either yourself or the DBO, you can leave the owner name off also. In this example, because you're the DBO but you don’t own the Inventory table, you must specify the table owner’s name. You can see the rows in Joe’s Inventory table with the following:

SELECT * FROM joe.inventory

If you or the DBO owns the object but the object isn’t in your current database, you can leave the owner name off:

SELECT * FROM pubs..titles

You can have many tables with the same name in a database as long as they have different owners. However, for the sake of clarity, I recommend that you give each table a different name.