Designing and Creating a Table

Use the create table statement to create a practice table. If you do not have create table permission, see a system administrator or the owner of the database in which you are working.

You can create a table, input some data, and work with it for a while before you create indexes, defaults, rules, triggers, or views. This allows you to see what kind of transactions are most common and what kind of data is frequently entered.

However, it is more efficient to design a table and the components that go with it at the same time. You might find it easiest to sketch your plans on paper before you actually create a table and its accompanying objects.

Decide what columns you need in the table, and the datatype, length, precision, and scale, for each.
Create any new user-defined datatypes before you define the table where they are to be used.
Decide which column, if any, should be the IDENTITY column.
Determine which columns can and cannot accept null values.
Decide what integrity constraints or column defaults, if any, you need to add to the columns in the table. This includes deciding when to use column constraints and defaults instead of defaults, rules, indexes, and triggers.
Decide whether you need defaults and rules, and if so, where and what kind. Consider the relationship between the NULL and NOT NULL status of a column, and defaults and rules.
Decide what kind of indexes you need and where.

Create the table and its associated objects:

Create the table and its indexes using create table and create index.
Create defaults and rules using create default and create rule.
Bind any defaults and rules using sp_bindefault and sp_bindrule. Any existing defaults or rules on a user-defined datatype already used in a create table statement, are automatically used.
Create triggers using create trigger.
Create views using create view.

Table Names
Table names must be unique for each user.
Create the User-Defined Datatypes
Before you create a table, create any user-defined datatypes.
Choose Columns That Accept Null Values
Except for columns that are assigned user-defined datatypes, each column has an explicit NULL or NOT NULL entry. You do not need to specify NOT NULL in the table definition, because it is the default.
Sample Table Design Sketch
A table called friends_etc is used to illustrate how to create indexes, defaults, rules, triggers, and so forth.

Parent topic: Databases and Tables

Related concepts

Create Indexes on Tables

Defining Defaults and Rules for Data

Stored Procedures

Triggers: Enforce Referential Integrity

Views: Limit Access to Data