DELETE 5_Useless_Things FROM [SQL Server]

It’s been a while since I’ve been caught up in a round of chainblogging, the blogosphere’s version of a Facebook meme. This time, Denis Gobo tagged me in a post started by Paul Randal. Paul asked us to list the “top-5 things in SQL Server we all wish would just be removed from the product once and for all.” I reviewed other posts, and the good and bad news is that they already listed several of the same things I would have. The good news is I’m apparently not alone; the bad news is that means I need to come up with something original! So while these wouldn’t necessarily be the *first* 5 on my list, they’d still be on the list nevertheless:

Default Autogrowth Options
Okay, so I lied. I’m not completely original. Yes, I know Paul Randal also commented on this one. While I said I would try to come up with only original ones, this one just has to be repeated. I’ve actually this option overlooked in production environments, resulting in thousands of VLF’s. It’s just a terrible default, and it needs to be changed.

Edit Top 200 Rows
This “feature” is just asking for trouble. Any DBA who is managing a SQL Server database should understand how to actually write insert/update/delete statements. Maybe leave the option available in SQL Express, but please remove it from SQL Server Standard & Enterprise.

There’s nothing wrong with the Debug option, but I think it should be removed as a default option for the toolbar. It’s easily mistaken for “Execute,” which I’ve seen more than one DBA do on occasion.

I understand the need to pivot your data, but let’s face it. PIVOT is a clunky, expensive SQL operation. Let’s move the presentation tasks to the presentation layer (.NET), and reserve the database layer for what it does best.

Update: By popular demand, I have removed PIVOT from this list. Who am I to argue with such fine folks? šŸ™‚

Okay, okay, I know I can’t actually get rid of this, BUT I think it gets abused way too much. Set-based operations, anyone?

Alrighty, now it’s my turn to tag! I’m not sure if they’ve already been hit, but I’m tagging:

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged . Bookmark the permalink.

