Creating a Table

You can use Enterprise Manager to add a table to a database. In this section, you'll add a table to the Northwind database to store the details of a person. This table will be called Persons, and will contain the columns shown in Table 2.8.

Table 2.8: DEFINITION FOR THE COLUMNS OF THE Persons TABLE

COLUMN NAME	DATABASE TYPE	LENGTH	ALLOWS NULL VALUES?
PersonID	int	4	No
FirstName	nvarchar	15	No
LastName	nvarchar	15	No
DateOfBirth	datetime	8	Yes
Address	nvarchar	50	Yes
EmployerID	nchar	5	No

To create a table in the Northwind database, you select the Tables node of the Northwind database in Enterprise Manager and select Action ➣ New Table. You'll then see the table designer. Add the columns as shown in Table 2.8 to the table, as shown in Figure 2.20.

Figure 2.20: Adding a new table

Note 

The length of some of the data types is fixed. For example, the int type always uses 4 bytes of storage space, so you can't change the length of an int column from 4. Similarly, the datetime type always uses 8 bytes of storage space. You can change the length of nchar and nvarchar columns because those types are designed to store variable-length data.

Click the Save button on the toolbar to save the table. In the Choose Name dialog, enter Persons as the name, and click OK to save your table, as shown in Figure 2.21.

Figure 2.21: Entering the name of the table

Note 

Once you've saved your table, you can return to the table designer at any time by selecting the table in the Tables node of Enterprise Manager, right-clicking the table, and selecting Design Table.

In the rest of this chapter, you'll learn how to:

• Get additional information about the columns in a table using the Columns tab.

• Set the primary key of a table.

• Set the permissions that allow access to the contents of a table.

• Create a relationship between tables.

• Create an index to allow faster access to the information in a table.

• Create a constraint to restrict values that may be stored in a column.

The Columns Tab

In the area beneath the grid, you'll notice a tab named Columns. The Columns tab contains additional information about the currently selected column in the grid, and Figure 2.20, shown earlier, shows the information on the PersonID column. As you change your selected column, the information in the Columns tab will change.

You can enter an optional description for a column in the Description field of the Columns tab. The Default Value field allows you to supply an initial value when a new row is added to the table; you can of course supply your own value to a column that will override the default value.

The Precision field shows the maximum number of digits that may be used to store a number, including those that might be stored to the right of a decimal point. The Scale field shows the maximum number of digits to the right of a decimal point. For example, the precision and scale of an int column are 10 and 0, meaning that an int column can store up to 10 digits, with no digits to the right of a decimal point-no digits to the right because an int is an integral number. The precision and scale for a money column are 19 and 4, meaning that a money column can store up to 19 digits, with up to four of those digits to the right of a decimal point.

The Identity field allows you specify whether SQL Server should automatically assign a value to a field. If you set the Identity field to Yes, then you can also specify values for the Identity Seed and Identity Increment fields. You use the Identity Seed field to set the initial value for the column, and you use the Identity Increment field to specify the increment for value. For example, if you set the Identity Seed to 1 and the Identity Increment to 1, then the first value for the column would be 1, the next would be 2, and so on. The ProductID column of the Products table is an example of a column that uses an identity to set its value.

The IsRowGuid field specifies whether a uniqueidentifier column is a globally unique identifier known as a GUID.

Tip 

SQL Server doesn't automatically supply a value for a GUID. If you want SQL Server to generate a GUID, you can use the SQL Server NEWID() function. The NEWID() function always returns a different value. You can then use the output from this function as the Default Value for your uniqueidentifier column. For example, you would set the Default Value field to [NEWID()]. You'll learn more about SQL Server functions in the next chapter.

The Formula field allows you to set a formula that is used to assign a value to a column.

The Collation field specifies the rules that are used to sort and compare characters. You might need to set this when working with foreign languages. For further details, consult the SQL Server Books Online documentation.

Setting the Primary Key

Next, you'll set the primary key for the Persons table to PersonID. To do this, click on the first row in the grid containing the PersonID column, and click the Set primary key button on the toolbar. Once you've done this, you'll see a small key icon to the left of PersonID.

Setting the Permissions

To set the permissions for your table, click the Show permissions button on the toolbar of the table designer. Grant SELECT, INSERT, UPDATE, and DELETE permissions to the public role, as shown in Figure 2.22. These permissions allow public users to retrieve, add, modify, and remove rows from the Persons table.

Figure 2.22: Setting the permissions

Click OK to continue.

Creating the Relationship

You'll be creating a relationship between your Persons table and the Customers table. To view the relationships screen, click the Manage Relationships button on the toolbar of the table designer. Click New to start creating the relationship. Pick the Customers table as the primary key table and pick the CustomerID column from this table. Make sure Persons is selected as the foreign key table, and pick the EmployerID column from this table. Figure 2.23 shows this. You'll notice that the relationship name is automatically set to FK_Persons_Customers.

