SQL rules to live by

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.

Quote:

---

Originally Posted by techgnome

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.

We generally use SET NOCOUNT ON in SPs.

Also, avoid the usage of NULL values in a table's field. Instead, let it be an empty string or if it's numeric, let it be -1. NULL has some adverse effect on paging which is undesirable. Not sure about the details but just do as I say. :wave:

other point that may interest you

use ntext,nvarchar,nchar instead of text,varchar,char when u r storing any language besides english.

We had the most weird problems when we were developing multi language application. Due to different server collations.

well the n stands for uni-code which prevents language problems and allow storing data with any language without even configuring the server for each language.

RGDS

What's wrong with SELECT DISTINCT?
I work with tables that have, for example, date and month fields. What if I just want to know what months are in the table, or what dates a month has? How would I get that without using SELECT DISTINCT?

VB Code:

SELECT DISTINCT fldMonth FROM tblFOO
SELECT DISTINCT fldSvcDate FROM tblFoo WHERE fldMonth = 'January'

I know these are simple queries, but so what? Maybe they'd be used to populate a combobox, for example.

Quote:

---

Originally Posted by salvelinus

What's wrong with SELECT DISTINCT?
I work with tables that have, for example, date and month fields. What if I just want to know what months are in the table, or what dates a month has? How would I get that without using SELECT DISTINCT?

VB Code:

SELECT DISTINCT fldMonth FROM tblFOO
SELECT DISTINCT fldSvcDate FROM tblFoo WHERE fldMonth = 'January'

I know these are simple queries, but so what? Maybe they'd be used to populate a combobox, for example.

---

Your examples are a proper use of DISTINCT - or of GROUP BY - you would get the same results.

But just as I said in the rule itself...

Quote:

---

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.

---

I've had many inexperienced coders come through my shop, and found that the use of SELECT DISTINCT hides poor joins. One ambiguous join can double the rows presented and the DISTINCT flattens them back out again.

I prefer to develop my queries and see all rows represented and as a final step add a GROUP BY to flatten the results. Prior versions of SQL favored GROUP BY, as rows would not get inserted into the working results set if already there - DISTINCT would flatten after the result set was complete.

That no longer happens - so in reality the two are the same in that regard.

But GROUP BY allows aggregate functions...

Do you really find that you use DISTINCT that much? Are your JOINS very complex?

I can't say I use DISTINCT with joins that often, or at all. But there's plenty of times I only want the user to be able to choose dates or months that actually exist in the table, and thus my example of the combobox, or listbox, listview, whatever. Why would I want to do a GROUP BY? That's not what I'm interested in.
There may be some kind of reason. I appreciate your help & it's clear you're way beyond me on this stuff. My design philosophy is the KISS model, for various reasons. I try to design things to avoid joins, for example, although that's just an ideal. I have plenty of 'em, know that it's one of the advantages of relational systems, etc. My stuff is all in (small) house, and I need to keep things where others can understand.
Thanks for your insight.

Follow up to #2 there.... after aliasing a table, USE that alias in the SELECT part of the statement. Nothing gets me more than when there's more than 3 tables joined and none of the fields have their table alias prefix. I can't possibly know the field content of every table, especialy if they are inner joins. Use that alias and let me know WHERE that field came from. Otherwise I'm just guessing.

Tg

Quote:

---

Originally Posted by techgnome

Follow up to #2 there.... after aliasing a table, USE that alias in the SELECT part of the statement. Nothing gets me more than when there's more than 3 tables joined and none of the fields have their table alias prefix. I can't possibly know the field content of every table, especialy if they are inner joins. Use that alias and let me know WHERE that field came from. Otherwise I'm just guessing.

Tg

---

I totally agree - and to add to that...

When I add a JOIN to an existing query that wasn't aliased the way it should be, I'm all of a sudden responsible for clearing up all the new ambiguities - that drives me nuts!

szlamany,

