Posted by Steve Jorgensen on 10/20/05 16:40

On 20 Oct 2005 02:27:37 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote:

>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

I run a program that drops and rebuilds all the stored procedures, views, and
functions. Before it runs, it checks to see if any previously existing
objects have been added or removed, copies the definitions of new objects, and
flags the deleted objects. I had to write this anyway to deal with the
occasional renamed object or column, since that can create a real tangled mess
otherwise.

• Next in forum: Re: Using "SELECT * " is a bad practice even when using a VIEW instead of a table?
• Prev in forum: Re: Using "SELECT * " is a bad practice even when using a VIEW instead of a table?
• Thread view: Re: Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

[Reply to this message]