You have an Enter Key. Use it!

It is a well known fact that long lines are hard to read. So don't be afraid of using your Enter Key. Take these examples:

-- Not the best way:
select ItemCode, Description from Items

-- The Better Way:
select i.ItemCode, i.Description
from Items i
where i.Assortment = 3000
and i.Type = 'S'
order by i.ItemCode

Here's my favorite way of using the Enter Key:

Select plus fields on one line
Exception: the line should fit within view without scrolling horizontal. In that case, keep Columns that belong together on the same line.
From should be on a new line.
Any join is on a new line.
Where plus first constraint is on a new line.
Any additional constraint is on a new line.
This also makes it easy to comment out a single Constraint.
Group by is on a new line.
Order by is on a new line.
Having is on a new line.

Yes, this can make your Query quite long and some might disappear behind the Results Pane. Making the Results Pane smaller is an option but hey, you want to see the results as well.

Don't worry, just use Ctrl+R to hide the results.

To show the results again, just hit Ctrl+R again. Or just run the Query again and there they are back!

Pick a Style. Be Consistent.

Take a look at the following:

-- My favorite way:
select i.ItemCode, i.Description
from Items i
where i.ItemType = 'S'
order by i.ItemCode

-- Not my favorite way, but still good:
SELECT i.ItemCode, i.Description
FROM Items i
WHERE i.ItemType = 'S'
ORDER BY i.ItemCode

-- Please not like this:
select i.ItemCode, i.Description
FROM Items i
WHERE i.ItemType = 'S'
Order By i.ItemCode

My favorite way is to write keywords in lower case because it is easier on the eyes to read.

Other people insist on using upper case.

Microsoft SQL Server doesn't care if you use upper or lower case for Keywords. But other Database Management Systems seem to work with upper case. So to be on the safe side, upper case might be a better choice.

Since I only work with Microsoft SQL Server, I prefer lower case. Ah well, living on the edge, huh?

Indentation is your best friend.

By using indentation, you can easily see what belongs together. Check it out for example with this Query with a Case Statement:

select i.ItemCode, i.Description,
case
when i.Type = 'S' then 'Standard'
when i.Type = 'P' then 'Phantom'
end as Type
from Items i
inner join ItemPrices ip on i.ItemCode = ip.ItemCode

Because I used indentation here, it is easy to see that there is something going on here.

It is also easy to see where the Case starts, how many cases we have and where it ends.

And it is easy to see that there is no Else in de Case Statement. If there are any other values than S and P, we might get Null values.

You can use the Tab Key to do indentation. I also use indentation with more complex Subqueries:

-- Like this:
select i.ItemCode, i.Description
from Items i
where i.ItemCode in (
    select r.artcode
    from frhsrg r
)

-- Or like this:
select i.ItemCode, i.Description
from Items i
where i.ItemCode in
(
    select r.artcode
    from frhsrg r
)

Again, it is easy to see that there is something going on. Also it is easy to read, and therefor to understand the Subquery.

Especially with the second example it is easy to see which Brackets belong together. People who don't use the Entery Key and proper indentation often run into problems because somewhere in a Query an opening or closing Bracket is in the wrong position or missing.

Don't be that person, or don't ask me to help you.

My rules for indentation:

The When's within a Case Statment get indentation
Everything within a Begin and End gets indentation
Everything within a Subquery gets indentation
A Case Statement or a Subquery within another?
Use indentation again.

Too many colums for one line?

So you run into a case where you have many columns and it no longer fits on a line without the need to scroll horizontally? Here are 2 good options, one bad option:

-- My favorite way:
select i.ItemCode, i.Description,
i.SalesPackagePrice, i.CostPriceStandard,
i.syscreated, i.sysmodified
from Items i

-- Not my favorite way, but still good:
select i.ItemCode, i.Description
, i.SalesPackagePrice, i.CostPriceStandard
, i.syscreated, i.sysmodified
from Items i

-- Someone shoot this guy:
select i.Description, i.SalesPackagePrice
, i.CostPriceStandard,
i.syscreated, i.ItemCode, i.sysmodified
from Items i

Here it is about the colon. I prefer to have it on the end of the line. Others might prefer to have it at the begining of the next line.

Both are fine to me, even if I prefer the first one. Pick the one you like best and be consistent with it.

The third example shows how not to do it. First of all the use of the colon is not consistent. Secondly, the Columns are just grouped well.

In this case I would say keep the descriptive Columns together. Then the Columns that have to do with pricing. Finaly the Columns that give some System Information. It just makes it so much easier to find the Column you need if you need to make changes.

That's it for today

Someone once said to me: Write code that is easy to maintain, because the person that has to maintain your code later on might be a psychopath on a bad day with an axe who knows where you live.

That is so true, but also do it for yourself.

Yes, it is true that there are online tools (like https://sqlformat.org/) that will do the work for you. Use such tools to format Queries from others if necessary.

But writing well formatted Queries yourself is definitely the best way. It makes your SQL life so much easier and once you get used to your own style, it is no effort at all.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!