Chapter 12. Creating and Managing Tables in SQL Server

by Paul Jensen


  • 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
  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.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features