SQL Server - SQL Table Basics
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.