CREATE TABLE (Azure SQL Database)
TOC
Collapse the table of content
Expand the table of content

CREATE TABLE (Azure SQL Database)

 

Updated: February 1, 2016

THIS TOPIC APPLIES TO: noSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Creates a new table in Azure SQL Database.

System_CAPS_importantImportant

The current implementation of Federations will be retired with Web and Business service tiers. Consider deploying custom sharding solutions to maximize scalability, flexibility, and performance. For more information about custom sharding, see Scaling Out Azure SQL Databases. SQL Database V12 does not support Web and Business service tiers.


CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    ( { <column_definition> | <computed_column_definition> 
        | <column_set_definition> } 
        [ <table_constraint> ] [ ,...n ] ) 
FEDERATED ON ( distribution_name = column_name)
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] 
    ]
    [ <column_constraint> [ ...n ] ]
    [ SPARSE ]
    [ ENCRYPTED WITH 
        ( COLUMN_ENCRYPTION_KEY = key_name ,
          ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED } , 
          ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max 
] 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
          WITH ( < index_option > [ , ...n ] ) 
        ] 
| [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_ table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
  | CHECK  ( logical_expression ) 
} 

<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
          WITH ( <index_option> [ , ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
   [ NOT FOR REPLICATION ] 
    | CHECK ( logical_expression ) 
] 

<column_set_definition> ::= 
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
           WITH ( <index_option> [ , ...n ] ) 
        ]

    | FOREIGN KEY 
        ( column [ ,...n ] ) 
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 

    | CHECK ( logical_expression ) 
} 

<table_option> ::=
{
    [DATA_COMPRESSION = { NONE | ROW | PAGE } ]
}

<index_option> ::=
{ 
  IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
}

System_CAPS_noteNote

This syntax diagram demonstrates the supported arguments and options in Azure SQL Database.

Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

Azure SQL Database does not support four-part names.

While SPARSE columns can be queried by all client drivers supported by Azure SQL Database, currently only the SQL Server 2008 Native Client ODBC driver or higher provides full support for the compression of null values in a result set.

The FEDERATED ON clause creates the table as a federated table within an existing federation, and applies the distribution constraint (distribution_name) for the federation to the specified federation column within the table (column_name). Federated tables have the following characteristics:

  • The federation column of the federated table can only contain data that confirms to the federation member range_low inclusive and range_high exclusive.

  • The datatype of the federation column must exactly match the data type that is defined in the federation definition.

  • All unique and clustered indexes on the federated table must contain the federation column. The order in which the federation column appears in the index can be different from the key ordinal in the federation.

  • Federation column values cannot be updated to values outside the federation member range.

  • The federation column cannot be a persisted or non-persisted computed column.

  • Indexed Views cannot be created on federated tables.

  • Federation columns cannot be NULLable.

  • All foreign key constraints on federated tables need to include the federation column on both the referrer and the referenced tables at the same ordinal in the foreign key. Reference tables cannot have foreign key relationships with federated tables. Federated tables can have foreign key relationships with reference tables without restrictions.

  • You can drop tables created with the FEDERATED ON clause normally. You can also use ALTER TABLE to change all properties of a federated table except federation attributes such as the federation key. To change a reference table into a federated table or a federated table into a reference table, you must create new tables with the desired properties and drop the existing table.

  • When a table is marked with STATISTICS_NORECOMPUTE, federation operations such as SPLIT do not invalidate or recalculate statistics. This could cause execution plan issues after repartitioning operations such as SPLIT.

  • Federated tables do not support the identity property.

  • Federated tables do not support the timestamp and rowversion data type.

ENCRYPTED WITH

Specifies encrypting columns by using the Always Encrypted feature.

COLUMN_ENCRYPTION_KEY = key_name

Specifies the column encryption key. For more information, see CREATE COLUMN ENCRYPTION KEY (Transact-SQL).

ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED }

Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows searching using equality comparison, grouping, and joining tables using equality joins based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. Joining two tables on columns encrypted deterministically is only possible if both columns are encrypted using the same column encryption key. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, and joining on encrypted columns. Columns using randomized encryption cannot be indexed.

Use deterministic encryption for columns that will be search parameters or grouping parameters, for example a government ID number. Use randomized encryption, for data such as a credit card number, which is not grouped with other records, or used to join tables, and which is not searched for because you use other columns (such as a transaction number) to find the row which contains the encrypted column of interest.

Columns must be of a qualifying data type.

ALGORITHM

Must be 'AEAD_AES_256_CBC_HMAC_SHA_256'.

For more information including feature constraints, see Always Encrypted (Database Engine).

Applies to: In preview in SQL Database.

For more information about the arguments and the CREATE TABLE statement, see CREATE TABLE (Transact-SQL).

The following example creates a simple table name Regions, with three columns.

CREATE TABLE Regions
(RegionID int IDENTITY(1,1) PRIMARY KEY,
RegionName varchar(5) NOT NULL,
RegionContact varchar(50) NULL);

This topic is separated from the CREATE TABLE (Transact-SQL) topic to clarify which syntax is applicable to SQL Database. For many more examples of creating columns, see CREATE TABLE (Transact-SQL). Since most syntax elements are the same, the examples are not provided here to avoid duplication.

Community Additions

ADD
Show:
© 2016 Microsoft