I haven't had much of a chance to play with SQL Server 2005 too much yet, but some of the features are really great, such as inclusion of the CLR.
Security guru Dana Epp has a great example of using SQL Server 2005 as an extra layer of highly configurable data validation using regular expressions. Check out the data validation screencast and read more about Dana's Anvil project here.