SQL rules to live by

[szlamany]

Techgnome asked in another thread for a list of our company SQL conventions. We have these 10 or so Commandments we live by.

I decided to start a new thread so others, if interested, can share theirs.

If you don't mind me asking, could you share them?

Tg

No problem - and a little explanation as well.

1) Never use SELECT DISTINCT

Sloppy joins fixed with SELECT DISTINCT are a pitfall of SQL. COUNT(DISTINCT) has merit. In 3 years of QUERY writing (nearly 750 stored procedures) I've never seen a real need for it. GROUP BY is a more controlled method of achieving "distinct" results.

2) Always ALIAS tables with 2 or 3 character names and USE STANDARDS

Makes for "ripping" JOINS and other stuff from existing SELECTS easier.

3) Always "SCRIPT"

Using ENTERPRISE MANAGER to make even the smallest change to db, table, sproc or UDF is a dead-end as far as distribution and tracking. We also wrote a little SQLSEARCH program that searches all the .SQL files (WINDOWS won't search them - it doesn't think they are TEXT files) for a keyword along with a simple click the filename and into a rich-text-box display for looking at these TEXT files.

4) Always "GRANT"

Obvious reasons - we don't allow any direct TABLE access by users - only SPROC access anyway...

5) strSQL = "Select ..." & vbCRLF _
"...From..." & vbCRLF

This is obsolete for us now - we don't do IN-LINE SQL QUERIES with ADO - all SPROCS. But back in our salad-days, when we did QUERIES in VB, having the VBCRLF made printing the strSQL string in the immediate window easier to read/copy/paste - silly stuff like that.

6) SELECT CONVERT(CHAR(23),DATEFIELD,121) for milliseconds

We want all DATES to come back to VB as STRINGS

7) Check SERVER DATE/TIME and LOCAL DATE/TIME upon connection

Never fully developed this one - just know we hate when CLIENTS connect with wildly different LOCAL times - makes timestamping and other activities like that difficult.

8) INT is BAD - LONG is GOOD - use LONG in VB

We only like a couple of datatypes - we use SQL-data types of VARCHAR, CHAR, DATETIME, MONEY, INTEGER and TINYINT only. Supporting more than this with more than one developer is a struggle. TINYINT makes sense for little values - otherwise go for the full longword.

9) SELECT CONVERT(CHAR(10),DATETIME,101) for MM/DD/YYYY

If someone forgets this, you end up with a date in a flex grid of 9/9/2004, instead of 09/09/2004. We allow users to enter 09092004 and put the slashes in ourselves - so even one time giving them 9/9/2004 confuses the whole point.

10) Always use UNION ALL

UNION without ALL does a DISTINCT automatically - you loose common rows. Slower and dangerous for obvious reasons.

11) Always specifiy SET NOCOUNT ON in a SPROC

Not doing this, and then adding an INSERT into a TABLE VARIABLE later on will make a subsequent SELECT not return the recordset. You have to process the "INSERT rows affected" result to get to the recordset - very ugly bug to introduce.

12) Soon to be added - always specify DBO. for tables, view, functions and SPROCS.

I would love to hear from others.