Export (0) Print
Expand All
Expand Minimize

CREATE TABLE (Azure SQL Database)

SQL Server 2014

Creates a new table in Azure SQL Database.

Important note Important

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.

Applies to: Azure SQL Database. For SQL Server syntax, see CREATE TABLE (SQL Server).

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)
[ ; ]

<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 ]
  
<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 ) 
} 

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

Note Note

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.

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

Because Azure SQL Database does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft