Sql Server Interview Question:Alter Table

What do you do with Alter table statement?
Modifies a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers.

Which are the data type column we cannot alter?

The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.

When altering column what you have to taken care of?

The previous data type must be implicitly convertible to the new data type.
new_data_type cannot be timestamp.
ANSI null defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
ANSI padding is always on for ALTER COLUMN.
If the altered column is an identity column, new_data_type must be a data type that supports the identity property.
The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as if the ARITHABORT option is ON.

What is WITH CHECK, WITH NOCHECKin the alter table definition?

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.

Which are the constraint use CHECK NOCHECK?

Specifies that constraint_name is enabled or disabled. When disabled, future inserts or updates to the column are not validated against the constraint conditions. This option can only be used with FOREIGN KEY and CHECK constraints.
ALL
Specifies that all constraints are disabled with the NOCHECK option, or enabled with the CHECK option.

How do your enable or disable a trigger?
ENABLE | DISABLE} TRIGGER
Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.
ALL
Specifies that all triggers in the table are enabled or disabled.
trigger_name
Specifies the name of the trigger to disable or enable.

What is ON DELETE CASCADE NO ACTION definition in the alter table statement?

Specifies what action occurs to a row in the table altered, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.
The CASCADE action ON DELETE cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table in question.


What is ON UPDATE CASCADE NO ACTION in the alter table definition?
Specifies what action occurs to a row in the table altered, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.
If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.
The CASCADE action ON UPDATE cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the table in question.

What is WITH VALUES in the alter table definition?

Specifies that the value given in DEFAULT constant_expression is stored in a new column added to existing rows. WITH VALUES can be specified only when DEFAULT is specified in an ADD column clause. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.


Which schema used by the alter table statement?
To add new rows of data, use the INSERT statement. To remove rows of data, use the DELETE or TRUNCATE TABLE statements. To change the values in existing rows, use UPDATE.
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows.

ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change.
The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects a large number of rows.

Notes on Alter table:
Indexes created as part of a constraint are dropped when the constraint is dropped.
Indexes that were created with CREATE INDEX must be dropped with the DROP INDEX statement.

The DBCC DBREINDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not need to be dropped and added again with ALTER TABLE.
All indexes and constraints based on a column must be removed before the column can be removed.

When constraints are added, all existing data is verified for constraint violations. If any violations occur, the ALTER TABLE statement fails and an error is returned.
When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the data in the column(s) must be unique.

If duplicate values are found, the ALTER TABLE statement fails. The WITH NOCHECK option has no effect when adding PRIMARY KEY or UNIQUE constraints.
Each PRIMARY KEY and UNIQUE constraint generates an index. The number of UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the table to exceed 249 no clustered indexes and 1 clustered index.

If a column is added having a uniqueidentifier data type, it can be defined with a default that uses the NEWID() function to supply the unique identifier values in the new column for each existing row in the table.

SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.
The ALTER COLUMN clause of ALTER TABLE does not bind or unbind any rules on a column. Rules must be bound or unbound separately using sp_bindrule or sp_unbindrule.

Rules can be bound to a user-defined data type.
CREATE TABLE then automatically binds the rule to any column defined having the user-defined data type.
ALTER COLUMN does not unbind the rule when changing the column data type. The rule from the original user-defined data type remains bound to the column.

After ALTER COLUMN has changed the data type of the column, any subsequent sp_unbindrule execution that unbinds the rule from the user-defined data type does not unbind it from the column for which data type was changed.

If ALTER COLUMN changes the data type of a column to a user-defined data type bound to a rule, the rule bound to the new data type is not bound to the column.