Creating Tables

A database is nothing more than a container for various types of database objects. The most important of these objects are tables, which hold the actual data that makes up the database. A database isn't very useful without at least one table. Most real-world databases have more than one table - in fact, many databases have dozens of tables.

To create a table, follow these steps:

1. Right-click the Tables node for the database and choose New Table.
2. Type the name of the first column of the table and then press the Tab key.
3. Choose the data type for the column and then press the Tab key.
   SQL Server has several data types to choose from for each column. Use the drop-down list to choose the appropriate type.
4. Use the Allow Nulls check box to indicate whether the column should allow null values. Then press the Tab key.
   Nulls are one of the more confusing aspects of database design and programming. In a SQL database, null means that the item does not have a value. It is different than zero (for numbers) or an empty string (for text). Allowing a column to have null values introduces programming complexities because when you retrieve the value of a column, the program has to anticipate that the value might be missing. But prohibiting nulls (by deselecting the check box) also introduces complexities because you have to make sure you always provide an explicit value for every column.
   The phrase null value is actually an oxymoron. Because null means the absence of a value, it doesn't make sense to say a column can have a null value, or that the value of a column is null.
5. Repeat Steps 2 through 4 to create additional columns.
6. When all the columns have been created, select the column you want to use as a key field for the table, right-click the column, and choose Set Primary Key.
   A little key icon will appear next to the column or columns to indicate the primary key.
   (The primary key provides a unique value that can be used to identify each row in the table. Most tables use a single column, such as a Customer Number or an Invoice Number, as the primary key. But some tables create the primary key by combining two or more columns. This type of key is called a composite key.)
7. Right-click the table name tab that appears above the list of columns and choose Save.
   A Save dialog box appears, prompting you to enter a name for the table.
8. Type a name for the table and then click OK.
   The table is created.

Note that each column has a properties page that appears when you select the column. You can set a variety of properties for each column, including the following:

• Default Value: A value that's supplied for the column if no value is provided when a row is created. This property is especially useful for columns that don't allow null values.
• Description: A text description that you can use to explain the purpose of the column.
• Identity Specification: Used to create an identity field, which is a field whose value is automatically generated when rows are created. Identity fields are often used as the primary key field for a table when it is desirable for the table to have a primary key, but no other column in the table provides a unique value for each row.
  When you create an identity field, you can specify two settings that affect how the values are generated. The seed is the value used for the first row in the table. The increment is a value that's added to the seed for each subsequent row. For example, if you specify 1000 for the seed and 1 for the increment, the rows in the table will be numbered 1001, 1002, 1003, and so on.

Editing Tables

SQL Management Studio includes a spreadsheet-like feature that lets you directly edit the contents of database tables. To use it, right-click the table you want to edit and choose Open Table. The table is opened in a spreadsheet-like window. You can then add data to the table by entering table values for each row. A table after some data has been added via the Open Table command.

Note that in addition to inserting new rows, you can edit existing rows. You can also delete one or more rows by selecting the rows you want to delete and pressing the Delete key.