Altering Tables in SQL Server

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 2234 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we are going to look into ALTER TABLE statement and the best practices for altering a table

Introduction

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:

  1. Add an identity property if the table is already having data.
  2. Remove an identity property.
  3. Cannot specify column position in the table. It always adds new column at the end of table’s column order. 
  4. Column ordinals cannot be altered using this alter table.
  5. Columns can be altered in place using alter column statement.
  6. Only the datatype, sparse attribute (2008) and the Nullable attribute of a column can be changed.
  7. You cannot add a NOT NULL specification if NULL values exist.
  8. In order to change or add a default value of a column, you need to use Add/Drop Constraint.
  9. 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
Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Goud.Kv on: 4/24/2015 | Points: 25
Nice one and Good start.. Keep contributing and sharing your Knowledge.

Thanks,
Krishna.
Posted by: Bandi on: 4/24/2015 | Points: 25
Thank You Krishna...
Posted by: Sunny.Sagar on: 4/28/2015 | Points: 25
Nice article

Login to post response

Comment using Facebook(Author doesn't get notification)