Maybe others do but I don't. Don't get me wrong... I very much appreciate it's rather universal nature for what it does but the people that designed it really didn't do their homework for what people use it for. It not returning the ordinal position of each element makes it useless for me. Some say that you can trust the order of its return because of the nature of such code behind the scenes and use a ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) to create that information but that's no guarantee because we don't actually know what that underlying code is. Joins could put a whammy on it, as well. And for them to not return anything when you pass it a NULL doesn't inspire confidence in that area for me. They also missed the huge opportunity to make it solve the much larger problem of having it be able to split true CSV/TSV with quoted identifies and the like.
Things like IIF are a great typing shortcut and I do use it in my production code but how much time did they actually expend on it while they ignored things like a very high performance sequence generator to replace the likes of Tally tables and the various functions that people have built to make a read-less version? Reading the dialog of the people at Microsoft on the related decade-old connect item tells me that they're actually and grossly out of touch with what people need to do in the real world, especially when it comes to T-SQL. The travesties of DATE, TIME, and DATETIME2() also bear witness to that fact. They tried to cover that mistake by adding a DATEDIFF_BIG() to the repertoire without, apparently, realizing that even ANSI/ISO standards clearly state that EndDate-StartDate = Interval, which the lowly DATETIME() data-type thankfully still supports.
And then there are performance nightmares like the implementation of rCTEs that increment a count in anything but a true set based manner behind the scenes (a well formed while loop beats it and uses 9 times fewer logical reads) and the miserable performance of FORMAT(), which I've tested to be no less than 44 times slower than a good ol' CONVERT. Don't get me started on the idiocy of PIVOT. They should contact the ACCESS group and find out how it should really run. Even when they do come up with useful additions to T-SQL, you have to be very careful to test it because they followed the wrong rules because "People want it real bad" and that's the way they give it to us... real bad.
While I agree that MS is a business and not a charity and so marketing decisions do come into play, I've thought that MS has been out of touch on the T-SQL side of the house for quite some time. ROW_NUMBER() was brilliant but things like rCTEs, FORMAT, PIVOT, the splitter function and the various windowing functions they added without having LEAD/LAG capabilities were all put together in a mostly thoughtless hurry to say that the "have it". They need to start thinking about how much better it would be for marketing if they took on the attitude to reach the end goal of "we have it and it's not only done right but it's also nasty fast". If you're not going to do it right, don't do it at all.
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)