What SQL Server Feature Do You Wish Would Go Away?

Humor
45 Comments

I asked a two-part question on Twitter: if you could only remove one feature from SQL, why would it be cursors?

The responses were great:

Justin’s referring to MERGE’s hot mess.

But out of all of ’em, this one intrigues me the most:

How about you?

Previous Post
Query Hints You Can Use to Avoid Blocking
Next Post
Free Webcast: Help! My SQL Server Maintenance is Taking Too Long!

45 Comments. Leave new

  • Wesley Crockett
    January 22, 2021 9:17 am

    I responded with “select statements” though mine was, clearly, a joke.

    As far as John’s response:
    Aren’t SSIS and SSRS technically “free” in that you can run them on any number of machines… the only one that needs to have paid licensing is the server that is running whichever SQL instances? I honestly hate that they split off SSRS from the main install suite… It seems more of a pain to upgrade and keep up to date now.

    Reply
    • If SSIS is installed on a server, that server needs to be licensed for SQL Server. I don’t believe it needs enterprise edition licenses though so you might be able to get away with standard on servers where you don’t have SQL installed.

      Reply
      • Wesley Crockett
        January 22, 2021 9:52 am

        Oh interesting. I thought it just could only connect to licensed servers. Good thing I just run it on my primary DB servers…

        Reply
  • I would say ‘Database Engine Tuning Advisor’, sometimes people following it blindly.

    Reply
  • Ability to store images in the database (whether of image type or varbinary max).
    1 200 Gig table full of pdfs in a 400Gig database makes many things needlessly complicated.

    As with regard to SSAS/SSRS/SSIS, they are certainly not the future with Power Bi/ADF. And I’ve used SSRS extensively. But I get that all you want is a database, it seems like your paying for something you don’t want. (They aren’t free, they are included. Like the T-shirt the tour company gives you on vacation).

    And I want my cursors. Don’t use them for data manipulation, but do use them for more administrative level things.

    Reply
  • There is a lot of good and bad in sql server. Fully separating SSAS, SSRS, and SSIS and adjusting the price of SE AND EE is possible, makes logical sense, allowing Microsoft to enhance each based on the votes of people via orders. Don’t get me wrong each product has a purpose and need, but how is Microsoft determining the amount of the expensive cost cost to the side car products? With any luck it will deduce the number of people that want to run it all on a single VM.

    Reply
  • I manage a 13 TB DWH database with tables containing multi billion of rows.

    Without cursors (of course not on row basis but e.g. over dates (day or month) or import file ids) I would not be able to handle serveral tasks because you just can not simply copy 100 billons of rows at once (or at least you shouldn’t, it would blow up your log file and it would be extremly annyoing if there occurs an error after 4 h processing in the one of last few records that causes a HUUUGE rollback (and rollbacks takes usually much longer than the operation itself).

    Reply
  • So tell me .. how are you going to access data in another disparate SQL environment withOUT a linked server ?

    Pretty stupid idea

    Reply
  • Yea.. how do you expect to get data from different separate data silos ( different SQL Servers ) to create the data warehouse ? Without using a “linked server” ?

    Reply
    • Wesley Crockett
      January 22, 2021 12:39 pm

      SSIS would be a good start… unless that was a rhetorical question. We do a ton of this and I refuse to set up linked servers.

      Side note: use the reply button to reply to an existing thread.

      Reply
      • SSIS also adds considerable time to development and for some reason seems completely alien to a lot of people for how easy it is to use.

        I myself, am pretty good with SSIS, but my SSIS jobs are all heavily driven by stored procedures run in an execute TSQL step and only use SSIS for things it is objectively better at, like derived columns, XML, dealing with other media types like other DBMSes, flat files, etc. It’s faster to develop TSQL than SSIS packages, its faster to run, you don’t deal with SSIS buffers, and can be updated without writing the query somewhere else, pasting it into visual studio and then deploying. Its also quicker to commit changes to a text file to TFS than a query step in visual studio. I can also delegate some tasks to someone else who can write me a query that I can put into a job somewhere else, instead of needing to be the only person who does everything for SSIS. Nevermind applications and reports that use lookups in a sql server dedicated to one application by application requirement with dynamic data that you could never extract and store with enough freshness.

        Constrained Kerberos delegation is not hard to configure and a proper architecture avoids a maze of linked servers.

        Reply
  • Not a good answer Wesley

    Reply
  • Ok .. you have your reasons .. I have mine .. SSIS is to limited ..

    Reply
  • Full Text, xml indexing, geo

    Reply
    • Geo spatial data can be important. For my previous employer (a billboard advertising company) I used them to find the distance between billboard or to ensure that there are no tabac ads placed next to a school or kindergarden. Of course you could do it with your own C# / .NET routines (and this is what geospatial do internal), but why should I reinvent the wheel every time?

      Reply
  • Brian Boodman
    January 22, 2021 1:56 pm

    Excluding the MSDN documentation, is there any otherwise-competent DBA who suggests that auto-shrink should *ever* be turned on?

    I can only think of a two legitimate reasons to use it:
    • Working with a partner who requires it to be on (if anyone is in that unfortunate position, I’d love to hear your story!)
    • In a non-production database, for pedagogical reasons.

    Reply
    • Only thing I have seen are some databases on very limited, super fast SSD that cycle through a datawarehouse job, expand and then empty out before going on to the next. ideally they’d just have enough space to run every day, but hundreds of thousands of dollars of SSD may not be in the budget.

      Reply
  • Johnny Wyskiel
    January 22, 2021 2:07 pm

    Filestream/Filetables – Just use a *real* object based storage system (S3 / Azure blob)

    Reply
    • David Wiseman
      January 22, 2021 2:29 pm

      Yes! Please. 🙂 Maybe add VARBINARY(MAX). Luckily I got to remove 160TB of BLOB data from one DB. Yeah!

      Reply
  • David Wiseman
    January 22, 2021 2:42 pm

    Maybe it’s time to replace read committed with RCSI as the default isolation level. Not sure if this counts as removing a feature?
    Cursors I would keep – some legitimate uses though it’s open to abuse. DEVs kind of seem to be aware of cursors = bad for the most part. Scalar functions – less so. Maybe remove NESTED cursors! Give us nested INSERT EXEC instead. 🙂

    Reply
  • I wouldn’t wish for any particular feature to ‘go away’ – every feature is a tool that (in most cases) can be leveraged for a particular circumstance; – you either choose to use it or not, and whether you do comes down to experience. People hate on cursors and I never use them, but part of the learning process of how and why to avoid them is to be stung by them.
    On the other hand are there features I wish they had not bothered to include and focussed time and resource elsewhere instead – that’s a different question!

    Reply
  • Stephen Morris
    January 25, 2021 12:48 am

    Autoclose is a particularly bad idea, I only used it once to try and find out if a database was in use (which it was), but that wasn’t the bad part, during the autoclose process the database got corrupted – just as well I’d taken a backup.

    Reply
  • My two most-hated keywords in SQL: UNION and DISTINCT.

    I wouldn’t remove them, because they have legitimate usefulness. I’d just change the query optimiser, so that any plan generated for a statement that contains them spits out a message something like “Do you actually know what (TF) you’re doing, or are you just throwing SQL at a wall until something sticks?” Maybe with an animation of Brent wagging his finger.

    If QueryStore stored login information, could integrate that into this new feature to make it more selective. “Sorry, your last 10 queries tied up 90% of the server memory for 3 hours, each. I’m not running this one, no sirree. Maybe you’d benefit from one of Brent’s free courses?”.

    All too often, DISTINCT means “oh dear, I’m getting duplicates. Dunno why, stick a DISTINCT in there”. And UNION means

    SELECT bla FROM MassiveHeap WHERE [some logic]
    UNION
    SELECT bla FROM SameMassiveHeap WHERE [some marginally-different logic]
    UNION
    SELECT bla FROM LetsHammerThatHeapAgain WHERE [more logic]
    UNION
    SELECT bla FROM YepThatSameHeapAgainWhyIsThisTakingHoursToRun? WHERE [more logic]

    Reply
    • I’m going to have to disagree with you on UNION – it can be a really useful performance tuning technique, and I talk about that in Mastering Query Tuning. If you have two sargable things in your WHERE clause, like WHERE DisplayName LIKE ‘Alex%’ OR Location LIKE ‘India%’, and SQL Server refuses to use the DisplayName and Location indexes because it (incorrectly) thinks it’d be past the tipping point, you can break them into two queries with a UNION that will each use their respective indexes.

      Reply
      • Pavel Nefyodov
        February 4, 2021 9:42 am

        I tested the above case (just to check if UNION actually makes any positive difference).
        SELECT [DisplayName]
        ,[Location]
        FROM [dbo].[AAATest]
        WHERE DisplayName Like ‘Alex%’
        OR Location Like ‘India%’

        Does index scan.

        SELECT [DisplayName]
        ,[Location]
        FROM [dbo].[AAATest]
        WHERE Name Like ‘Alex%’
        UNION
        SELECT [DisplayName]
        ,[Location]
        FROM [dbo].[AAATest]
        WHERE Location Like ‘India%’

        Does two index seeks. However, the latter query also does DISTINCT sort which makes it more than four times more ‘expensive’ than the former query.
        Did I miss something in your explanation?

        Reply
    • I’d disagree on union as well. As Brent pointed out, if you are taking data from one table using several separate filters you can start losing use of indexes. It can also just be used for code tidiness if the set of filters are particularly complex, you can break each select statement into separate sets of logic that won’t break when you need to make a change to something else.

      I also use them extensively in ETLs where I take data from several separate sources to be combined into a single final reporting product. (HR and time card data in particular)

      Distinct I can get on board with a bit more, but I also find it useful, again in ETLs. I don’t care how costly it is to run a query using distinct when I only run it once or maybe a few times per day, and in cases where the data is being significantly transformed, it is vastly more efficient to use a distinct that to group by function1(x,y,z), function2(1), function3(y) etc, or to write the data to a temp table first and then read it out again and use group by to get rid of duplicates.

      Reply
    • UNION by itself is not an issue, but I saw a lot of people who needed UNION ALL, but forgot the ALL. How many times did I explain that UNION means in fact “UNION DISTINCT”?

      Imagine if it was the same with SELECT: SELECT / SELECT ALL instead of SELECT DISTINCT / SELECT. It would be a nightmare? So, why UNION doesn’t work the same way?

      Reply
    • Douglas Coats
      January 28, 2021 8:50 am

      Union is an awesome approach to dealing with convoluted predicates and honestly with dense WHERE requirements its waaaay easier to read in a UNION format as opposed to janky OR’s. I actually have a tendency to break up any query with OR in the WHERE clause just to see if performs better as an UNION. Its probably a bad habit though.

      Reply
  • DELETE statement not requiring you to type something like DELETE ALL when you actually want to delete all the rows. How many countless people have been burnt by forgetting to add their where clause..

    Reply
    • Daniel Johnson
      January 26, 2021 7:42 am

      I agree. If the delete statement doesn’t have a where clause or join it should just not work. More often than not you are NEVER trying to delete the whole table and if you are, use a truncate statement.

      On the off chances you need to delete the entire table, use where 1=1.

      Reply
  • I’d vote for (NOLOCK).

    “READ UNCOMMITED” is a little more alarming, and would appear much less in queries (at least at my company).

    Reply
    • +1

      seeing nolock hints all over the place makes me want to punch a baby lamb. I’d love a query hint that causes it to not interpret

      Reply
      • Stephen Morris
        January 26, 2021 5:31 am

        Keith > like a readuncommitted snapshot isolation level RUSI , go for broke and make it turn transactions under both nolock/readuncommitted and read committed use snapshot isolation

        Reply
  • SQL Server Licensing…

    Reply
  • read uncomitted isolation, SQL authentication, CEIP, MS Access functions.

    Wouldn’t get rid of cursors. I have a few uncommon production things where they are useful and have also used them for some administration things.

    Reply
  • If we could ADD something… why don’t they add “Clippy”?

    Reply
  • Feature difference between Enterprise edition and other editions.

    Reply
  • User defined data types begs to differ. Think of the pain of changing the size of a data type on a large database.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu