Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We've long had coding standards for our .Net code, and there seem to be several reputable sources for ideas on how to apply them which evolve over time.

I'd like to be able to put together some standards for the SQL that is written for use by our products, but there don't seem to be any resources out there on the consensus for what determines well written SQL?

share|improve this question
    
Pinal Dave has a list of coding standards on his site. They look like a fair basis for a set of standards. –  Will A Jan 17 '11 at 15:05
    
There is a related question on SO. –  Scott Whitlock Jan 18 '11 at 17:54
    
@Scott that only covers identing; nothing about naming, use of cursors/stored procedures/choices of datatype or anything that actually affects the quality of the code... –  Rowland Shaw Jan 18 '11 at 18:06
    
exactly, hence why I said it was "related", not a "duplicate". –  Scott Whitlock Jan 19 '11 at 14:41

3 Answers 3

In addition to Jon Hopkins' answer...

  • Separate internal vs external objects

    • IX, UQ, TRG, CK etc for constraints and indexes etc
    • lower case or CapsCase for client facing eg uspThing_Add
  • For internal objects, make them explicit if "non default"

    • UQ = unique constraint
    • UQC = unique clustered constraint
    • PK = primary key
    • PKN = nonclustered primary key
    • IX = index
    • IXU = unique index
    • IXC = clustered index
    • IXCU or IXUC = unique clustered index
  • Use schemas to simplify naming + permissions. Examples:

    • Helper.xxx for internal procs
    • HelperFn.xxx for udfs
    • WebGUI.xxx for some facing code
    • Data and/or History and/or Staging for tables
share|improve this answer

In my experience the main things I'd look for would be:

  • Table and column naming - look at whether you use ID, Reference or Number for ID type columns, singular or plurals for names (plurals being common for table names - e.g. THINGS, singular for column names - e.g. THING_ID). For me the most important things here are consistency which avoids people wasting time (for instance you don't run into typos where someone has put THING as a table name because you just know intuitively that table names are never singular).

  • All creates should include a drop (conditional on the object existing) as part of their file. You might also want to include grant permissions, up to you.

  • Selects, updates, inserts and deletes should be laid out one column name, one table name and one where clause / order by clause per line so they can be easily commented out one at a time during debugging.

  • Prefix for object types particularly where they might be confused (so v for view being the most important). Not sure if it still applies but it used to be inefficient for stored procedures other than system procedures to begin sp_. Probably best practice to differentiate them anyway usp_ was what I've used most recently.

  • A standard indicating how the name of a trigger should include whether it's for update/insert/delete and the table it applies to. I have no preferred standard but this is critical information and must be easy to find.

  • Standard for ownership of objects in earlier versions of SQL Server or the schema it should exist in for 2005 and later. It's your call what it is but you should never be guessing who owns something/where it lives) and where possible the schema/owner should be included in the CREATE scripts to minimise the possibility of it being created wrongly.

  • An indicator that anyone using SELECT * will be made to drink a pint of their own urine.

  • Unless there is a really, really good reason (which does not include laziness on your part), have, enforce and maintain primary key / foreign key relationships from the start. This is after all a relational database not a flat file and orphaned records are going to make your support life hell at some point. Also please be aware that if you don't do it now I can promise you you'll never manage to get it implemented after the event because it's 10 times the work once you have data (which will be a bit screwed because you never enforced the relationships properly).

I'm sure I've missed something but for me they're the ones that actually offer real benefit in a decent number of situations.

But as with all standards, less is more. The longer your coding standards, the less likely people are to read and use them. Once you get past a couple of well spaced pages start looking to drop the stuff that isn't really making a practical difference in the real world because you're just reducing the chance of people doing any of it.

EDIT: two corrections - including schemas in the ownership section, removing an erroneous tip about count(*) - see comments below.

share|improve this answer
    
Some strange choices... "SELECT COUNT(*)" is bad? Ever heard of schemas (which is not the same as owner)? Your others are good though –  gbn Jan 17 '11 at 19:50
    
@Jon Hopkins - I know why its bad to use SELECT *. It would be great if you could say why using SELECT COUNT(*) is bad. –  k25 Jan 17 '11 at 21:00
    
@gbn @k25 - A few years back (2002?) I had a DBA who was very hot on count(*) but Googling in response to your questions it seems that this is now outdated (if it was ever true). sqlservercentral.com/articles/Performance+Tuning/adviceoncount/… (Registration required). She was primarily an Oracle DBA so it may have been a genuine issue there which she assumed was also an issue for the SQL optimiser. –  Jon Hopkins Jan 17 '11 at 21:32
    
@gbn - Yes I have, though I've been relatively hands off since they were introduced so my automatic reaction was users. I'll update the answer to cover schemas. –  Jon Hopkins Jan 17 '11 at 21:34
    
@gbn, @k25 - More digging on count(*). Apparently this was an issue in Oracle 7 and earlier, fixed in 8i and beyond. Not clear if it was ever an issue in SQL Server but certainly isn't any more. My DBA was out of date it would seem. –  Jon Hopkins Jan 17 '11 at 21:38

there don't seem to be any resources out there on the consensus for what determines well written SQL

That's because there is no consensus. Just as an example, I would have different answers for at least half the items in Jon Hopkins' list, and based on the amount of detail on his list, it's a safe guess that we both work with databases for a living.

That said, a coding standard is still a good thing to have, and a standard that everyone on the team understands and agrees with is a better thing, because that standard will more likely be followed.

share|improve this answer
    
+1. I think the most important thing is that you've got consistency among your team. –  Dean Harding Jan 17 '11 at 23:12
    
out of interest what would you do differently? Are they largely matters of taste (layout and so on) or are there any "hard" errors? –  Jon Hopkins Jan 18 '11 at 9:44
    
@Jon: no hard errors, just subjective things like singular table names, hatred of triggers, etc.. BTW, "SELECT *" is fine inside an "EXISTS()". –  Larry Coleman Jan 18 '11 at 13:19
    
fair example (and I do use it with EXISTS and don't force myself to drink urine). –  Jon Hopkins Jan 18 '11 at 14:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.