Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Falling Over our Assumptions

By Phil Factor,

Often, the mistakes that are made in SQL code seem to be so perverse that one is left scratching ones’ head and wondering whether there is a basic incorrect assumption underlying all the errors.

I think one of the most insidious assumptions is that each SQL Statement takes the same length of time to execute. It is crazy, I know, but we are talking about unconscious, impulsive judgments here.  When I’m taking to developers who are gridlocked with a complex routine, I always advise them to break the problem down into a number of simple testable steps; using temporary tables for intermediate results.  They hate it. Often, the problems have happened because they’ve gone headlong into an attempt to get a complex result in one SQL Statement.  ‘Why create so many statements when one will do? , they often say.

When I had less self-confidence as a SQL Programmer, I’d break problems down into simple steps, test each step for sanity, logic and performance, and then re-write them in as few statements as possible, using derived tables and subqueries. Nowadays I don’t always bother with the last step, because the contribution that this step makes to the performance of the entire process  is usually negligible. It just looks better.  I have my pride; well I used to.  The advantage of a series of simple steps is that it is easier to test, to maintain, and to understand.  The disadvantage is that many developers will whistle through their teeth and say ‘Cor, I bet that runs slow!’, and try to rewrite it in one statement.

We all approach our development work armed with poorly tested assumptions, and a lot of the excitement of the job is in testing them and finding them wrong. However, if you keep them for too long, you’ll soon find that they get in the way of the quality of your output.

So what other assumptions do we fall over?  Bearing in mind General Sedgewick's fatal assumption, "Why, my man, I am ashamed of you, dodging that way,  They couldn't hit an elephant at this distance.",  here are a few assumptions that can cause a lot of subsequent problems

  • 'My routine will scale in a linear fashion'
  • 'If I test this in a single-user database, it is likely to work in a multi-user one.'
  • 'It is always quicker and easier  to do stuff in C#'
  • 'I can always shave time by denormalising'
  • 'Joins cause performance problems'
  • 'get something up and running. We can always tidy it up later'
  • Come across any other fatal assumptions recently in the office? It would be great to hear them.

Total article views: 164 | Views in the last 30 days: 1
Related Articles


For this Friday poll, Steve Jones asks about assumptions that cause you problems at work.



Comments posted to this topic are about the item [B]Assumptions[/B] good morning.... assumptions... ...



We had another snow storm in Denver this weekend. We woke up Saturday morning to howling winds a...


Falling Over our Assumptions

Comments posted to this topic are about the item [B]Falling Over our Assumptions[/B] "Don't bother w...


Risk and Assumptions

Comments posted to this topic are about the item [B]Risk and Assumptions[/B] [quote]I would be worki...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones