SQL Server Reference Guide

We're continuing our study of programming SQL Server databases with the next object after databases: tables. Tables are the absolute heart of Relational Database Management Systems, and a solid understanding here will help you immensely as we go along.

I'll go from the general to the specific in this article – but there's a lot to this topic. Don't worry; just keep this introduction handy, since we'll see table structure all throughout the development cycle. I've also included some really good links on this topic, plus several books in the Safari online service from InformIT. Ready? Here we go!

Tables Described

The concept of tables is really quite simple. Think of a series of rows and columns, like a spreadsheet. The columns represent the grouping of the data, and the rows represent the unique sets of data. The "cell," or the single intersection of a row and column, contains a single datum.

The relational database concept (which shares many concepts with set theory from algebra) has a few specific words for all this. Not that many people use them any more, but you might as well be familiar with them:

I'll use the more common names on the left throughout this tutorial.

So what might a table look like? Well, from the pubs sample database in SQL Server, here is a small set that I've selected:

This sample is from the "authors" table. You can see that each row makes a record, and obviously contains information about individual authors. Here's another sample set:

These are titles of books written, and this table has rows and columns representing information about books.

Now that we've covered the individual tables, we move to the reason that they make up the heart of the Relational Database Management System.

Relating Tables

Individual tables normally store the most absolute atomic level of data. By "atomic," I mean that only the data that belongs to the entity is stored in it. Information about the author should only be about the author – not his or her books. We'll come back to that in a bit, during the database design phase. (As a matter of fact, you'll come back to this over and over in your career, and you'll have some fairly heated arguments with your colleagues about what goes in a particular table!) This process is called table normalization, and it has several levels. For now, know that the entire basis of Relational Database Systems is based on this concept of keeping the table data atomic.

Back to the subject at hand. The individual tables are a nice, tight structure in which to store data, but they don't seem to have anything in common. As a matter of fact, from the data shown, you can't figure out which book was written by which author.

To solve this problem, we can add a column in one of the tables that points to the other, or relates them. That's done quite often in SQL, and is perfectly acceptable as long as once piece of data only relates to another one time (called a one to one relationship).

For instance, we could add a column in the second table that has the author's last name in it, showing who wrote that book. That may work, but two authors might have the same last name. Plus, storing the name (or almost any data) in two different tables can often lead to trouble, since you have to remember that if the information changes in one table, you have to change it in all the others.

Another problem arises in this situation if more than one author writes a single book. This is called a one-to-many relationship. I've worked on several projects like that – both as an author and as a SQL-guy! Would you add one column for the other author, two (in the two-author case), three, more?

You can solve this problem with a table that has a column for the author link, and another column for the title of the books. The link for the author would repeat in several records, but the book titles would be different.

You can see that the problem would also carry through the other way round. That is, if we tried to add a column for the books in the author's table, how many should we add? Again, this problem can be solved with a link to the book titles written, with the author appearing only once.

But often both situations occur at the same time. More than one author can write more than one book. This situation is called a many-to-many relationship. How do we resolve that dilemma? With one more table.

Here's what that table looks like in the pubs database:

You can probably guess how this helps. By combining the three tables, we pull the au_id from the first table, the title_id from the second and connect those using the third table. I've highlighted the values in question and placed the third table in the middle:

Authors

TitleAuthors

Titles

The middle table shown above provides the "link" between the other two tables. Actually, the middle table can be used to join many tables. This joining of tables is analogous to the concept of the union in algebra set theory.

To be sure, there's more to know about these relationships, but I've hit you with enough new info for now. We'll revisit this topic in future articles on development, called Logical Database Design.

For now, let's look at how SQL Server physically implements tables, and then how to create them.

How SQL Server 2000 Implements Tables

SQL Server 2000 pretty much complies with the ANSI-92 SQL Standards. Those standards are the base-level requirements for creating, destroying and managing tables. The ANSI standard sets the minimums, and then each vendor then extends that minimum to sell their product.

Here are some quick facts about SQL Server 2000 tables:

• Tables have owners

• Tables can be stored on various SQL Filenames

• You can store 8060 bytes per row of data in a table

• You can have 1024 columns per table

• You can have 2,147,483,647 objects in a database, and the number of tables is part of this maximum

• You can store 1,048,516 TB of data in a SQL Server database, so the total storage of all tables must be kept under this number

