Posted by David Portas on 10/20/05 12:27

SELECT * in views is buggy even if you require every column:

CREATE TABLE T1 (x INT PRIMARY KEY, y INT NOT NULL)
GO
CREATE VIEW V1 AS SELECT * FROM T1
GO
ALTER TABLE T1 DROP COLUMN y
ALTER TABLE T1 ADD z INT
INSERT INTO T1 VALUES (1,2)
GO
SELECT x,z FROM T1
SELECT x,y FROM V1

Result (SP4):

(1 row(s) affected)

x z
1 2

(1 row(s) affected)

x y
1 2

(1 row(s) affected)

This alone means it isn't an option in my book. Given that it takes
less than two seconds to past the column list into a view definition I
don't see what the excuse is for SELECT *.

--
David Portas
SQL Server MVP
--

[Back to original message]