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.

  1. Decide what columns you need in the table, and the datatype, length, precision, and scale, for each.
  2. Create any new user-defined datatypes before you define the table where they are to be used.
  3. Decide which column, if any, should be the IDENTITY column.
  4. Determine which columns can and cannot accept null values.
  5. 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.
  6. 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.
  7. Decide what kind of indexes you need and where.
Create the table and its associated objects:
  1. Create the table and its indexes using create table and create index.

  2. Create defaults and rules using create default and create rule.

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

  4. Create triggers using create trigger.

  5. Create views using create view.

Related concepts
Create Indexes on Tables
Defining Defaults and Rules for Data
Stored Procedures
Triggers: Enforce Referential Integrity
Views: Limit Access to Data