Conventions when writing queries/stored procedures

Question

Conventions when writing queries/stored procedures

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

I don't see any benefit to the extra lines. Just takes up space and results in more scrolling.

[These] [brackets] [also] [add] [to] [clutter]. I use them sparingly.

SELECT
    Date
FROM
    Staff.LeaveRequests
WHERE
    Id = @StaffId

(oh no ! This is the dreaded 16th post...LOL)

This reply was modified 1 year, 3 months ago by homebrew01.
This reply was modified 1 year, 3 months ago by homebrew01.
This reply was modified 1 year, 3 months ago by homebrew01.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

I don't think using trailing commas over leading commas because that is how English is written is good reasoning. The Language is SQL, not English.

When you are modifying existing code, the start of two lines is where you will have to start looking for your modification and you will be able to find both of them without ever scrolling. It also makes it easier to comment out big sections of your select statement if you only want to look at a few columns and you can do it without scrolling. Or to copy an expression that you approximately know the location of that is a huge mess of functions and you can do it without having to mentally parse the expression trying to find the end.

I absolutely despise aliasing by adding the alias to the right, prefer using AS for columns over [alias] = [column], as I don't care what the alias is at the time of writing or modifying code, I care what the table column name is, I only want the column name after I have run the query. I like that aliases can be quickly found by searching for AS [alias].

I always bracket everything after dealing with enough aggravation from poorly written applications that contain periods, spaces, quotes, brackets etc (worst was something like [dbo].[[sys].com.Download"client files"]) and prefer SQL to be consistent everywhere.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Heh... all of this reminds me of the great "Toilet Paper" debate... Unroll from the top or the bottom? Of course, there are people like me that prefer a vertical dispenser that dispenses from the side that is furthest away from the wall that the toilet is backed up to. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 4

Thanks to World War II, and modern newspapers, we have a lot of research on making typography readable. The University of Maryland also did studies on making code readable many years ago.

Putting a keyword on each line makes your eyes jump from line to line. It also destroys the unit of text, called a Bouma, that you used to process text. I covered a lot of this material in one of my books, but basically, write, programming languages in Boumas and put your punctuation marks where they would normally go in text. That means commas and semicolons go to the end of the line. Younger programmers probably don't know why we put commas at the front of the lines back in the old punchcard days. This was handy so we could reuse and rearrange decks of punch cards back in the 1960s! Sure it added about 12% to your maintenance, but you didn't have to wait for your time on a keypunch machine.

I also prefer the <expression> =<alias> format because it's actually standard SQL and not the proprietary Sybase stuff that uses an =.

Please post DDL and follow ANSI/ISO standards when asking for help.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 5

If the comma is off the screen, the shape of the bouma doesn't matter, its off the screen and code is not read like a narrative.

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 6

We actually found that code is weirder than text. Text is pretty much read in a very straight left-to-right, top to bottom pattern of eye movements. Furthermore, proportional-spaced typefaces with serifs are easier on the eyes. This is one reason that Times New Roman has become a standard typeface in academia. International Paper Company used to give away a plastic card called an "alpha caster" with which you measured the length of an alphabet in a given font. It then gave you the optimum column width in picas for a column of text.

But code has a strong top-to-bottom eye movement pattern. We found that code was better set in a mono font and that we wanted to put rivers in the text of the code. Indentation over three spaces resulted in extra eye movement. Back when we had paper printouts, splitting on a page break slow down everything. When we got video screens, that could scroll, these problems went away. I don't know what research on left or right scrolling on a screen showed; we just didn't have it back in those days as we do now.

A "pretty printer" was an incredibly useful tool. But you had to be sure that you ran everybody's codes through it before you archived it or showed it to other programmers. The goal was to make it look as if one person with one style manual had written everything in the system. Again, I don't know how old you are, but we had what we called "job secure programming" in the old days. Every programmer was free to arrange his code any way he wished.; Even worse, he could be creative about variable names and such like. I actually worked with a COBOL program created by a man who thought it would be fun to name the paragraphs in his code. After the names of countries (perform Afghanistan. Go to Argentina. Etc.)

Please post DDL and follow ANSI/ISO standards when asking for help.