Where it comes to using SQL Server features, I am a very late adopter. Not by choice, for all the DotNetNuke modules I create, I'd like to maintain backwards compatibility as much as possible, especially since DotNetNuke also fully supports MS SQL Server 2000.
Just slowly I am converting to use nvarchar(max), which is not available in SQL Server 2000. There is one tool I've become a great fan of though, and that is SQL Prompt, by Red-Gate. This is a great tool, adding intellisense to SQL Server, in management tools such as SQL Server Management Studio, Visual Studio and QueryAnalyzer. I can tell you it is a real time saver!
Just recently i did a complete new installation of Windows Vista, Visual Studio 2008 and SQL Server 2008. (read about my adventure with ASP.NET AJAX extensions for ASP.NET 2.0) I hadn't kept up really with all the new features in SQL Server 2008, so it came as big surprise to me that I had intellisense available in SQL Management Studio 2008, without installing SQL Prompt! See for yourself:
Another thing that seems to be a great tool voor developers: real time sql script debugging!. Check this out:
This will make it so much easier for developers to create their SPROCs.... Good Job Microsoft!