Why to avoid SELECT * from tables in your Views
- by Jeff Smith
-- clean up any messes left over from before:
if OBJECT_ID('AllTeams') is not null
drop view AllTeams
go
if OBJECT_ID('Teams') is not null
drop table Teams
go
-- sample table:
create table Teams
(
id int primary key,
City varchar(20),
TeamName varchar(20)
)
go
-- sample data:
insert into Teams (id, City, TeamName )
select 1,'Boston','Red Sox' union all
select 2,'New York','Yankees'
go
create view AllTeams
as
select * from Teams
go
select * from AllTeams
--Results:
--
--id City TeamName
------------- -------------------- --------------------
--1 Boston Red Sox
--2 New York Yankees
-- Now, add a new column to the Teams table:
alter table Teams
add League varchar(10)
go
-- put some data in there:
update Teams
set League='AL'
-- run it again
select * from AllTeams
--Results:
--
--id City TeamName
------------- -------------------- --------------------
--1 Boston Red Sox
--2 New York Yankees
-- Notice that League is not displayed!
-- Here's an even worse scenario, when the table gets altered in ways beyond adding columns:
drop table Teams
go
-- recreate table putting the League column before the City:
-- (i.e., simulate re-ordering and/or inserting a column)
create table Teams
(
id int primary key,
League varchar(10),
City varchar(20),
TeamName varchar(20)
)
go
-- put in some data:
insert into Teams (id,League,City,TeamName)
select 1,'AL','Boston','Red Sox' union all
select 2,'AL','New York','Yankees'
-- Now, Select again for our view:
select * from AllTeams
--Results:
--
--id City TeamName
------------- ---------- --------------------
--1 AL Boston
--2 AL New York
-- The column labeled "City" in the View is actually the League, and the column labelled TeamName is actually the City!
go
-- clean up:
drop view AllTeams
drop table Teams
