Blog ala Vidar

Have any of you ever had problems with understanding a query because of the (lack of) indenting? I’ll bet the answer is “Yes, MANY times!”.

Today, as you’ve probably already understood, I’m going to take a look at how easy indenting is, and how much time it saves you, me, Bill Gates and all other poor creatures. Some may call me strict when it comes to indenting, but I think it’s a good thing. I’m not saying everyone should follow my standard. I just want to spread the word and make people understand.

We can start with an example from an old view in AppFrameR2 (aviw):

Before:

select d.title as "teamdoctitle", e.*,
(select count(*) from stbv_calendar_events e2 where e2.copiedfromeventref=e.copiedfromeventref and e.copiedfromeventref is not null) copycount,
cast(case when exists(select * from stbl_calendar_attendance where login = suser_sname() and eventref = e.primkey) then 1 else 0 end as bit) as userattend
from stbv_calendar_events e (nolock) left outer join stbv_teamdoc_documents d (nolock) on d.primkey=e.teamdocref where e.primkey = @primkey

After:

SELECT D.Title AS TeamDocTitle, E.*,
  (SELECT COUNT(*)
        FROM stbv_Calendar_Events AS E2
        WHERE E2.CopiedFromEventRef=E.CopiedFromEventRef
          AND E.CopiedFromEventRef IS NOT NULL) CopyCount,
  CAST(CASE WHEN EXISTS(SELECT *
                            FROM stbl_Calendar_Attendance
                            WHERE Login = SUSER_SNAME()
                              AND EventRef = E.PrimKey)
                    THEN 1
               ELSE 0
               END AS BIT) AS UserAttend
    FROM stbv_Calendar_Events AS E WITH (NOLOCK)
    LEFT OUTER JOIN stbv_TeamDoc_Documents AS D WITH (NOLOCK) ON D.PrimKey = E.TeamDocRef
    WHERE E.primkey = @PrimKey

As you see, it’s difficult to see what’s SQL and what’s not. It is also very hard to know where the subqueries end.

Imagine you have a MUCH longer query. Sviw_MSAccessCR2_MyMenuObjects for instance. In this view you have alot of case, subqueries (with joins) and both inner and outer joins. Try reading that without indenting, syntax highlight and uppercasing SQL-commands! That’s not even possible! About a year ago we had some performance issues with this view. I remember my thoughts:
– ”EHM! Where do I start?!”

It was about 13 kilometers longer than the one today, it had alot of unions and other ”fancy” stuff. I think I used almost a day only ”getting to know” (indenting/casing) the view! If possible, take a look at it, remove all indenting and change all text to lowercase (mark it and press ctrl + shift + L). Copy it to notepad and try to figure out where the subqueries end, where each case ends etc. It’s almost impossible.

Very often I get questions like ”Could you help me with a SQL-query?” on mail and/or MSN. To understand the logic, I always start with indenting (if it’s not already indented). I’ll bet $100 that most of these questions wouldn’t be asked if the person already had indented the query, because it’s so much easier to find the issue with, than without.

It takes me more time to write a query WITHOUT indenting and casing than WITH, because I’m so used to this method

.. and it’s MUCH more difficult to read. My advice: Use indenting and uppercase on SQL-commands. If it’s ”my” standard or some improved (if that’s possible 😉 standard does not matter. ANYTHING is easier to read than one-line-plain-sql-queries!