palotaiarpad (3/27/2012)
I'm a bit confused.BOL: If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced. Other weird stuff is possible as well - see for instance this demo, that removes the "first" column of the table, then adds a new one.
CREATE TABLE t1
(IntCol int, CharCol varchar(20), DateCol date);
GO
INSERT INTO t1(IntCol, CharCol, DateCol)
VALUES (1, 'One', '2012-01-01'),
(2, 'Two', '2012-02-02');
GO
CREATE VIEW v1
AS SELECT * FROM t1;
GO
SELECT * FROM v1;
GO
ALTER TABLE t1
DROP COLUMN IntCol;
ALTER TABLE t1
ADD NewIntCol int;
GO
UPDATE t1
SET NewIntCol = 0;
GO
SELECT * FROM v1;
GO
DROP VIEW v1;
DROP TABLE t1;
GO
Finally, note that just adding columns does not affect the results from the view; if you comment out the alter table drop column in the code above, the view will keep returning correct results. But dropping the table and recreating it with the columns in a different order (or reordering the columns through the SSMS table designer - which does the exact same thing under the covers) will have similar effects as the code above.