Transact SQL Other Articles Software Reviews
SQL Server Help: Creating tables using the Create Table statement
As well as creating tables in the SQL Server Enterprise Manager/Management Studio, it is also possible to create them programmatically using SQL statements.
The basic syntax for creating a database table is to use the Create Table statement like the following example:
[MyIdentityColumn] [int] IDENTITY(1,1) NOT NULL,
[MyVarcharColumn] [varchar](100) NOT NULL,
[MyIntColumnWithNullsAllowed] [int] NULL,
[MyDateTimeColumn] [datetime] NOT NULL
This SQL Statement creates a table called MyTable with the owner dbo. The table is created with four columns. The first column, MyIdentityColumn is the identity column and is set up to autonumber new rows (starting at 1 for the first row, and incrementing the row number by 1 each time a row is inserted into the table). The second column, MyVarcharColumn is a varchar field of length 100 and does not accept null values. The third column, MyIntColumnWithNullsAllowed is configured to accept integer values and also nulls. Finally, the last column, MyDateTimeColumn, is a datetime field.
Creating temporary tables in SQL Server
The create table statement can also be used to create temporary tables. There are two types of temporary SQL tables: local, which are accessible to the current SQL connection, and global, which are accessible to all current SQL connections. Local temporary tables have names that are prefixed with a single hash symbol (#), global temporary tables have names that are prefixed with a two hash symbols. The following SQL creates a local temporary table called #MyTempTable:
CREATE TABLE [dbo].[#MyTempTable](
SQL Server's temporary tables are particularly useful if data needs to be stored temporarily by a SQL query or stored procedure and the data is only required by that query or stored procedure. Using temporary tables also means that the database is kept free of temporary data that could otherwise clutter up the database.