by Paul Jensen
IN THIS CHAPTER
Datatypes
Numeric Datatypes
Datatype Synonyms
User-Defined Datatypes
Creating Tables
Creating Tables Using T-SQL
Creating Tables Using Table Designer
Viewing Table Properties and Data in Enterprise Manager
Limitations on Table Size and Dimensions
Dropping Tables
Temporary Tables
The table is the basic structure upon which the relational database is built. A table is a set of columns, having defined properties, used to store data. The stored data is represented as rows in the table. These rows are used to represent an entity (an employee, for example), that has a number of attributes (last name, salary) associated with it. These attributes are stored in the table's columns. Table 12.1 illustrates a typical table structure.
Emp_no | Lname | Fname | Phone | Dept | Salary |
---|---|---|---|---|---|
1 | Smith | John | 555-1111 | 20 | 10000 |
2 | Jones | Jill | 555-1211 | 20 | 10000 |
3 | Johnston | Bob | 555-3214 | 30 | 20000 |
4 | Jensen | Carl | 555-4321 | 40 | 12000 |
5 | Wright | Alex | 555-2156 | 40 | 14000 |
6 | Ivings | Kris | 555-3215 | 20 | 21000 |
The CREATE TABLE and UPDATE TABLE commands are used to create and modify tables. When you create a table, you must provide a table name, a name for each of the columns, and a datatype for each column. Optionally, but highly recommended, you can specify whether a column should allow null values. The script for creating the table in Table 12.1 is shown in Listing 12.1.
CREATE TABLE employee ( Emp_no int NOT NULL, Lname char(20) NULL, Fname char(20) NULL, Phone char(13) NULL, Dept smallint NULL, Salary int NULL )
The example in Listing 12.1 uses three different datatypes?int, char, and smallint?to define how data will be stored. Before you learn more about the business of creating tables, it's important that you have an understanding of datatypes.