SQL Server 2008
All the stuff that is new and exciting -- until SQL Server 2011 comes along, that is!
-- 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 ...
Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. We all know that there's lots of bad databases designs out there, so this can be a handy technique to know.
Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time,...
Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008.
It will be very interesting to see how date and time usage changes once SQL Server 2008 becomes the most commonly used edition. Of course, since even SQL 2005 still doesn't seem to be as widely adopted as I would like, who knows when that will be!