T-SQL: CTEs - the best thing since sliced bread?

In the absence of anything particularly informative to say, i thought it might be nice just to give a big thumbs up to the MS SQL Server guys for introducting CTEs.

I honestly believe they are the best thing to come out of SQL Server in the last 5 years. Ok, so they may not push the boundaries of what SQL can do but they are the thing I find myself using more than any other. Even if its just making a query more "tidy" they are incredibly powerful particularly when you harness them with the ranking functions.

So a big thanks to MS for giving us CTEs. More evidence that its often the little things that make the difference!

T-SQL: DATEADD Bug?

Just trying to troubleshoot an issue with one of my developers and came across this "feature" with DATEADD.

He had something akin to, SELECT DATEADD(Y,-1,DateColumn) and couldn't understand why it wasn't returning the correct data. It turns out that using the datapart Y in this context actually runs as if it were D.

I checked the documentation in BOL which states that DY or Y is a valid datepart which i don't really understand. DY means day of year which isn't really appropriate for a DATEADD statement. If it behaves like DAY then i'd expect that to be in the documentation.

Looking around connect, it appears that this is a problem with the vbscript implementation of dateadd too so my guess is that this error has been "ported".

I raised a connect case #596764 but it was closed as "by design". I guess its low priority and strictly speaking it shouldn't throw an error as Y is a valid datepart. Still, i don't believe the documentation is clear and i also don't believe that it should behave like this.

SSRS: Format Date Parameter Labels in RS

Recently creating a report with a dataset populated parameter list with INT as the value and DATETIME as the label.

I wanted to have a sensible title along the lines of: Report for date: DD/MMM/YYYY rather than the full nasty datetime. Easy i thought. Just use Parameter!MyDate.Label in a textbox and wrap it with a Format expression. To my chagrin however, i could not get this to work. Doing the following:

=Format(Parameters!MyDate.Label, "dd MMM yyyy")

resulted in a display of dd MMM yyyy. Not what i wanted at all.

Although the label is a DATETIME in the dataset, i wondered whether it was being converted to a string by RS so i tried to explicitly convert it to a date before running the format clause:

=Format(CDate(Parameters!MyDate.Label), "dd MMM yyyy")

Again, no dice but with #Error.

The closest I have been able to get is to just TRIM the label to the 1st 10 characters, which doesn't give me the exact format i'm after and I don't believe is the most robust solution.

=Left(Parameter!MyDate.label,10)

I've posted on the MSDN forums and hope for a response.

Admin: Analysis of Deprecated Features

I recently came across the perfmon counter introduced in SQL2008 of SQLServer:Deprecated Features. It basically tracks executions of deprecated features on a given database server which is invaluable if you are trying to find potentially breaking changes in a huge database - particularly if that database has grown organically across several versions of SQL.

There is also the list provided in Books Online, but i think a real live analysis of your database is much more powerful.

Using the DMV sys.dm_os_performance_counters, you can run a query similar to:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Deprecated Features'

And this will give you the values for any deprecated features you may need to weed out. Further explanations of the exact changes can be found in the earlier article.