Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Why to avoid SELECT * from tables in your Views

– 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