SQL Server Performance Forum – Threads Archive

Odd View Problem

I have a view which simply selects * from a few joined tables. I use it for an export function in an application, which selects just the fields it needs from the view. At some point the view started behaving strangely. It was returning the correct data, but some field names were being transposed.. ie normally O_Value is a float, and O_OddsText is a varchar. The view was returning these two fields, but with O_Value being the column name for O_OddsText and vice versa. Obviously this causes no end of grief for my application which expects certain fields to be of a certain type. After a bit of playing around, I eventually refresh the definition of the view by running an ALTER VIEW with exactly the same definition as it already had. After this the view started returning the correct columns and data again. Is this typical behavoir for a view? Should I have been recreating the view after every schema change to the base tables? The view is not schema bound, and the fields in the base table which were being transposed had not been altered since the view was created (but the underlying base tables had (new fields etc)). The view is not indexed either. This is contrary to how I expected a view definition to work.
Thanks for any advice

SELECT * will bind columns to their ordinal positions when the view is created. Schema changes can cause the view to return the wrong columns. Example made by Brett @ SQLTeam USE Northwind create table foo ([id] int identity, value1 varchar(20), value2 char(10));
GO insert into foo values (‘foo’, ‘bar’);
insert into foo values (‘bar’, ‘bar’);
insert into foo values (‘foo’, ‘foo’);
GO Create view v_foo as Select * from foo
go select * from v_foo
GO SELECT * INTO tmp_Foo FROM Foo DROP TABLE Foo
GO
create table foo ([id] int identity, value1 varchar(20), value2 char(10), value3 varchar(30));
GO SET IDENTITY_INSERT foo ON INSERT INTO foo([id], value1, value2)
SELECT [id], value1, value2 FROM tmp_foo SET IDENTITY_INSERT foo OFF
GO insert into foo values (NULL, NULL, ‘foobar’); select * from v_foo select value3 from v_foo
GO ALTER TABLE foo DROP Column value2 select * from v_foo
select * from foo
GO
DROP VIEW v_foo
DROP TABLE foo
GO

yeah this is true – i’ve seen this. To avoid it ever happening, use WITH SCHEMA_BINDING when you create your view and then if you try to change the structure of any tables the view relies upon you will receive an error message. (you then have to drop the view, make the changes to the table, re-create the view) Dave.

Thanks guys, I had a feeling it would be to do with this.
Id prefer not to use WITH SCHEMA BINDING, hopefully if I explicitly name all fields instead of * this too will resolve the problem. If all else fails, I can quite happily drop the view and recreate since the only time the schema will ever change is when changes are made by my application (its a single user MSDE database deployed with my app, and I can send ‘plugin’ files which perform operations on the database through my app.
Cheers Paul

Anyone want to say "don’t use select *" Sure for testing and concepts, but never for prod Brett :cool:

hehe yes, point taken. Ive no excuse for that one <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /><br /><br />

]]>