SQL Server – Can a View with Schemabinding be Altered if Binding Requirements are Met

lockingschemasql-server-2008-r2view

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one.

I am changing a column while not changing its alias so consumers will be unaffected.

If I were to drop all my other views that depend on this I would be able to recreate them again, so SCHEMABINDING is still valid.

Can I temporarily disable the schema lock on this view in order to ALTER it?

Or must I drop the dependent views and recreate them once it's changed?

Best Answer

You don't need to drop the dependent views, but you do need to alter them to (temporarily) not be schemabinding. If any of them are indexed, this means you will need to re-create the indexes.

SQL Server doesn't have some weighting system for schemabinding: you can't change the object. Period.

ALTER VIEW dbo.view_name
--Remove this WITH SCHEMABINDING
AS
  SELECT ...

Related Solutions

Sql-server – Schema blocking in READ_COMMITTED_SNAPSHOT

The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"

When a row is deleted or its PK/Unique is mutating, the database engine need to make sure there are no orphans. When there is no proper index to support it, it scans the whole thing.

Can a view affect ALTER TABLE commands

No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;

Best Answer

Related Solutions

Sql-server – Schema blocking in READ_COMMITTED_SNAPSHOT

Can a view affect ALTER TABLE commands

Related Question