SQL Server - SQL Table Basics

Create Table

The mechanics of creating a table are relatively straight forward. Being able to design a well thought out database that will scale to meet the needs of a large scale enterprise is a very challenging undertaking. In these examples we will be working through development of a fairly simple three table database. In doing so we will get to cover a good deal of the development basics for creating tables and indexes.

Creating the person table


CREATE TABLE person
     ( 
        num               INT              NOT NULL  , 
        firstname         VARCHAR(20)          NULL  , 
        lastname          VARCHAR(30)          NULL  ,         
        gender_code       VARCHAR(1)           NULL  , 
        birth_dttm        DATETIME             NULL  ,  
        inactive_date     DATETIME             NULL  ,
        CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (num ASC) 
        ON [PRIMARY] 
     )
GO

Creating the phone table


CREATE TABLE phone
     ( 
        person_num   INT          NOT NULL  ,
        type_code    CHAR(3)      NOT NULL  ,          
        area_code    CHAR(3)      NULL  , 
        exchange     CHAR(3)      NULL  , 
        extension    CHAR(4)      NULL  , 
        CONSTRAINT PK_phone PRIMARY KEY CLUSTERED 
        (person_num ASC, type_code ASC) ON [PRIMARY] 
     )
GO

Creating the address table


CREATE TABLE address
     ( 
        person_num    INT          NOT NULL  , 
        type_code     CHAR(4)      NOT NULL  , 
        street1       CHAR(30)         NULL  , 
        street2       CHAR(30)         NULL  , 
        city          CHAR(30)         NULL  , 
        state         CHAR(2)          NULL  , 
        postal_code   CHAR(10)         NULL  , 
        CONSTRAINT PK_address PRIMARY KEY CLUSTERED 
        (person_num ASC, type_code ASC) ON [PRIMARY] 
     )
GO

It is possible, though not advised to create a table without a primary key. I will discuss primary keys on a different page yet to be created. It is also possible to set initial default values for a column though none of the columns in our tables were suitable candidates to do this. There are many good primers on creating tables and databases. The purpose of this page is just to show the basic SQL Server syntax by example for creating a table. Also, taking the create table for an existing table and using it as a model is also a very good practice and means of learning.

If you find this site useful and are a book buyer/reader... You can support my site by clicking on one of the Links below to Abe books By doing this, I will receive a small commision on your purchase and making your purchase will not cost you any more money than if you went directly the site yourself! Abe Books sells books at a fraction of the original cost. I have purchased many books from them originally cost $40.00 for as little as $5 to $10 dollars including shipping costs Click on applicable icon to find books at Abe Books on ...
SQL Server 2008	SQL Server 2005	SQL Server 2000	T-SQL

Abe Books is one of my affiliates. I only use affiliates that I also purchase products from. I love Abe books. If it is printed they likely have a copy. I have also included a generic link to Abe Books below. Once on their site, you will find that they have a nice, easy to use search engine so to find what you are looking for.