With runaway generation of rows I do not mean these rows end up in the result or are part of additional processing by the consuming parts of the query. But that rows are generated before the TOP operator takes effect when dealing with parallel execution plans. Not always, but frequent enaugh to cause issues of unpredictable large slowdowns. The processing is in the generation of the rows itself for large sets and not anything that depends on the rows that come out of the top operator.

It is a bug obviously and one that the connect issue can use an extra votes for :)

If all you want is the month, you can use MMM or Mmm as the format string. Plus you can change locales!

Of course, the caveat is that it doesn't work in versions of SQL Server prior to 2012. Sadly, that rules it out for most of what I do. The good news is that we BI people pre-build date dimensions with all of that stuff anyway, so I only ever have to worry about this sort of thing when actually building a date dimension. And of course, the general rule is that in almost every case, return a date as a date and let the front end application do the formatting.

What do you mean "remove select value from table"? Why wouldn't you use this against a table?

Geoff (the other "Jeff"), did the exact same test as what's in the article which contains a million rows. I'd say he did a pretty good job of comparing "apples-to-apples". I did the original test in the article on an 11 year old single CPU desktop, which is MUCH slower than any modern day laptop, and the "new" 2012 method is still 10 times slower according to Geoff's tests.

I will admit though... both of us used SET STATISTICS to get our times which has been known to make a mess of things when testing Scalar Functions in T-SQL. Perhaps the new FORMAT function has the same problems as Scalar Functions written in T-SQL. I don't have 2012 loaded on any of my boxes at home or at work (explains why I didn't load it at home), yet. I do have a vacation starting tomorrow. Maybe I'll have time to load 2012 on my laptop during the vacation.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Wow! Unless SET STATISTICS is really putting the hammer on this like it does on Scalar Functions (see the following article on that subject), it looks like MS may have might have made a terrible mistake in their code. That's really too bad because a lot of people were really looking forward to this FORMAT functionality.

Hat's off to you for being one of those good people that actually tests the new stuff instead of just blindly using it. Hmmmm... I wonder if the formatting in SSIS has the same massive performance problem as this. It's ultra rare to find anyone that's doing performance tests in SSIS. I don't test in SSIS because I avoid using it altogether.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."