Create a new table.
Syntax
CREATE TABLE table
( { column_definition | computed_column_definition} [table_constraint] [,...n] )
[ ON storage_option]
[ {TEXTIMAGE_ON { filegroup | "default" } ] [;]
column_definition:
column data_type
[COLLATE collation_name ]
[NULL | NOT NULL]
[CONSTRAINT constraint] DEFAULT constant_expression ]
[IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
[ROWGUIDCOL] [column_constraint [ ...n ] ]
data type:
[type_schema.] type
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
Column_constraint:
[CONSTRAINT constraint ] {PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED ]
[WITH FILLFACTOR = fillfactor
[WITH ( index_option [ , ...n ] )
[ON storage_option]
[CONSTRAINT constraint ]
[FOREIGN KEY ]
REFERENCES [schema. ] referenced_table [ ( ref_column ) ]
[ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[NOT FOR REPLICATION ]
[CONSTRAINT constraint ]
CHECK [ NOT FOR REPLICATION ] ( logical_expression )
Computed_column_definition:
column AS computed_column_expression [PERSISTED [ NOT NULL ] ]
[CONSTRAINT constraint { PRIMARY KEY | UNIQUE } ]
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor
[WITH ( index_option [, ...n] ) ]
column AS computed_column_expression [PERSISTED [ NOT NULL ] ]
[CONSTRAINT constraint]
[FOREIGN KEY ]
[REFERENCES referenced_table [ ( ref_column ) ] ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
column AS computed_column_expression [PERSISTED [ NOT NULL ] ]
[CONSTRAINT constraint ]
[CHECK [ NOT FOR REPLICATION ] ( logical_expression ) ]
[ ON storage_option ]
table_constraint:
[CONSTRAINT constraint]
{PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
(column [ ASC | DESC ] [,...n] )
[WITH FILLFACTOR = fillfactor ]
[WITH ( index_option [, ...n] ) ]
[ON storage_option ]
[CONSTRAINT constraint]
FOREIGN KEY
( column [,...n] )
REFERENCES referenced_table [ ( ref_column [ ,...n ] ) ]
[ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[NOT FOR REPLICATION ]
[ CONSTRAINT constraint ]
CHECK [ NOT FOR REPLICATION ] ( logical_expression )
table:
database.schema.table
database..table
schema.table
storage_options:
partition_scheme ( partition_column )
filegroup
"default"
index_options:
PAD_INDEX = {ON | OFF}
FILLFACTOR = fillfactor
IGNORE_DUP_KEY = {ON | OFF}
STATISTICS_NORECOMPUTE = {ON | OFF}
ALLOW_ROW_LOCKS = {ON | OFF}
ALLOW_PAGE_LOCKS = {ON | OFF}
Arguments:
column - A maximum of 128 characters. The name 'timestamp' is used if no name is specified for a timestamp data type column.
computed_column_expression - An expression that defines the value of a computed column. A computed column is not physically stored in the table, unless the column is marked PERSISTED.
ON <partition_scheme> - Table with partitions stored on one or more filegroups.
ON filegroup - Table
is stored in the named filegroup.
ON "default" (or if ON is not specified at all) - The table is stored on the default filegroup.
TEXTIMAGE_ON - Specifies an alternate storage filegroup for columns of type: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type.
CONTENT - Allow multiple top-level elements in each instance of the xml data type.
DOCUMENT - Allow only one top-level element in each instance of the xml data type.
DEFAULT - A value provided for the column when nothing is explicitly supplied during an insert.
IDENTITY - An identity column, one per table: tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
NULL / NOT NULL - Whether the column can accept null values.
NOT FOR REPLICATION - Do not enforce constraints for the replication agent (IDENTITY, FOREIGN KEY and CHECK constraints.)
CONSTRAINT - Define a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.
CLUSTERED | NONCLUSTERED - The type of index is created for a PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.
FOREIGN KEY REFERENCES - A constraint to provide referential integrity for the data, requires a UNIQUE INDEX on the referenced table.
Maximum row size
The maximum number of bytes per row is generally 8,060 bytes.
This restriction also applies to each column of type: varchar, nvarchar, varbinary, or sql_variant.
However if multiple columns of those datatypes are added it is possible for the total table width to exceed 8,060 bytes.
SQL Server 2005 can have up to 1,024 columns per table.
-- Create table CREATE TABLE [dbo].[ss64Table] ( [ss_ID] [int] NOT NULL, [ss_Line] [smallint] NOT NULL, [ss_fkProductID] [int] NULL REFERENCES Stock.Product(ProductID), [ss_Price] [money] NULL, [ss_Date] [datetime] NULL, [ss_Modified] [datetime] NOT NULL CONSTRAINT [DF_ss64Table_ss_Modified] DEFAULT (getdate()), CONSTRAINT [PK_ss64Table]
PRIMARY KEY ([ss_ID]) ) ON [PRIMARY] -- Create Partitioned Table CREATE PARTITION FUNCTION myRangePtnFunction (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) ; GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePtnFunction TO (Myfg1, Myfg2, Myfg3, Myfg4) ; GO CREATE TABLE ss64Table2 (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO -- Create Temporary Table CREATE TABLE #people ( id INT, name VARCHAR(32) )
"At a good table we may go to school” ~ Thomas Fuller
ALTER TABLE
DROP TABLE
Should I use a #temp table or a @table variable?
Equivalent Oracle command: CREATE TABLE