VERKOOP: 1-800-867-1389
EN
Deze inhoud is niet beschikbaar in uw taal, maar wel in het Engels.

ALTER TABLE (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see ALTER TABLE.

Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning partitions, or disabling or enabling constraints and triggers.

ImportantImportant
This topic is not being maintained. For the most current version, see ALTER TABLE.

Syntax Conventions (Azure SQL Database)


ALTER TABLE [database_name. [ schema_name ]. |schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ ,scale ] 
            | max } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]
    | {ADD | DROP } 
        { PERSISTED | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
       <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]

    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...n ] 

    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 
    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }
    | REBUILD 
       [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      ]
}
[ ; ]

<drop_clustered_constraint_option> ::=  
    { 
       ONLINE = {ON | OFF }
            
    }

<rebuild__option> ::=
{ 
  IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF }
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] 
    ]
 [<column_constraint> [ ...n ] ] 

<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 } ] 
    | CHECK ( logical_expression ) 

] 

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

<column_set_definition> ::=  
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS



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

Microsoft Azure SQL Database does not support the following arguments and options with the ALTER TABLE statement:

  • xml_schema_collection

  • ROWGUIDCOL option

  • NOT FOR REPLICATION

  • Enable/Disable CHANGE_TRACKING

  • SWITCH [PARTITION] TO target_table [PARTITION]

  • SET FILESTREAM_ON

  • REBUILD specific partition (no partition support)

  • REBUILD option in tempdb

  • REBUILD option with compression

  • REBUILD ON

    • filegroup

    • default

    • partition_schema_name

noteNote
Microsoft 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 #.

Microsoft Azure SQL Database does not support four-part names.

While SPARSE columns can be queried by all client drivers supported by Microsoft 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.

For more information about the arguments and the ALTER TABLE statement, see ALTER TABLE in SQL Server Books Online.

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

When using the <index_option> argument, you can only use the index options that are enabled for Microsoft Azure SQL Database. For a complete syntax of the index options in Microsoft Azure SQL Database, see the <index_option> in the syntax description of the CREATE TABLE (Azure SQL Database).

Vindt u dit nuttig?
(1500 tekens resterend)
Bedankt voor uw feedback

Community-inhoud

Toevoegen
Weergeven:
© 2014 Microsoft