|
|
Posted by David Portas on 04/16/06 15:18
Peter wrote:
>
> The way I look at it is that when the implementation of SQL Server changes
> in a next version, so the select * causes problems, I can change that anyway
> and replace the asterix with the column names. I then make the cost of extra
> maintenance overhead when nessecary. In the mean time, I didnt put any extra
> development time in naming each column. When something change in the table,
> I just recompile the view, without worrying about which columns are added.
>
> Let me put my question in another way:
>
> Is there an extra performance overhead now in SQL Server 2000/2005 when I
> use select * in views instead of naming each column of the table?
If the queries are executed by SQL Server on the same server as the
view then there may not be any measurable performance overhead of
SELECT * (except perhaps during compilation). However, you are mistaken
about something else. Recompiling a view containing SELECT * will NOT
necessarily cause it to reflect changes made to the base tables. For
this reason alone, it is a bad idea to use SELECT * in views. See the
following example, which was tested on 2000 SP4 and 2005.
CREATE TABLE dbo.t1 (x INT NOT NULL PRIMARY KEY, z1 INT NULL);
GO
CREATE VIEW dbo.v1 AS
SELECT * FROM dbo.t1
GO
ALTER TABLE dbo.t1 DROP COLUMN z1 ;
ALTER TABLE dbo.t1 ADD z2 INT ;
GO
EXEC dbo.sp_recompile 'dbo.v1' ;
/* Notice that the second column still exists as Z1 in the view */
SELECT x,z1 FROM dbo.v1 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|