Creating Tables

You can create permanent tables or temporary tables. SQL Server supports two types of temporary tables: local and global. A local temporary table is visible only to the connection that created it. A global temporary table is available to all connections. Local temporary tables are automatically dropped at the end of the current session. Global temporary tables are dropped at the end of the last session using the table (normally, this is when the session that created the table ends). For details on temporary tables, see the CREATE TABLE statement in the Microsoft SQL Server Transact-SQL Reference.

When you create a table, you name its columns and supply a datatype for each column. You also specify whether or not a particular column can hold null values.

    To create a table

1. Create user-defined datatypes before you define the table in which you want to use them.
2. Create the table with constraints and defaults by using the CREATE TABLE statement. (Any defaults or rules on a user-defined datatype used in a CREATE TABLE statement are automatically in effect.)
3. Create triggers with the CREATE TRIGGER statement.
4. Create views with the CREATE VIEW statement. (For details, see "Using Views," later in this chapter.)
5. Create the table:
   • In SQL Enterprise Manager's Server Manager window, select the database in which you want to create the table, select Tables or Objects, click the right mouse button, and then choose New Table.

     (For details, search for "table" in SQL Enterprise Manager Help.)

   Or

   • Use the CREATE TABLE statement.

     (For details, see the CREATE TABLE statement in the Microsoft SQL Server Transact-SQL Reference.)

   Or

   • Use the INTO option of the SELECT statement.

     (For details, see the SELECT statement in the Microsoft SQL Server Transact-SQL Reference.)

You can define up to 250 columns per table. Table and column names must follow the rules for identifiers; they must be unique within a given table, but you can use the same column name in different tables in the same database. You must define a datatype for each column.

Although table names must be unique for each user, different users can create tables with the same name. For example, a user named Jonah and one named Sally can both create a table named info. Users with permission on both of those tables must qualify the tables as jonah.info and sally.info. Sally must qualify all references to Jonah's info table as jonah.info, but she can refer to her own simply as info.