Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Two Best Practices!


Two Best Practices!

Author
Message
linto
linto
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 5
For the second best practice, i would like to add

Give proper alignments or Indent while writing the joins to make the query more readable.

Linto



jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 18
I liked the article...I am amazed that people still use pre ansi-92 syntax...it has been over 10 yrs already.

In addition to using ansi join syntax, I like to move the where clause restrictions to the join level where applicable...that way all of my join information is in the same spot.



David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3174
quote:
For the second best practice, i would like to add

Give proper alignments or Indent while writing the joins to make the query more readable.



Nice in theory, but it relies on everyone's Query Analyser being set to use the same font and font size.

I've got both Enterprise Manager and Query Analyser using the same font and size but they still show indents at different positions.

I've found that leaving blank lines between key blocks of SQL aids readability For example

FROM dbo.Customers AS cust
INNER JOIN dbo.Orders AS Ord
ON cust.Id = Ord.CustomerId

INNER JOIN dbo.OrderDetails as Det
ON Ord.Id = Det.OrderId



========================
He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile

Newbie on www.simple-talk.com
lansley
lansley
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 38
I second the recommendation on using ANSI joins. It's much clearer.

However, it's important to remember that for outer joins the results may not be the same between ANSI and non-ANSI syntax. For ANSI syntax, the join logic is applied before the filter logic. For non-ANSI joins, the filter logic is applied before the join logic.

Many articles have been published describing this.

Because of this, I am always suspicious of the creators intent and/or skill when viewing non-ANSI joins.

Larry Ansley
Atlanta, GA


Larry Ansley
Atlanta, GA
Philip Kelley
Philip Kelley
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 232
I'd like to add to all the above threads.

I tend to use two characters for table aliases; this generally works, because some tables pop up in almost every query, and you get quite familiar with them. For subqueries and other complex abstractions, I tend to use short words or phrases to make it clear what the subquery's doing.

The specifics are vague and abstract, but there are certain forms or conditions where a (left?) outer join cannot be properly performed using pre-ANSI-92 [ANSI 89, right?] syntax. Exclusively using ANSI-92 avoids ever even having to think about this. (This issue is documented somewhere in BOL.)

For spacing, I always use non-proportional fonts (for any coding job) and spaces for indentation, and that you can cut and paste most anywhere with impunity. I've developed a complex and rigorous code layout convention... but, when it comes right down to it, everyone has their own systems and standards, and getting anyone to agree on anything is about as productive as arguing religeon. Devise something that works for you, get others to use it if you can, adapt those parts of their conventions that make sense, and be prepared to be flexible. (I will say that I'd never add blank lines within a single T_SQL statement, no matter how long. I use blank lines to tell when one (long) statement has ended and the next one has begun.)

Rather than "tit", would "git" be at all appropriate? (From and American who watched loads of Monty Python at a young and impressionable age...)

And you missed "rouding out" the Hitchhiker's Guide / Database analogy. The immortal who'd set about insulting everyone (third book?) showed up early on and insulted Arthur Dent... and then showed up at the end of the book and did it again, only to realize "Wait a minute, didn't I already do you?" implying that his list was corrupt and he'd probably have to start all over again. Dude should have normalized his database...

Philip



David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3174
quote:
Dude should have normalized his database...


Probably bought the list from a spammer!

========================
He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile

Newbie on www.simple-talk.com
skawczynski
skawczynski
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
I agree with the second best practice but feel that the code example is still somewhat hard to read. I like to format select statements as follows:

select distinct
clo.clone_id,
clc.collection_name,
clo.source_clone_id,
clo.image_clone_id,
lib.library_name,
lib.vector_name,
lib.host_name,
loc.plate,
loc.plate_row,
loc.plate_column,
clo.catalog_number,
clo.acclist,
clo.vendor_id,
clc.value,
lib.species,
seq.cluster
from clone clo
inner join collection clc on clo.collection_id = clc.collection_id
inner join library lib on clo.library_id = lib.source_lib_id
inner join location loc on clo.clone_id = loc.clone_id
inner join sequence seq on clo.clone_id = seq.clone_id
where clc.short_collection_type='cDNA'
and clc.is_public = 1
and clo.active = 1
and clo.no_sale = 0
and seq.cluster in (select cluster from master_xref_new where
type='CLONE' and id='LD10094')



Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40524 Visits: 18846
I have to admit that I tend to use single character, sometimes 2 char aliases, but they have some bearing to the tables. For example, I might use

select *
from products p
inner join orderline oi
on p.productid = oi.productid
inner join orders o
on oi.orderid = o.orderid
inner join customers c
on o.custtid = c.custid


As mentioned above, the same tables appear often, so you get used to p for products.

ANSI syntax, while strange at first, is 1000 times better than the old syntax.

I always use non proportional or fixed width fonts as well and spaces instead of tabs so that the code looks the same on my machine as yours. And vice versa, which is usually the more important one BigGrin

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
dlhatheway@mmm.com
dlhatheway@mmm.com
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 12
When I wrote the article, I originally included some very brief remarks on formatting but the article was starting to get rather long, so I focussed on the two Best Practices that would make my life easier and I cut the references to formatting. If something shows up in front of me that looks ugly merely because the spacing and tabbing is poor, that's easy to fix if I feel it's necessary in the situation. Fixing up table aliases and separating join conditions from "this query" where restrictions is MUCH more time-consuming.

I believe an earlier reply had it right: develop a style but, as for imposing it on others, it's like arguing religion. Our local SQL Style Guide simply tells the individual programmers to develop some sort of style and stick with it. When I'm looking at something someone else wrote, I can usually get along OK as long as it has SOME sort of style.

My experience has been that most programmers seem to develop some sort of spacing/tabbing style of their own without being told to and that it's usually readable.

I also like fixed fonts for coding.



David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3174
I tend to use short meaningful abbreviations, which is fine for databases with a few tables, but if you get an app with thousands of tables then your abbreviations cna only be local to the view, function or proc.

I remember looking at an SAP application, where there were tens of thousands of tables. The names of the tables themselves were pretty bloody meaningless in the first place.

========================
He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search