Creating and Modifying Table Basics
After you design a database, you can create the tables that will store the data in the database. The data is typically stored in permanent tables; however, you can also create temporary tables. Tables are stored in the database files until they are deleted, and are available to any user that has the required permissions.
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 specific table, but you can use the same column name in different tables in the same database. For more information, see Identifiers and Using Identifiers As Object Names. You must also define a data type for each column. For more information, see Assigning a Data Type to a Column.
Although table names must be unique for each schema within a database, you can create multiple tables with the same name if you specify different schemas for each. You can create two tables named employees and designate Jonah as the schema of one and Sally as the schema of the other. When you have to work with one of the employees tables, you can distinguish between the two tables by specifying the schema with the name of the table.
To create a table
How to: Create Tables (Visual Database Tools)
Temporary Tables
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.
There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. 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 the instance of 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 the instance of SQL Server.
For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##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 after you disconnect and after all other sessions are no longer actively using it.
Many uses of temporary tables can be replaced with variables that have the table data type. For more information about using table variables, see table (Transact-SQL).
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. These can include the following:
- 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.
- If the table is a partitioned table, it can be repartitioned, or individual partitions can be added or removed. For more information, see Modifying Partitioned Tables and Indexes and Transferring Data Efficiently by Using Partition Switching.
- 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 by 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 a 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 (Transact-SQL).
The name or schema 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 schema of the table.
To rename a table
Table Properties (Visual Database Tools)
To change the schema of a table
See Also
Concepts
Assigning a Data Type to a Column
Identifiers
Understanding Files and Filegroups
Using Identifiers As Object Names
Other Resources
CREATE SCHEMA (Transact-SQL)
DROP SCHEMA (Transact-SQL)