SQL table modification

Alter table table
{[Alter column column_name
{New_data_type [(precision [, scale])]
[Collate <collation_name>]
[Null | not null]
| {Add | drop} rowguidcol}
]
| Add
{[<Column_definition>]
| Column_name as computed_column_expression
} [,... N]
| [With check | with nocheck] add
{<Table_constraint>} [,... n]
| Drop
{[Constraint] constraint_name
| Column} [,... n]
| {Check | nocheck} Constraint
{All | constraint_name [,... n]}
| {Enable | disable} trigger
{All | trigger_name [,... n]}
}
<Column_definition >::=
{Column_name data_type}
[[Default constant_expression] [with values]
| [Identity [(seed, increment) [not for replication]
]
[Rowguidcol]
[Collate <collation_name>]
[<Column_constraint>] [... n]
<Column_constraint >:: =
[Constraint constraint_name]
{[Null | not null]
| [{Primary key | unique}
[Clustered | nonclustered]
[With fillfactor = fillfactor]
[On {filegroup | default}]
]
| [[Foreign key]
References ref_table [(ref_column)]
[On Delete {cascade | no action}]
[On update {cascade | no action}]
[Not for replication]
]
| Check [not for replication]
(Logical_expression)
}
<Table_constraint >:: =
[Constraint constraint_name]
{[{Primary key | unique}
[Clustered | nonclustered]
{(Column [,... n])}
[With fillfactor = fillfactor]
[On {filegroup | default}]
]
| Foreign key
[(Column [,... n])]
References ref_table [(ref_column [,... n])]
[On Delete {cascade | no action}]
[On update {cascade | no action}]
[Not for replication]
| Default constant_expression
[For column] [with values]
| Check [not for replication]
(Search_conditions)
}

Example
A. Change the table to add a new column
The following example adds a column that allows null values and does not provide a value through the default definition. The value in the new column of each row is null.
Create Table doc_exa (column_a INT)
Go
Alter table doc_exa add column_ B varchar (20) null
Go
Exec sp_help doc_exa
Go
Drop table doc_exa
Go

B. Change the table to remove Columns
In the following example, modify the table to delete a column.
Create Table doc_exb (column_a int, column_ B varchar (20) null)
Go
Alter table doc_exb drop column column_ B
Go
Exec sp_help doc_exb
Go
Drop table doc_exb
Go

C. Change the table to add columns with Constraints
The following example adds a new column with the unique constraint to the table.
Create Table doc_exc (column_a INT)
Go
Alter table doc_exc add column_ B varchar (20) null
Constraint exb_unique unique
Go
Exec sp_help doc_exc
Go
Drop table doc_exc
Go

D. Change the table to add unverified constraints
the following example adds constraints to the existing columns in the table. This column contains a value that violates the constraints. Therefore, with nocheck is used to prevent validation constraints on existing rows and allow the addition of these constraints.
Create Table doc_exd (column_a INT)
go
insert into doc_exd values (-1)
go
alter table doc_exd with nocheck
Add constraint exd_check check (column_a> 1)
go
exec sp_help doc_exd
go
drop table doc_exd
go

E. Change the table to add multiple columns with constraints
the following example adds multiple new columns with constraints to the table. The first new column has the identity attribute. The ID column of each row in the table will have a new value in increments.
Create Table doc_exe (column_a int constraint column_a_un unique)
go
alter table doc_exe add
/* Add a primary key identity column. */
column_ B int identity
constraint column_ B _pk primary key,
/* Add a column referencing another column in the same table. */
column_c int null
constraint column_c_fk
references doc_exe (column_a ),
/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d varchar (16) null
constraint column_d_chk
check
(column_d is null or
column_d like "[0-9] [0-9] [0-9]- [0-9] [0-9] [0-9] [0-9] "or
column_d like
" ([0-9] [0-9- 9] [0-9]) [0-9] [0-9] [0-9]-[0-9] [0-9] [0-9] [0-9] "),
/* Add a nonnull column with a default. */
column_e decimal (3, 3)
constraint column_e_default
default. 081
go
exec sp_help doc_exe
go
drop table doc_exe
go

F. add columns with default values that can be empty.
In the following example, add a column that can be empty and has a default definition, and use with values to provide values for existing rows in the table. If with values is not used, the new column of each row has a null value.
Alter table mytable
Add adddate smalldatetime null
Constraint adddatedflt
Default getdate () with values
G. Disable and re-enable a constraint
In the following example, the constraint used to restrict acceptable salary data is disabled. Use with nocheck constraint with alter table to disable the constraint and execute insert operations that normally cause violation of the constraint. With check constraint re-enable this constraint.
Create Table cnst_example
(ID int not null,
Name varchar (10) Not null,
Salary money not null
Constraint salary_cap check (salary <100000)
)
-- Valid Inserts
Insert into cnst_example values (1, "Joe Brown", 65000)
Insert into cnst_example values (2, "Mary Smith", 75000)
-- This insert violates the constraint.
Insert into cnst_example values (3, "Pat Jones", 105000)
-- Disable the constraint and try again.
Alter table cnst_example nocheck constraint salary_cap
Insert into cnst_example values (3, "Pat Jones", 105000)
-- Reenable the constraint and try another insert, will fail.
Alter table cnst_example check constraint salary_cap
Insert into cnst_example values (4, "Eric James", 110000)
H. Disable and re-enable the trigger
In the following example, the disable trigger option of alter table is used to disable the trigger, so that insert operations that normally violate the trigger conditions can be executed. Next, use enable trigger to re-enable the trigger.
Create Table trig_example
(ID int,
Name varchar (10 ),
Salary money)
Go
-- Create the trigger.
Create trigger trig1 on trig_example for insert
As
If (select count (*) from inserted
Where salary> 100000)> 0
Begin
Print "trig1 error: You attempted to insert a salary >$ 100,000"
Rollback transaction
End
Go
-- Attempt an insert that violates the trigger.
Insert into trig_example values (1, "Pat Smith", 100001)
Go
-- Disable the trigger.
Alter table trig_example disable trigger trig1
Go
-- Attempt an insert that wocould normally violate the trigger
Insert into trig_example values (1, "Chuck Jones", 100001)
Go
-- Re-enable the trigger.
Alter table trig_example enable trigger trig1
Go
-- Attempt an insert that violates the trigger.
Insert into trig_example values (3, "Mary booth", 100001)
Go