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

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

Print | posted on Friday, May 11, 2012 10:10 AM | Filed Under [ T-SQL Techniques SQL Server 2008 ]

Feedback

Gravatar

# re: Why to avoid SELECT * from tables in your Views

hi...thanks!
8/1/2012 3:01 AM | Big bang
Gravatar

# re: Why to avoid SELECT * from tables in your Views

This is nice! Can you give more instruction why the view working like this?
8/7/2012 3:33 AM | jessiefun
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET