Home Uncategorized The SQL Genie (T-SQL Tuesday #086)

    The SQL Genie (T-SQL Tuesday #086)


    Genie in a BottleAt some point a long time ago, when I was first getting serious about SQL Server, I became aware of an open secret.

    You see, there was this special Microsoft alias called “sqlwish.” Those in the know could send an e-mail and pass along suggestions for improving the product. And they wouldn’t just vanish into thin air; some actual member of the product team on the other end would not only read the suggestions, but also add them to the internal Official List of Stuff to Get Done.

    How exciting! A chance to make my mark on the product I worked with day in and day out. I immediately put together a list of my wishes and sent an e-mail. And over time, I sent a few more. I never heard back from anyone, so I have no clue if my missives were actually read. And while I can’t remember all of the things I asked for, I’m pretty sure none of them were ever implemented. (I can clearly remember only one request from that very first e-mail. I’ve asked for it again numerous times over the years and still want it today. No luck.)

    Fast-forward a few years and Microsoft introduced the Product Feedback Lab, a web-based solution for user feedback collection. The sqlwish alias was retired and now we could submit feedback in an open, transparent way. We were told by Microsoft that not only would someone read these items, but now we would actually get status updates and replies! An amazing idea. And it went well for a while. Users loved the Lab, populated it with lots of items, and Microsoft soon promoted it to a permanent home in a place called Connect.

    Alas, the powers that be at Microsoft seem to have severely under-estimated the amount of work and attention this endeavor would require. Members of the product group got busy or moved on to other things and the replies stopped coming as often. Users lost enthusiasm. And time simply passed, as it does. After a while Connect fell into a state of abandonment. The SQL Server items have occasionally seen periods of promising forward movement, as well as at least one horrible stage during which the product group indiscriminately closed virtually every open item. But generally speaking the site is mostly considered to be a joke by the SQL Server professionals I’m friendly with. It’s insanely slow, has an unbelievably awful UI, and most of the submitted items are eventually “resolved”—if they ever are—with the infamous “Won’t Fix” status. (Said resolutions are almost never accompanied by a comment from Microsoft or anyone else.)

    All of which doesn’t mean that Connect is completely useless. As desperate as we often are to see change in certain areas, Connect has become an amazing catalog of user stories. It’s one of the only mechanisms for most people to voice concerns about the direction the product is taking, and it turns out that DBAs have quite loud voices. Some tickets actually do go through the system and end up in that exalted state of having been “Fixed.” One such case that I will never forget occurred early on, when Hugo Kornelis demanded the release of SQL Server 2005 SP3. Over 800 people joined him and the service pack was released. A true win for and by the SQL Server community.

    But today’s post isn’t about victory. Today’s post is about a dream.

    T-SQL TuesdayThis month’s T-SQL Tuesday challenge, issued by Brent Ozar, is simple, vague, and frustrating: find an interesting item on Connect and write about it. The problem for me is that I can’t find just one interesting item; there are simply too many from which to choose. But sometimes when I’m both especially bored and feeling a bit depressed about SQL Server I’ll peruse Connect by using one of its only good features, the ability to sort the items by “most voted.” (Note that even this feature doesn’t even work properly; the sort order is only kinda-sorta accurate.)

    What if, I wonder, Microsoft took a step back and actually listened to the voice of the masses? What if Microsoft actually committed to implementing the top voted items on the list?

    That’s exactly what would happen if Microsoft put me in charge. (Imagine that!) Looking over the most voted—but still open—items, and sorting the wheat from the chaff just a bit (I only want legit developer features), I could come up with a pretty amazing release.  Here’s the session I would present at PASS just before Microsoft fired me for not filling the bits with enough marketing fluff:

    SQL Server v.Next.Next++: Developer Enhancements That Will Completely Change Your Life

    Hyperbole? Maybe just a little bit. But check out some of these amazing features…

    First and foremost, I would eliminate the biggest debugging time waster in the history of the product. (Bad error messages!) That sounds nice. Nine years and 1300 votes, for those keeping score.

    I would so, so love to see this virtual table of errors that would completely revolutionize relational ETL. I’ve even written about it before. (Three and a half years ago. Sigh.) 600 votes over four years.

    Regular expressions. I have to admit that I absolutely hate them and even when I work in C# I often find some other way to solve my problems. But the built in LIKE/PATINDEX patterns just don’t cut it. And yeah, you can implement some SQLCLR solution for this, but it should just be built in by default. Only 475 votes in the past 10 years, but I guarantee this would be an amazingly popular feature among the hardcore developer set.

    Scalar functions are awful. It bears repeating. Scalar functions are awful. Just for good measure, maybe once more. Scalar functions are awful. But they’re so incredibly tempting! Encapsulation and re-use simply make development better, more scalable, more manageable. If only scalar functions weren’t so very awful. Fix them? Yes. Please. Now! 560 people over the past 10 years have agreed with this sentiment. I bet that number would be much higher if the item was not somewhat strangely worded.

    I use a lot of derived tables in my T-SQL. I bet you do, too. I spend so much time writing so very many derived tables. And they bloat my statements to ginormous proportions. But I have no choice! I need to be able to use expressions as filter qualifications. Oh wait, there’s an amazing solution for this problem, suggested by Itzik Ben-Gan. 340 people over the past seven years have ticked the upvote button. Maybe Microsoft would have implemented it were it suggested by someone who actually knows T-SQL pretty well. The world may never know.

    Table-valued parameters are one of those things that I was so excited about when they were released. And I told myself that typical Microsoft lie. “It’s a v1 thing. It will be improved over time.” Well, that hasn’t happened. And this is a feature so very ripe for improvement. Removing that READONLY restriction would mean the difference between today’s “meh” and an absolute developer productivity masterpiece. Nine years. 320 votes.

    How many times have you been asked by end users to change the order of columns in a table? “But tables are sets, and sets have no explicit order.” Guess what? Users don’t care. We waste so much time on this and SQL Server could so easily support changing the order. And this item asking for a feature to do it has been in the system so long (almost 12 years) that when you click on it you don’t even see the item, you get an error. (When Microsoft migrated the items from Product Feedback Lab to Connect, something got messed up and a large number of items went into this limbo state. Perhaps someone got a column out of order? It’s been 10 years since that transition and I’m not expecting a fix anytime soon. But you can still see the number of votes on the main list: 270. I’m sure making these items almost completely unavailable did them no favors in terms of votes.)

    NULLs. What’s the first thing you think of? 3VL? You’re a geek. Annoyance over subtle bugs? You’re quite correct. And while there are some pretty brilliant workarounds to the various NULL issues, the fact is that the ANSI standard includes a clause specifically dedicated to eliminating a huge subset of the problems. Why, after nine years and almost 300 votes, do we not have this?

    Magic numbers! That sounds cool. Except it’s not. It’s a horrible drain on readability and a pox on the manageability of your code. But we need them, because every database out there has “lookup tables” with specific values that we need to predicate on throughout the code base. SQL Server could solve this problem once and for all by allowing users to promote these values to named enumerators. How cool would that be?!? 220 votes, nine years.

    And finally, one more from Mr. Ben-Gan, a clever idea for improving TOP with the OVER clause. I love the OVER clause. I love TOP. Their offspring would be beautiful indeed… 180 voters over the past nine years have shared in this sentiment.

    Quite a list, isn’t it? I think it would in fact change the entire developer experience for the better. But these items are old, and these numbers aren’t too impressive. 180 votes, really? There are probably a hundred thousand SQL Server DBAs and developers out there. The community, it seems, has no faith in Connect. People have neither time nor inclination to navigate its painful interface just to click the vote button on an item that will sit untouched for a decade. It’s a sad state of affairs.

    Eternal optimist that I am, I still hit Connect from time to time. I still vote, and I still submit my own items. I remain ever hopeful. Microsoft, if you need a new product head and fall guy, just for one release, I’m waiting by the phone…

    And just for the record, the number one item in my very first sqlwish e-mail, and something I’d still love to see today? Implementation of DOMAINs, an ANSI-standard feature that would bring the type system out of the Dark Ages and into the 21st Century. Here it is on Connect. “Won’t Fix.” The genie clearly slept through that one.

    Previous articleT-SQL Tuesday Rules of Engagement
    Next articleSQL Server 2016 “Hidden Gems” Resources
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.


    1. @George
      Actually the SSMS team has been BY FAR the most responsive on Connect in recent years. Try posting an item on that, if there’s not one already there. And if there IS one already there, maybe consider posting a new one. Sometimes the really old ones get lost in the shuffle.

    2. The OVER clause one did get a response. In 2007, it said it would be prioritized for the next release.
      Seriously: great post Adam. I’m sure I’ve got a pile of ones that I feel the same about but these are a great start.
      Partly, it’s been an attitude thing. I remember a discussion at a pre-release session where a PG member asked me "T-SQL enhancements? Why would you want any? Isn’t it "done"?" Sadly, he was serious.

    3. @Greg
      Sadly, I remember at least two PG people saying the same thing when I was in the room. One guy I’m thinking of was very briefly head of the programmability team and mentioned in front of a large group of MVPs that he was interested in getting rid of T-SQL altogether, and making everyone use LINQ. That was not met with much approval 🙂 … the other person was an executive-level guy, who said almost verbatim what you’ve quoted. After hearing that from him I actually seriously started considering focusing on a different database platform. Luckily, he was gone shortly thereafter and I decided against changing my career path. So far so good 🙂
      So very, very many things. It’s truly sad. But I hope that much of that ridiculousness was due to Ballmer-era politics and the need for people to "make a mark" on the product in order to be properly stack ranked. From what I heard, enhancements were frowned upon during that period. I think that’s not so much the case today, but time will tell…

    Comments are closed.