I don't really agree with #5, on the grounds that I dont beleive your sql should ever be constructed at run time. The sql statements should always be read from some sort of resource repository.

This has the following benefits:
1) Enforces the use of sql parameters. This is the single most important benefit. Using string concatenation always lends itself to lazy programming and concatenating the parameters into the sql, leaving you wide open to sql injection attacks. Not to mention the fact that it degrades your db performance.
2) Provides a central location for all sql statements.
3) Allows easy maintenance. You can simply copy from a query analyser and paste it into the resource location. It will also exist in the same format as you initially wrote the sql, and dont forget, you wont have to put in all that string concatenation code.

Quote:

---

Originally Posted by shunt

I don't really agree with #5

---

He does actually say that you should not use it.

Quote:

---

This is obsolete for us now - we don't do IN-LINE SQL QUERIES with ADO - all SPROCS.

---

Quote:

---

Originally Posted by shunt

szlamany,

I don't really agree with #5, on the grounds that I dont beleive your sql should ever be constructed at run time. The sql statements should always be read from some sort of resource repository.

---

And in our shop that repository is stored procedures on the server. Haven't written an in-line SQL statement in several years - actually converted all our old "standalone" VB clients to use stored procedures.

We have what we feel is an unique take on n-tier design because we have:

1) UI tier - Extremely lightweight VB client. Users can experience dozens of different maintenance forms, all drawn at run time with textbox and flexgrid names, size, positions and attributes all "read" from a FORM table on the MS SQL SERVER database.

2) Business tier - all interaction between UI and DB is through an extremely controlled and standardized set of stored procedures. Named in a fashion as to self-bind to the VB FORM NAME, VB OBJECT (textbox or flexgrid for example - even grid column/cell). All SPROCS have a standard 9 or so parameters to allow for this binding to the UI - so that UI state is driven by the SPROCS.

3) DB tier - simple old MS SQL server.

Granted this is a twist on the n-tier model - we are tied to MS SQL, we cannot relocate our business tier. I've argued my position on this in several other threads...

But we can whip up a fully functional maintenance form with absolutely no VB code being written, by making a couple of entries in a FORM control table and writting a VIEW, INQUIRE and UPDATE SPROC.

Hello! Quite new to all this so hope you wouldn't mind explaining more about using DBO? Thanks!

DBO is DataBase Owner, and "dbo" is the standard owner of objects in each database (the SQL Server tools show an "owner" column next to the table names).

The reason for Rule 12 (always specify DBO) is that different people can create different objects that have the same name - so for example person A creates a table called "Table1", then person B comes along and creates a "Table1"; the names are identical, but the owners are different. If you use just Table1 in your SQL statements, you don't know which one you will get.

If you also specify the owner of the object (table/view/..), you know you will always get the right one - even if somebody else creates an object with the same name later on.

And speed - without DBO the "schema owned by the calling database user is checked first" - then DBO

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ba06f43a-a6a6-48e3-886a-707fe4226858.htm

OK. So i did find a simple tutorial suggesting the same thing (I think he called it "four-part" something). What I'm not sure now is I'd use dbo.School.SchoolID in all stored procs, do I need to change anything else in the system I am developing (VB6,ADO,SQL Server)?

SQL Server Profiler

If you write a lot of SQL/SP etc in SQL Server, then this should be one of the tools you should get to know very well. (if you dont already)

@cantinamosh - keep in mind that dbo. and schemas in general have a purpose.

As Si indicated a few posts back - objects are searched for in schemas in a certain order. There could be a benefit to your app by not putting a schema - in that you could have an installation at one customer site - or specific to one single user - that uses a different object. That object could be a SPROC or a table or whatever. There could be a benefit to this.

We personally don't put fully qualified object names in our SPROCS. But when we call a SPROC from a VB client we do use dbo.SPROCNAME.

But we are forced to put dbo.UserDefinedFunction in a SPROC - for some reason UDF's require schemas.

Maybe others can share there use of DBO or schemas in general.