Figure 2.23: Creating the relationship

The check boxes at the bottom the page are as follows:

• Check existing data on creation This applies your constraint to data that might already exist in the database when you add your relationship to the foreign key table.

• Enforce relationship for replication Replication allows you to copy information to a different database. When you enable Enforce relationship for replication, your constraint is applied to the foreign key table when that table is copied to a different database during replication.

• Enforce relationship for INSERTs and UPDATEs This applies your constraint to rows that are added, modified, or removed from the foreign key table. It also prevents a row in the primary key table from being deleted when there is a matching row in your foreign key table.

• Cascade Update Related Fields This causes SQL Server to automatically update the foreign key values of your relationship when the primary key value is modified.

• Cascade Delete Related Fields This causes SQL Server to automatically remove rows from the foreign key table whenever the referenced row in the primary key table is removed. Click Close to continue.

Creating an Index

An index allows the database to quickly locate a row when you request retrieval of that row based on a particular column value. In this section, you'll create an index on the LastName column of your Persons table.

To view the indexes for your Persons table, click the Manage Indexes/Keys button on the toolbar of the table designer. Click New to start creating a new index. Set the index name as IX_LastName_ Persons, pick the LastName column, and set the order as ascending. Figure 2.24 shows this.

Figure 2.24: Creating an index

You won't change any of the other fields and check boxes when creating your index, but just so you know what they are, this is what the fields mean:

• Index Filegroup The index filegroup is the filegroup in which you want to store your index. A filegroup is made up of one or more physical files on a computer's hard disk. SQL Server uses filegroups to store the actual information that makes up a database.

• Create UNIQUE The Create UNIQUE option allows you to create a unique constraint or index for the selected database table. You indicate whether you are creating a unique constraint or index by selecting either the Constraint or Index radio button.

• Ignore duplicate key If you create a unique index, you can select this option to ignore duplicate key values.

• Fill factor Unless you are an advanced SQL Server user, you should leave the fill factor in the default setting. The smallest unit of storage in a SQL Server database is a page, which can hold up to 8,096 bytes of data. The data for tables and indexes are stored in pages. You can specify how full each index page can be by setting the fill factor. For example, if you set the fill factor to 60 percent, then the page will contain up to 60 percent data and 40 percent empty space. The amount of empty space on an index page is important because when an index page fills up, SQL Server must split the page to make room for new index data. By reducing the fill factor, therefore, you can increase the performance of your database because SQL Server won't have to split pages so often. Reducing the fill factor, however, also causes the index to take up more hard disk space because there will be more empty space in each page. If you don't specify a fill factor, then the database's default fill factor is used.

• Pad Index Unless you are an advanced SQL Server user, you shouldn't enable the Pad Index option. If you specify a fill factor of more than zero percent and you're creating a unique index through the Create UNIQUE option, then you can enable the Pad Index option. This informs SQL Server that it is to use the same percentage you specified in the fill factor field as the space to leave open on each leaf node of the binary tree that makes up the index. You can learn more about this option in the SQL Server Books Online documentation.

• Create as CLUSTERED You use the Create as CLUSTERED option to indicate that your index is clustered. A clustered index is one that contains the actual table rows, rather than pointers to the table rows. Clustered indexes allow faster retrieval of rows, but require more time when inserting new rows. You can learn more about this option in the SQL Server Books Online documentation.

• Do not automatically recompute statistics You typically shouldn't use this option as it might reduce performance. When you create an index, SQL Server automatically stores statistical information regarding the distribution of values in your indexed columns. SQL Server uses these statistics to estimate the cost of using the index for a query. You use the Do Not Automatically Recompute Statistics option to indicate that SQL Server should use previously created statistics, which means that the statistics are not necessarily up to date and could reduce performance. You can learn more about this option in the SQL Server Books Online documentation.

  Click Close to continue.

Creating a Constraint

A constraint allows you to define a limit on the value that may be stored in a column. In this section, you'll be creating a constraint on the DateOfBirth column of your Persons table. This constraint will ensure that you can place only dates between January 1, 1950, and December 31, 2050, in the DateOfBirth column.

To view the constraints for your Persons table, click the Manage Constraints button on the toolbar of the table designer. Click New to start creating a new constraint. Set the constraint expression as follows:

 ([DateOfBirth] >= '1/1/1950' and [DateOfBirth] <= '12/31/2050') 

Set the constraint name as CK_DateOfBirth_Persons. Figure 2.25 shows this.

Figure 2.25: Creating a constraint

You won't change any of the check boxes when creating your constraint, but just so you know what they are, this is what the fields mean:

• Check existing data on creation Use this option to ensure that data that currently exists in the table satisfies your constraint.

• Enforce constraint for replication Use this option to enforce your constraint when the table is copied to another database through replication.

• Enforce constraint for INSERTs and UPDATEs Use this option to enforce your constraint when rows are added or modified in the table.

Click Close to continue. Save the table and close the table designer.