Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Friday, May 11, 2012

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

posted @ Friday, May 11, 2012 10:10 AM | Feedback (2) | Filed Under [ T-SQL Techniques SQL Server 2008 ]

Powered by:
Powered By Subtext Powered By ASP.NET