Creating and Modifying a Table

After the database design has been determined, the tables that will store the data in the database can be created. The data is usually stored in permanent tables. Tables are stored in the database files until they are deleted and are available to any user who has the appropriate permissions.

Temporary Tables

You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from computers running Microsoft® SQL Server™. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

For example, if you create a table named employees, it can be used by any person who has the security permissions in the database to use it, until it is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.

Table Properties

You can define up to 1,024 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 also define a data type for each column.

Although table names must be unique for each owner within a database, you can create multiple tables with the same name if you specify different owners for each. You can create two tables named employees and designate Jonah as the owner of one and Sally as the owner of the other. When you need to work with one of the employees tables, you can distinguish between the two tables by specifying the owner with the name of the table.

To create a table

Modifying Tables

After a table is created, you can change many of the options that were defined for the table when it was originally created, including:

• Columns can be added, modified, or deleted. For example, the column name, length, data type, precision, scale, and nullability can all be changed, although some restrictions exist. For more information, see Modifying Column Properties.
• PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.
• UNIQUE and CHECK constraints and DEFAULT definitions (and objects) can be added or deleted.
• An identifier column can be added or deleted using the IDENTITY or ROWGUIDCOL property. The ROWGUIDCOL property can also be added to or removed from an existing column, although only one column, in a table can have the ROWGUIDCOL property at any one time.
• A table and selected columns within the table can be registered for full-text indexing.

For more information about the modifications that can be made to a table, see ALTER TABLE.

The name or owner of a table can also be changed. When you do this, you must also change the name of the table in any triggers, stored procedures, Transact-SQL scripts, or other programming code that uses the old name or owner of the table.

To rename a table

To change the owner of a table

See Also