Search: Go
 Transact SQL
 Other Articles
 Software Reviews

 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

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:

CREATE TABLE [dbo].[MyTable](
[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](
[MySalesPersonID] [int] NOT NULL,
[MySalesPersonName] [varchar](100) NOT NULL,
[MySalesInLastQuarter] [int] NOT NULL,

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.

Useful Links

  • The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012