18 Responses to DELETE 5_Useless_Things FROM [SQL Server]

  1. SDC says:

    I agree 100% on PIVOT. It isn’t ‘pure’ from a SQL point of view.

  2. Brandon Reno says:

    I’ve used PIVOT and UNPIVOT on more than one occasion for loading a data warehouse. it’s not always just display-layer logic.

    now, that being said, i could use pivot/unpivot transformations in SSIS to accomplish the same thing, but i’ve noticed it to be considerably faster if done in SQL.

  3. Mike Walsh says:

    Actually mad at myself for not getting original and thinking of a few of the ones you mentioned!

    Edit is bad enough in SSMS, let alone Edit Top 200… NO! If you have a database in SQL Server, you should be able to write a basic insert or update statement šŸ™‚

    Also a GREAT point about the Default Autogrowth. I focused on shrinkage but those darn growth settings are something to banish also. Or at least make a mandatory configuration after an install or make the default better for more “responsible” growth.

  4. Denis Gobo says:

    I actually use pivot to transfer market data into a file and values for each index go into their own column

    prefer pivot over case when then else end

    Yes debug is a pain in the neck but I also think most people press F5 instead of the toolbar

    I just wish they added CTRL + SHIFT + C and CTRL + SHIFT + R shortcuts back to comment/remove comments from code in SSMS

  5. Cade Roux says:

    PIVOT and UNPIVOT are incredibly useful in my processing.

    We have what we call base statistics (which are stored normalized for flexibility in adding new stats on a regular basis):


    And then derived statistics which are very complex formulas. So I have to PIVOT to be able to get all the values on a row, then do the calculations and then UNPIVOT to insert the derived statistics in a table.

    There is no way I want to pull 37m rows of base stats out and then calculate and insert another 100m rows of derived stats.

    The alternative within SQL – using CASE or multiple self-joins is far too wordy and difficult to maintain – as I’m finding attempting to convert this process to Teradata (at least Teradata allows one common-table expression to be able to set up my data before I have to keep repeating the code to do UNPIVOTs)

  6. dbajonm says:

    i gotta disagree on the PIVOT. as a couple other comments here state, this can save a tremendous amount of time when loading a data warehouse. this week i utilized the UNPIVOT to remove three UNION statements and reduce the load time from 22 minutes to 3 minutes.

    i’m glad that you mentioned the EDIT TOP. that just sounds wrong — too much like access! šŸ™‚

    great post!

  7. Wes W. says:

    The “Edit Top N Rows” adjustable in SSMS, but default is 200. This feature is actually very useful. I mostly use the feature to edit status messages that are retrieved to appear on a system status webpage. Since I don’t normally recall what the last messages were/are, this is helpful to see and edit all in one step as nothing more elaborate is necessary. I’d agree though that this would be a bad idea for updating more critical data.

    PIVOT can be useful in a few circumstances where the front-end doesn’t allow manipulation. Rather than remove the feature, it’s fine when the dba/developer understands the limitations.

    I highly doubt you’ll find anyone defend default Autogrowth, or as other’s posted on the same topic, Auto Shrink on a production system. However, I can see both having possible use on a developer’s laptop, again an experienced dba should know better.

  8. Claire Streb says:

    I frequently use top 200. We have some small lookup tables, and the top 200 is only two keystrokes. I don’t see the harm in keeping it. I think it was a good idea.

  9. Ian Posner says:

    Want to get rid of something that will make a real difference? Try tempdb. Replace it with a temp filegroup per database and eliminate contention.

  10. jonmcrawford says:

    You could also get a mouse that allows you to assign macros to buttons, I have mine assigned for execute and adding/removing comments, so I don’t use the keyboard or toolbar for any of those.

  11. Linda says:

    >>I just wish they added CTRL + SHIFT + C and CTRL + SHIFT + R shortcuts back to comment/remove comments from code in SSMS<<

    They are there, but like many things MS does, they like to hide change them up and hide them.

    Comment = CtrlK + CtrlC
    Uncomment = CtrlK + CtrlU

  12. I also want to speak up in defense of PIVOT.

    Here’s an example of a scenario where I like to use it:

    I have a “report runner” application which I wrote to re-run a set of user reports on a backend database server while it’s offline. It logs report runtime to a table. We can change configuration and then rerun the set up reports to test the impact.

    To look at the results, I have a nice, simple PIVOT query to tabulate all the results. It could be done with case statements, but it’s much simpler and easier to edit with PIVOT. It works really well for my purposes.

    I do agree that it may not be appropriate for all use scenarios, but like anything else, it’s the developer’s responsibility to make sure application code can work towards the intended scale/capacity.

    In other words, please consider sparing PIVOT a little luv for quick, adhoc reports.

  13. By popular demand, I have removed PIVOT from the list. Thanks, everyone, for your comments! šŸ™‚

  14. Faamasani Messenger says:

    This is the first time I’ve read this particular blog series and I know I’m a little late to the party on this post. You have great tips and methods and I especially like the fact that you graciously retracted your PIVOT comment. Before I go offending people I would like to state that I’m all about set based operations. That being stated, Iā€™d like to defend the use of STATIC cursors as they can be quite useful and fast. Brad Schulz did a great three part series on cursors that caused me to give cursors another look and I encourage everyone to do the same.

  15. Chris Lively says:

    I disagree with you on the Edit Top 200 rows issue. Sure I can write the insert statements, I can write assembly to but you won’t find me doing that either. This little window makes it much easier to copy / paste large amounts of rows from excel or even another SQL server.

    And before you say that’s what SSIS or the Import Data function is for, let me just say that the import feature is the biggest pain in the butt I’ve ever seen.

  16. IPFREELY says:

    I agree Chris
    but ever copy and paste into the top 200 a HUGE chunk of data, only to discover a hidden column in the excel sheet. Now THAT is a pain in the arse

  17. Roy says:

    Edit 200 is a really useful feature for people who just want to do a select and an update all in one place. It’s like a GPS while driving. Imagine how difficult driving was when you had to keep looking at a map everytime you needed to turn. Sure, once you’ve navigated that route enough times, you don’t need the map, but when you’re new and need to learn the ropes, it’s most helpful.

    Similarly, with Edit 200, you get to see the data, and change it – all in one fell swoop. I would recommend showing the SQL on top so you can change which rows are picked without having to click the [View SQL] and then modifying the SQL.

  18. Pingback: Something for the Weekend - SQL Server Links for the week 16/05/10

Leave a Reply

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