Chapter 12. Creating and Managing Tables in SQL Server

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.

Table 12.1. An Example of a Table, Showing Columns and Rows

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.

Listing 12.1 A Simple Create Table Script

 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.