July 8, 2017 at 5:04 am
Comments posted to this topic are about the item The Panjandrum Conundrum
Best wishes,
Phil Factor
July 8, 2017 at 5:48 am
July 8, 2017 at 6:19 am
I think that the track record of changes to T-SQL shows that the rate of change is less radical than that. Mind you, there is a Graph Database going into SQL Server 2017 so maybe the sages have smiled on the idea. I can't imagine that it would be the high priority of many of the PASS members.
Best wishes,
Phil Factor
July 10, 2017 at 8:24 am
peterw 85974 - Saturday, July 8, 2017 5:48 AMI think you'll find the great sages are concerned that if you let popular opinion influence your decisions you end up with idiocy like NoSQL.
So do you think that the new graph database features in SQL Server are idiotic? How about xml features in SQL?
July 10, 2017 at 8:25 am
Careful challenging the wisdom of the gods.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 10, 2017 at 8:39 am
Microsoft probably also has to take into consideration the costs of implementing new features, and I'm betting that IIF was relatively easy to put in compared to other functions.
July 10, 2017 at 12:35 pm
SQL is a lot like HTML, I'm sure they don't want to get too far ahead of the ANSI standards and end up with a lot of proprietary extensions in the T-SQL language that are soon not needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 10, 2017 at 1:06 pm
I just started playing around with SQL 2017 and was enthusiastic about TRANSLATE and STRING_AGG. STRING_AGG is long overdue and performs a wee-bit better than using the FOR XML PATH('') method and much better when using the FOR XML PATH('') with TYPE (to prevent against special XML characters).
TRANSLATE is a joke - the 2nd and 3rd parameters must be the same length which is a huge limitation. I wrote a translate function some time back. You'll notice that, in the code comments, I include some examples of what you can do with the function when the 2nd and 3rd parameters are not the same length.
Concat_WS, like CHOOSE and IIF, is convenient but basically adds nothing.
-- Itzik Ben-Gan 2001
July 10, 2017 at 1:43 pm
Alan.B - Monday, July 10, 2017 1:06 PMI just started playing around with SQL 2017 and was enthusiastic about TRANSLATE and STRING_AGG. STRING_AGG is long overdue and performs a wee-bit better than using the FOR XML PATH('') method and much better when using the FOR XML PATH('') with TYPE (to prevent against special XML characters).TRANSLATE is a joke - the 2nd and 3rd parameters must be the same length which is a huge limitation. I wrote a translate function some time back. You'll notice that, in the code comments, I include some examples of what you can do with the function when the 2nd and 3rd parameters are not the same length.
Concat_WS, like CHOOSE and IIF, is convenient but basically adds nothing.
I think its a matter of subjective opinions with programming who has the better version, and silently removing characters like yours does turns it into a function that does multiple tasks and I'd probably wonder about that. After all, aren't you able to use "replace" to remove characters?
edit:
I know its not a huge thing, but it seems like a very arbitrary thing to remove characters and would probably "surprise" developers who mistakenly didn't give parameters that matched and that hits my "least surprise" wishlist with programming features. Other choices of course would be to truncate parameters enough to match, or maybe raise an error like substring does when presented with nonsensical parameters.
Also, why do you think its a huge limitation to require matching parameters? Maybe I could be missing something by not having your explanation?
July 13, 2017 at 1:23 am
Once you've worked in a large hierarchical structure you realise that hell isn't fire and brimstone, it's being teased for ever with the promise that a simple decision with an obvious answer that will alleviate your suffering will be delivered tomorrow.
It works thus.
Now you run into utilisation problems.
If you are 50% utilised then 50% of the time people are going to be waiting for you to become available.
If you are 75% utilised then 75% of the time people are going to be waiting for you to become available.
If you bump decisions up the chain of command higher and higher you reach the point where the person who eventually grows a pair and makes the decision is utilised 99.9% of the time. Add all that together and a 10 minute conversation and decision can take months!
In such a hierarchy I reached the point where being told "NO" sent me away with a happier feeling than being told that the decision was progressing.
July 13, 2017 at 8:38 am
and everyone loves the String_Split() function in SQL Server 2016
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2017 at 9:30 am
@jeff
OK. I should have said 'everyone except perhaps Jeff.'
For everyday work, I think String_Split() is a godsend because it is so easy to use, I agree that the ordinal information would have been good, but I reckon that if you want an ordered list you might as well use XML lists. After all, sets have no intrinsic order. The general case you make is great, though I'll never agree that syntactical candy like IIF is ever a good idea. Your contribution confirms my view that the Panjandrums are rather out of touch and that a group of PASS experts like Jeff Moden would do a radically better job.
Yes, recursive CTEs are a terrible mess. I always feel sorry for anyone who inadvertently tries to use them, I hadn't run any timings on the format command but it doesn't surprise me that they are so slow considering the overhead.
Best wishes,
Phil Factor
July 13, 2017 at 12:39 pm
Phil Factor - Thursday, July 13, 2017 9:30 AMOK. I should have said 'everyone except perhaps Jeff.'
...but I reckon that if you want an ordered list you might as well use XML lists.
😉 Maybe that's fine for passing multiple parameters to a stored procedure but it's a performance nightmare when handling a large amount of data in batch runs. And sets having no intrinsic order is exactly why the new function is useless to me. There are some sets where order (even if it's just left to right in a string of delimited elements) is indeed important even in passing parameters especially if you have paired parameter strings or nested element strings.
...my view that the Panjandrums are rather out of touch and that a group of PASS experts like Jeff Moden would do a radically better job
Thank you for the compliment but the Panjandrums are already listening to PASS "experts". If you look at the built in Tally CONNECT item I was speaking of, you have such "experts" offering "work arounds" that undermine the original request. You also have nay-sayers that suggest that doing direct temporal math like EndDateTime-StartDateTime = Interval is some sort of T-SQL sin and worst practice even though it's clearly stated as such in the ANSI/ISO standards.
We need people that understand that they in a box before they can think outside the box. Until that happens, the Panjandrums will continue in their self-inflicted ignorance.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2017 at 10:04 am
I get the impression that Jeff spends a LOT of time splitting strings.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 17, 2017 at 10:42 am
Eric M Russell - Monday, July 17, 2017 10:04 AMI get the impression that Jeff spends a LOT of time splitting strings.
Heh... that might be what we're fighting with MS... a bunch of people that think its only utility is in splitting strings. That might be a part of why MS finally came out with a string splitter, which also doesn't meet a lot of folks requirements.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply