Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



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.



Post Categories




SQL Server 2008

All the stuff that is new and exciting -- until SQL Server 2011 comes along, that is!
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            ...

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

UNPIVOT: Normalizing data on the fly

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

posted @ Wednesday, April 23, 2008 10:33 AM | Feedback (17) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques SQL Server 2005 Report Writing SQL Server 2008 ]

SQL Server 2008 - Enhancements in Date and Time Data Types (link)

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!

posted @ Thursday, August 30, 2007 10:05 PM | Feedback (1) | Filed Under [ Links SQL Server 2008 DateTime Data ]

Powered by:
Powered By Subtext Powered By ASP.NET