I have been developing with SQL Server since around the 6.5 version, all the way to the 2005 version.  SQL Server 2005 has so many sophisticated features that almost no one knows about them all or uses them to their full advantage.

One theme I will push repeatedly is using SQL Server to do things for you so you don't have to write as much procedural code.  My first example is using SQL Server to output an RSS feed.  This technique for outputting formatted XML eliminates having to write and maintain processing code that you don't need. 

Also, check out my article Dyamic Data Display in Silverlight.  I use a similar technique to generate XAML in T-SQL instead of procedural code.