Schema Management in SQL Server 2005

The schema changes can be effectively managed in SQL Server 2005 by understanding its internal behavior. The following points should be kept in mind before making changes the schema in large tables

The fastest schema changes will occur when

• Adding a column with NULL property
• Adding a column with NULL property and DEFAULT
• Changing NOT NULL to NULL property
• Adding DEFAULT constraint
• Dropping CHECK or DEFAULT constraint

The slowest schema changes have accompanying read and write operations such as:

• CHECK constraints require a read of the entire table to enforce the CHECK.
• Batch updates occur when adding a NULL column in combination with DEFAULT and WITH VALUES clauses
• Batch updates occur when adding NOT NULL column (to provide Default value)
• Batch updates occur when changing NULL to NOT NULL property
• Batch updates occur when column data types are changed or when lengths change. The only exception is increasing the length of varchar.

The schema change best practice for performance and concurrency of large tables includes adding columns with NULL properties and avoid batch updates.The more detailed performance and concurrency issues during the schema cahnge can be found at the below link

http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspx