Renaming Views - Part 2

Did you know that if you rename a view using SSMS you can wind up in a situation that causes a different view to get over written later in the future? That's right, change the name of a view by appending something like '_old' and later on when someone runs sp_refreshview you will change the original view! Unexpected and incredibly bad, watch the lesson to see how it happens.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

AEE49878AC on 2/14/2011

Excellent...thanks for sharing!

Will Casey on 2/15/2011

good insight

37F4DA828D on 2/15/2011

well - that explains what happened a year or so ago... thanks - I thought I was a few french fries short of a happy meal

Mark Clancy on 2/15/2011

Thanks for sharing. One might wonder why this invitation to insidious latent problems is permitted within SSMS.

Joe on 2/15/2011

Excellent Lesson on what NOT to do with views! Thanks Andy!

D151BB6B9F on 2/15/2011

You probably should have reiterated the right way (drop and recreate) that you did in Part 1. It's worth repeating.

Ed Svastits on 2/15/2011

Andy, what is the best practice for avoiding this problem? In the past, I've right-clicked on the view, selected Script View as ... CREATE To ... New Query Editor Window, then created the new (renamed) view that way. Then, I'd go back and modify (ALTER) the original view. Do you have a more elegant solution? If so, then please share it.

Kevin Bayer on 2/15/2011

Thanks Andy...something else to look out for...

WChaster on 2/15/2011

Did I hear you right this is an undocumented feature, not a bug?

Don Weigend on 2/15/2011

Excellent tip, thank you!

jamie on 2/15/2011

I hope this was reported to MS as a bug.

Dexter Jones on 2/15/2011

Good information, as usual, but it would have been nice to see the proper way to rename a view. Or is that going to be part three? ;)

Wayne on 2/16/2011

I did not know that! thanks

Mark Sopczak on 2/16/2011

Wow... never knew that. It almost seems like a bug to me. Quite honestly they should not allow a rename view if you ask me.

Glen Taylor on 2/17/2011

Very good to known.

Bobby on 2/18/2011

Thanks for the tip, I was not aware of this behavior!

Lee Linares on 2/21/2011

Very enlightening. I never suspected this behaviour.

Gil on 2/22/2011

I really appreciate lessons like this that help people avoid a possible "gotcha" that wouldn't have been avoided just by knowing about SQL Server, but only comes from experience. It's much better to benefit from the painful experience of others.

Mahadeavn on 2/27/2011

Hi Andy, Very nice information. Thanks for your community support

Maurice Ivory on 3/11/2011

It was nice sharing the bad experience with us so that we can learn not to do it.

Talha on 4/27/2011

Thanks for sharing.

M Hoss on 5/24/2011

why would you run sp_refreshview... if it could create the error you describe?

Keith Badeau on 6/13/2011

Great tip, thanks a lot for the videos.

Olu on 7/13/2011

Wow! I'm just speechless. Thanks for pointing this out Andy. I do think that Microsoft should get their act together on this though and ensure that any name change is implemented all the way through.

ROKKAM KIRAN KUMAR on 9/16/2011

Very Nice Video that to avoid renaming a view