Figure 59

I then right-clicked the Tables object and selected New Table from the menu that appeared. I continue the process on this panel:

Figure60

You can see here that I'm setting the name of the columns in my table and giving them a data type (I'll cover data types in greater depth in another article) and length.

The part I wanted to point out here is the Allow Nulls selection. I'll explain Nulls more completely when we begin coding, but a Null condition means basically "I don't know yet." It doesn't mean zero, it doesn't mean nothing, it means "I don't know yet." As a matter of fact, there is still a bit of argument going around on whether you should even be allowed to have a Null value in a database.

While I won't take sides on this issue (some people need to get out more!) I will say that you should carefully consider whether you should allow a value to be Null. Here's a basic rule of thumb: If you're going to compare this value to something else, then you should think long and hard about allowing a Null value. Think of it like this:

I don't know how much money I have in my pocket. You walk up and say, "How much money do you have?" I say, "I don't know." You then ask, "Do you have more than Carl? He's got ten dollars." I reply "I don't know; I might." You ask, "Do you have exactly what Christine has? She has five dollars." I reply "I don't know. I might have that much, or none. I just don't know."

You see, you can never be sure what I have, so you can't trust any answer I give. For that reason, you shouldn't compare values to a Null value. In listing the number of people who have five dollars in their pocket, you really can't count me in or out of that set.

Even though I've said all this, I'll make quite a few of my columns to allow a Null that really shouldn't be, so we can see the problems that it creates later.

OK, I've beat that concept to death! Let's move on.

The next panel has all the columns filled out, and you can see that I've also created a value to hold the key for this table, which is the unique value assigned to each row. It's easy to do – just highlight the column and click the key icon. I've set the Identity attribute to be true on this Integer column, which means that SQL Server will generate a new number for me each time I enter a new record.

Figure 61

I haven't covered all of the options here, since we'll use this tool again when we create our programming databases in future articles. I just wanted to expose the panels to you here.

Clicking the icon that looks like a scroll brings up the following panel:

Figure 62

Here I'm setting the table name, owner, and so forth. The next tab on this panel is the Relationships tab, shown here:

Figure 63

On this tab I can create relationships with other tables, just as we discussed earlier. This allows me to make sure that a "child" value from another table checks to make sure that a "parent" value exists in this one. I can also make this the "child" table, or have this table be a child to one table and a parent to another.

The next tab is shown here:

Figure 64

I explained indexes in another set of articles, and here's how you can create them graphically. We'll see more of this tab later. The next tab sets the check constraints:

Figure 65

A check constraint is another way you can make sure users and programs only put data in a field when it meets certain conditions. These constraints, along with others, are called Declarative Referential Integrity. That means that no matter how the code is prepared, you prevent anyone or thing from entering data you don't want in the database. If you forgo this type of constraint, it will be up to the developer to make sure the data is clean. That's called Programmatic Referential Integrity, and is less safe, for obvious reasons.

OK, that's the high level graphical method for creating a table. You can also create tables with T-SQL Commands. The syntax is quite extensive, and is described completely in Books Online, but here's the code to create the same table I just made:

CREATE TABLE UserNames (
 UserCode int IDENTITY (1, 1) NOT NULL,
 FirstName varchar (50) NULL,
 LastName varchar (50) NOT NULL,
 Address varchar (50) NULL,
 City varchar (50) NULL,
 State char (2) NOT NULL,
 PostalCode varchar (15) NULL 
) 
ON [PRIMARY]

The relevant parts here are the Null and Not Null qualifiers, which I'm sure you can decipher. Also note the IDENTITY qualifier, which creates that auto-incrementing (1 to start, then increment by 1 each time) value.

What you don't see here is the Primary Key constraint I set with the key icon earlier. I'll demonstrate adding that constraint in the next article.

There's an optional qualifier here: the ON [PRIMARY] keywords. This important command sets where the table lives. If you just have one file for all objects (as 90% of most databases do) then you don't need it. If you do make other files for the database (which I've described elsewhere on this site), remember that they aren't used unless you put this statement on your indexes or table creation statements. You want to place tables that are used most often on these other files to speed access to them.

Whew!

Well, once again, we've scratched the surface of one of the database objects. We've got several more to cover, and as mentioned, we'll be revisiting the table object quite often in our programming. See you soon!