Non-bound views with SELECT * broken after database changes?

Question

Non-bound views with SELECT * broken after database changes?

Cody Konior

SSCarpal Tunnel

Points: 4827
More actions
December 11, 2011 at 7:05 pm

#248006

On SQL Server 2008 RTM I have a non-schema bound view like this which is called from some stored procedures:
CREATE VIEW Blah
AS
SELECT Table1.*,
SomethingSpecial
FROM Table1
LEFT OUTER JOIN (SELECT LSView.TableKey,
MIN(LSView.SomeDate) AS SomethingSpecial
FROM LSView) Table2
-- Actually another view that references a linked server
ON Table1.TableKey = Table2.TableKey
Basically tacking a special date onto a table from a linked server. Over the weekend some fields were changed in the database, and the stored procedures that called the view would no longer run.
- The query from the view would work correctly.
- Running the view itself would return all NULLs in the SomethingSpecial date field.
- Any stored procedures would fail on date checks against SomethingSpecial because it really believed it was a BIT now (Operand type clash: date is incompatible with bit).
Dropping and recreating the view fixed all the problems. But now I want to know why it happened, I thought making it non-schema bound would make the views safe.

Viewing 4 posts - 1 through 3 (of 3 total)

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

dogramone Hall of Fame Points: 3216 More actions · Answer 1

Your poblem is probably related to the "select *" stuff. When SQL creates the view with select * it creates the view in the underlying DB engine as select field1, field2, etc.

so changing the enderlying defanition will impact a view with select * and is why most if not all people recommend never usign select * in views or procs.

Lowell SSC Guru Points: 323506 More actions · Answer 2

dogramone (12/11/2011)
Your poblem is probably related to the "select *" stuff. When SQL creates the view with select * it creates the view in the underlying DB engine as select field1, field2, etc.
so changing the enderlying defanition will impact a view with select * and is why most if not all people recommend never usign select * in views or procs.

dogramone's identified the issue; in our shop, if we typically run sp_refreshview [viewname] on every view in a database when a script is deployed, so they get fixed for that select * situation, and also lets us see if the views are now broken due to dropped columns, and need to be tweaked.

you should make the views select the specific columns, but that's not always possible or desirable, i know, which is why we do a cleanup run like this.

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!

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 3

Thanks guys, today I learned 😉

I'd heard the advice about using specific fields but I didn't want the maintenance of adding/removing fields every single time the underlying table changes. Turns out I have maintenance either way; sigh.