By looking into the ALTER TABLE full syntax, the below are the different usages of ALTER TABLE command.
The ALTER TABLE DLL statement allows you to do the following:
- ALTER a column by changing its data type, size, collation, NULLability (from NULL to NOT NULL, or vice versa), and so on.
For example, Assume that we have CreatedBy Column with VARCHAR(30) in the table TestTable. To alter column for extending size to 100,
ALTER TABLE [dbo].[TestTable] ALTER COLUMN CreatedBy VARCHAR(100)
- ADD one or more columns( including a computed column) or constraints
For example, adding two new columns ModifiedBy and ModifiedDate to the table TestTable
ALTER TABLE [dbo].[TestTable] ADD ModifiedBy VARCHAR(100), ModifiedDate DATETIME
- DROP a constraint or column
Assume that we have a default constraint on column CreatedBy column of TestTable. To drop this constraint,
ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [DF_TestTable_CreatedBy]
- ENABLE or DISABLE a trigger, constraint, change tracking, or filetable namespace
To add same constrint again,
ALTER TABLE [dbo].[TestTable] ADD CONSTRAINT [DF_TestTable_CreatedBy] DEFAULT (('USerName')) FOR [CreatedBy]- Either CHECK or NOCHECK (disable or enable) a constraint
To disable a foreign key constraint (FK_TestTable_ID_ParentTable_ID),
ALTER TABLE [dbo].[TestTable] NOCHECK CONSTRAINT [FK_TestTable_ID_ParentTable_ID]
GO
- SET filestream on, the filetable directory, or lock escalation to either 'auto' or 'table', or disable it
To enable lock escalation of a table,
ALTER TABLE dbo.TestTable SET (LOCK_ESCALATION = AUTO);
GO
- REBUILD a partition to change the compression. Here DATA_COMPRESSION can be done in PAGE/ROW/NONE levels
ALTER TABLE TestTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
GO
- Set SPARSE columns for Middle Name (sql server 2008 only)
ALTER TABLE dbo.TestTable
ALTER COLUMN SparseCol VARCHAR(100) SPARSE NULL
You cannot use ALTER TABLE to:
- Add an identity property if the table is already having data.
- Remove an identity property.
- Cannot specify column position in the table. It always adds new column at the end of table’s column order.
- Column ordinals cannot be altered using this alter table.
- Columns can be altered in place using alter column statement.
- Only the datatype, sparse attribute (2008) and the Nullable attribute of a column can be changed.
- You cannot add a NOT NULL specification if NULL values exist.
- In order to change or add a default value of a column, you need to use Add/Drop Constraint.
- In order to rename a column, you must use sp_rename.
Add Column/Constraint
If the table already has data and you want to add one more NOT NULL column, then you must have default constraint for the newly created column
ALTER TABLE TestTable
ADD NewCol VARCHAR(50)
CONSTRAINT DF_TestTable_NewCol DEFAULT ' ' NOT NULL
GO
Drop Constraint/Column
- To drop a column called NewCol,
ALTER TABLE TestTable DROP Column NewCol
GO
- To drop a constraint on a table
ALTER TABLE TestTable DROP constraint Constraintname
To temporarily enable/disable foreign key constraints, we can use CHECK/NOCHECK options..
To disable FK,
ALTER TABLE [dbo].[TestTable] NOCHECK CONSTRAINT [FK_TestTable_Name_Test2_ID]
GO
To re-enable the FK constraint,
ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_TestTable_Name_Test2_ID]
GO
Conclusion
To alter a table in SQL server, the above scripts helps you to make the table structure changes to a table References
Refer the ALTER TABLE syntax in http://msdn.microsoft.com/en-us/library/ms190273(v=sql.105).aspx