ALTER TABLE

Computed column definition.

Syntax


  ALTER TABLE table ALTER COLUMN column [ WITH {CHECK | NOCHECK} ] ADD computed_column_definition [,...n]
    

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:
    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} 

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.

Example

-- add a date/time column defaulting to today
ALTER TABLE MyTable
ADD MyDateColumn smalldatetime NULL
CONSTRAINT MyDateColumn_dflt
DEFAULT GETDATE() WITH VALUES ;
GO

"At a good table we may go to school" - Thomas Fuller

Related commands:

CREATE TABLE
DROP TABLE
Equivalent Oracle command: ALTER TABLE