This section gives an example of a create table statement you can use to create a practice table of your own. If you do not have create table permission, see a System Administrator or the owner of the database in which you are working.
Creating a table usually implies creating indexes, defaults, and rules to go with it. Custom datatypes, triggers, and views are frequently involved, too.
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 often most efficient to design a table and all the components that go with it at once. Here is an outline of the steps to go through. You might find it easiest to sketch your plans on paper before you actually create a table and its accompanying objects.
First, plan the table’s design:
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.
Decide which columns should and which should not 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 to enforce data integrity.
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. Indexes are discussed in Chapter 13, “Creating Indexes on Tables.”
Now, 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. These commands are discussed in Chapter 14, “Defining Defaults and Rules for Data.”
Bind any defaults and rules using sp_bindefault and sp_bindrule. If there were any defaults or rules on a user-defined datatype that you used in a create table statement, they are automatically in force. These system procedures are discussed in Chapter 17, “Using Stored Procedures.”
Create triggers using create trigger. Triggers are discussed in Chapter 20, “Triggers: Enforcing Referential Integrity.”
Create views using create view. Views are discussed in Chapter 12, “Views: Limiting Access to Data.”
