Jeff Smith Blog

-- 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