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?
Best 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.