October 14, 2009 | SQL Server

Bad habits to kick : using the visual designers

In my last post in this series, I talked about abusing triggers.  This time I thought I would focus a bit on using the visual designers in SSMS.
 

Table Designer

My biggest problem with the Table Designer : many changes you make can force SQL Server to rebuild the table.  Obviously this can be a very expensive operation if the table is of significant size.  In SQL Server 2005, you could do this unwittingly — commit some change and then try to figure out why you get an hourglass for 20 minutes and three tickets about app responsiveness.  That the "designer" pumping your data into a second table, then dropping the original table, then renaming the new one — all in a transaction, of course.  In SQL Server 2008, they mitigated this somewhat by adding an option called "Prevent saving changes that require table re-creation," and enabling it by default.  Now at least you will get a warning when making a change, which shouldn't require the table to be rebuilt, will (such as making a column NULLable).  So you can go change the option, and then re-save your change, and it will re-build the table for you.  But what if you forget to set it back?  Later on you might make some innocent change to a table and then wonder why it's taking 6 hours to commit.  There are several other potential problems in the way the table designer unnecessarily rebuilds tables; for example, you can lose Change Data Capture configuration settings, and you can lose filtered indexes.

There are 33 active bugs and suggestions on Connect that mention "table designer," including the two above.  Because a lot of these problems will likely not be fixed for several versions, I strongly recommend either becoming a DDL master and scripting all of your table changes directly, or at least getting into the habit of using the Table Designer only to generate your scripts — which you can then review / correct / store in source control, and execute independently.  You'll find that many of these changes do not require a table rebuild at all, and that the DDL command will save you much time and aggravation.

Query Designer

The Query Designer is far from perfect.  Like the Table Designer, the Query Designer can lead to some very interesting and unexpected results.  I talked before about using the schema prefix; the Query Designer simply drops it.  This means if you take a query that references dbo.Customers, then "design" the query, you may end up with a new query that pulls data from a different table altogether (depending on the context of the caller, of course).  Steve Kass has posted several (and much less obvious) examples where a query that returns one result gets pasted into the Query Designer, re-worked automatically, and suddenly it returns a different result.  That Connect item is said to be fixed, but I'm not convinced that it has been completely addressed.

Anyway, there are 51 bugs and suggestions that have the phrase "query designer" in them, including Steve's bug.
 

View Designer

If you are using the 2005 version of SSMS, you can right-click a view in Object Explorer and choose "Design" from the context menu.  Not that you *should*, but you could.  I recommend against it.  Instead, use the menu option Script View As > ALTER To > New Query Editor Window.  The biggest beef I have with the View Designer is that it encourages the use of "features" like SELECT TOP 100 PERCENT … ORDER BY, proliferating the myth that users can then use SELECT * FROM this_view and the results will always be ordered in their defined order.  It also doesn't support syntax highlighting at all, which can make it a little more challenging to read and maintain the object if you are used to having those visual cues.  There are several other problems with the View Designer; most notably, it carries forth all of the same bad query re-writing bugs that are present in the Query Designer.  Surprisingly, there are only 9 active bugs, and 2 suggestions.
 

Edit Top n Rows

Technically, this isn't a visual designer, but I think it falls under the same type of feature : you use a grid to modify data directly, like a spreadsheet, instead of using proper DML statements.  I understand the purpose behind it; it is often much easier to identify the row you want to edit visually than trust an UPDATE statement with a WHERE clause.  You should just be aware that plenty of things can go wrong, and there are several cases where you will have to revert to an UPDATE statement anyway.  A pet peeve that has long plagued me in this case (and with the previous feature, "Open Table"), is data entry involving the BIT data type – instead of typing 1 or 0, you are expected to type "true" or "false" – blecch!  There are 32 other bugs and suggestions revolving around this feature.

Summary

I realize that a lot of people just aren't proficient with T-SQL (or DDL), and asking them to avoid visual designers altogether is like asking me to build a RAM chip… there will be much banging of the head on the desk and not very much in the way of decent results.  What I suggest for people in this category is to use the visual designers to craft the original query, but simply use it to learn from it.  Paste the start of the query into a real query editor window, and get familiar with the syntax.  Before too long, you'll be working without a crutch at all.

User interfaces are supposed to create less work for people; in all but the simplest cases, these designers often create much more work (or worse).  I really don't want to crap on the folks responsible for some of these tools in the Management Studio UI.  But they really have some major issues to fix before I can possibly recommend that people use them at all.

I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code.  Up next: using alias types.

4 comments on this post

    • Brian Tkatch - October 15, 2009, 4:55 PM

      @Aaron, good points.
      I use the visual stuff for convenience only. Like marking a COLUMN as NULLable for a moment, or a COLUMN rename.
      In general the DDL is better. If only to truly understand what is being done.

    • Atif Shehzad - October 17, 2009, 10:42 AM

      Using T-QSL is much efficient when working with large number objects. In case of table designers in SSMS 2008, we have the option to prevent changes that may cause table re creation. It may be allowed or restricted. http://www.mssqltips.com/tip.asp?tip=1740

    • Shivani - November 2, 2009, 5:06 PM

      Hi,
      Nice post.  Well I'm reasonably good in DMLs and DDLs, and I normally use them, but I cant resist myself using GUI for operations like Restore/Backup.  Is the GUI equally bad for this operations as well?
      Thanks
      Shivani

    • David - January 25, 2010, 3:54 PM

      There's a View Designer in SS2008?  Oh, so there is!  Thanks, I didn't know you'd get a Designer if you selected the New… option on Views.
      I immediately found it useful for learning how to set up views.  But don't worry, it's the SQL it generates that I'll use, and alter/improve as necessary, as I understand SQL better.
      And there's my point, really: these tools are very useful for people new to SQL Server, or some of its features.  They help us get started, and show us what correct (if not optimum) SQL looks like for lots of standard tasks.  I can see though that they can become counter-productive if a more experienced person uses them as a crutch to avoid learning how the system works in more detail.

Comments are closed.