3 4
To create a table in a project (such as the empty Tasks project we created in the previous section), double-click Create Table In Design View on the Tables tab in the Database window, as shown in Figure 19-14. Alternatively, you can click the New button in the Database window.
Figure 19-14. You can double-click Create Table In Design View in the Database window to create a new table.
In either case, a new, blank table will open in Design view, as shown in Figure 19-15.
Figure 19-15. A newly created table opens in Design view.
You can add columns (fields) to the table much as you would for an Access table, except that the Length and AllowNulls properties are columns in the grid. You need to specify a data type for each column. Table 19-2 lists the SQL Server field data types. (See Table 19-1 for a comparison of Access and SQL Server field data types.)
Table 19-2. SQL Server field data types
SQL Server data Type | Description |
bigint | Stores whole numbers from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. |
bit | Stores either a 1 or 0 value; other integer values are interpreted as 1. |
char | A fixed-length data type with a maximum of 8000 ANSI characters. |
datetime | A date-and-time data type ranging from January 1, 1753, through December 31, 9999, to an accuracy of 3.33 milliseconds. |
decimal | An exact data type that holds values from -1038-1 through 1038-1. Allows specification of the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point). |
float | Stores positive values from approximately 2.23E-308 through 1.79E + 308 or negative values from approximately -2.23E-308 through -1.79E + 308. |
image | A variable-length data type that holds a maximum of 2,147,483,647 bytes of binary data. Used to store Binary Large Objects (BLOBs) such as pictures, documents, sounds, and compiled code. |
int | Stores whole numbers from -2,147,483,648 through 2,147,483,647. |
money | Stores monetary values from -922,337.203,685,477.5808 through 922,337,203,685,477.5807. |
nchar * | A fixed-length data type with a maximum of 4000 Unicode characters. |
numeric | An exact data type that holds values from -1038-1 through 1038-1. Allows specification of the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point). |
nvarchar(n) | A variable-length data type with a maximum of 4000 Unicode characters. |
real | Stores positive values from approximately 1.18E - 38 through 3.40E + 38 and negative values from approximately -1.18E - 38 through -3.40E + 38. Zero can also be stored. |
smalldatetime | A date-and-time data type ranging from January 1, 1900, through June 6, 2079, to an accuracy of one minute. |
smallint | Stores whole numbers from -32,768 through 32,767. |
smallmoney | Stores monetary values from -214,748.3648 through 214,748.3647. Rounded up to two decimal places when displayed. |
sql_variant | Stores data of several data types, except for text, ntext, image, timestamp, and sql_variant types. Used in a column, parameter, variable, or return value of a user-defined function. |
text | A variable-length data type with a maximum of 2,147,483,647 characters; the default length is 16. |
timestamp | A data type that's automatically updated every time a row is inserted or updated. Values in timestamp columns are not datetime data, but binary (8) or varbinary(8), indicating the sequence of data modifications. |
tinyint | Stores whole number from 0 through 255. |
uniqueidentifier (SQL Server 7 or later) | A 16-byte GUID. |
user-defined | A definition of the type of data that a column can contain, defined by the user with existing system data types. Rules and defaults can be bound only to user-defined data types. |
varbinary | A variable-length data type with a maximum of 8000 bytes of binary data. |
varchar(n) | A variable-length data type with a maximum of 8000 ANSI characters. |
* In an Access project or SQL Server database, the "n" prefix stands for "national." It means that the data type is unicode-enabled. In an Access database, all text columns are unicode-enabled.
Figure 19-16 shows the table with four fields, one of them set as the key field. When you close the new table, you'll be asked to name it. As with naming Access tables, you may want to use the LNC tag tbl to identify tables, although this naming convention isn't as widely used for SQL Server tables as for Access tables. In this example, we'll name the table tblTasks.
Figure 19-16. A new table has four fields of different data types.
To aid the discussion of setting up relationships between project tables, let's follow the same procedure discussed earlier to create another table named tblPeople. Add to the table two columns: Person and Available. Set both columns' Data Type property to char and the Length property to 50. Set the Person column as the primary key column.
The column properties for the columns in project tables are set on the Columns tab in the Table Designer, as shown earlier in Figure 19-16. The more commonly used properties are listed here. For information about the more obscure properties, see the "Columns Property Page" Help topic.
The Lookup tab for project columns works the same way as the equivalent feature for Access database table fields.
See Chapter 4, "Creating a Database," for details on creating a lookup field.
Constraints in an Access project table correspond to validation rules in an Access database table. You set them on the Check Constraints tab in the table properties sheet. Figure 19-17 shows the constraint for the emp_id key field of the Employee table in the Publications project we created earlier. The Constraint expression corresponds to the database Validation Rule property, and the Validation text corresponds to the database property of the same name.
Figure 19-17. A check constraint for a project table field corresponds roughly to a validation rule for an Access table field.
Triggers for project tables are a special type of stored procedure. Triggers somewhat resemble event procedures for Access forms, reports, and controls except that they fire when data is modified by an INSERT, an UPDATE, or a DELETE statement. Triggers, like stored procedures, are written in Transact-SQL code.
caution
Figure 19-18 shows the ALTER trigger for the Employee table in the Publications project. To open this trigger, click the Tables tab in the Database window, right-click the Employee table, and choose Triggers from the context menu. Click Edit in the Triggers For Table Employee dialog box. If you want to create or delete a trigger, click New or Delete in this dialog box.
Figure 19-18. The Employee table in the Publications project has an ALTER trigger.
To set a column as a primary key in a project table, select the column, and click the Primary Key button on the Table Design toolbar, as shown in Figure 19-19.
Figure 19-19. Click the Primary Key button on the Table Design toolbar to set a field as the primary key for a project table.
note
To index a column in a project table, select one of the available choices for its Indexed property: No, Yes (Duplicates OK), or Yes (No Duplicates). Figure 19-20 shows that you can select an index type for the Due column in the tblTasks table in the Tasks project we created earlier.
Figure 19-20. You can select an index type for a column in a project table.
Troubleshooting - I opened a project, but I don't see any tables in the Database window, and the project's name has disconnected after it
SQL Server might not be set to run when Windows is started. To connect your project to its back-end SQL Server database, select Service Manager from the Microsoft SQL Server program group (or click its icon in the Windows system tray), and click the Start/Continue button to start SQL Server, as shown in Figure 19-21. After you close and reopen the project, it will be connected to its SQL Server back-end database and you can work with the tables and other SQL Server objects.
Figure 19-21. Start SQL Server in the SQL Server Service Manager dialog box.
You can create a relationship between tables in a database diagram. The technique is slightly different from the technique used in the Relationships window in an Access database. For this example, we'll use the Tasks project created earlier.
To link two project tables in a database diagram, follow these steps:
Figure 19-22. You use the Add Table dialog box to add a table to a database diagram.
Figure 19-23. You can select relationship options in the Create Relationship dialog box.
Figure 19-24. Linked tables are displayed in a project database diagram.
note
You can also create relationships between project tables on the Relationships tab in the table's properties sheet. To create a relationship between the AssignedTo column of tblTasks and the Person column of tblPeople, follow these steps:
note
Figure 19-25. You can create a relationship between project tables in the table's properties sheet.
There's no OK button to create the relationship; saving the table saves the new relationship.