Pages

Wednesday, 16 July 2014

SQL Server: Dropping the Priority column from a table

DBA/Developer authored the following Transact-SQL:

CREATE TABLE Blogging (ID int IDENTITY(1,1)
PRIMARY KEY,
Description varchar(30) NOT NULL,
MaxAttendance smallint DEFAULT 0,
Type bit NULL,
Priority tinyint CHECK (Priority BETWEEN 0 and 10),
Cost money NULL);

DBA/Developer created a view on the table using the following statement:

CREATE VIEW BloggingView
WITH SCHEMABINDING
AS SELECT ID, Description,
Priority FROM dbo.Blogging
WHERE Priority BETWEEN 1 and 5;
To drop the Priority column from a table, DBA/Developer should remove the CHECK constraint on the Priority column and alter the view to remove all references to the Priority column. When DBA/Developer created the view, DBA/Developer included the WITH SCHEMABINDING clause. The WITH SCHEMABINDING clause ensures that base tables of a view cannot be dropped or modified in a way that affects the view's definition. This prevents users from dropping or modifying base tables in a way that makes the view unusable. To drop base tables or make such modifications, DBA/Developer would need to first drop the view, alter the view omitting SCHEMABINDING, or alter the view to remove any unwanted dependencies. In this scenario, the Priority column also has a CHECK constraint, which must also be removed before dropping the column or the following error occurs:
 
 

No comments:

Post a Comment