SCHEMABINDING question

Question

SCHEMABINDING question

Ray Cacciatore

Ten Centuries

Points: 1260
More actions
September 28, 2010 at 7:32 am

#238907

If I just change the data type of a field in a table, does that break the schemabinding on a view that is based on that table?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You won't be able to do the change on the table untill the schema bound objects have been dropped.

Use ssms and see the script it generates to have an idea of how it needs to be done. AFAIK, you don't need to drop the table for this, just alter will work. I'm just saying in case ssms drops the table like it does sometimes.

Lowell SSC Guru Points: 323518 More actions · Answer 2

actually, what happens is the alter table statement fails:

here's a simple example:

create table Example(exid int identity(1,1) not null primary key,

exampletext varchar(30) )

GO

create view vw_example

with schemabinding as

select exid,exampletext from dbo.Example

alter table Example alter column exampletext varchar(50)

Msg 5074, Level 16, State 1, Line 2

The object 'vw_example' is dependent on column 'exampletext'.

Msg 4922, Level 16, State 9, Line 2

ALTER TABLE ALTER COLUMN exampletext failed because one or more objects access this column.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sjimmo SSChampion Points: 11139 More actions · Answer 3

I recieved the following error when attempting to alter a table with a view which was created with schemabinding:

Warning: The following schema-bound objects will be modified:
- View 'dbo.test_vw': schema binding will be removed.

The change to the table was made, and the schema binding was removed. I made an additional change after to find that the binding was no longer there. If the view was created without the

with schemabinding

then the view will continue to work as normal as it is pulling data from the regular select statement.

BTW - after breaking the binding, the view still worked.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Did you use a script to do the modification or did you use the gui?

I wouldn't "lose" the schema binding if I were in your place. It's certainly there for a good reason.

sjimmo SSChampion Points: 11139 More actions · Answer 5

Did you use a script to do the modification or did you use the gui?

I did it the lazy way, used the GUI

I wouldn't "lose" the schema binding if I were in your place

???

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Ya the gui will let you make the changes and it will drop the schema bindings.

You need to re-run the scripts to create the views so that you get the schemabinding back.