SQL rules to live by-VBForums
Results 1 to 17 of 17

Thread: SQL rules to live by

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    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.
    Last edited by szlamany; Feb 17th, 2005 at 01:22 PM.

  2. #2
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: SQL rules to live by

    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.

  3. #3
    Frenzied Member maged's Avatar
    Join Date
    Nov 2002
    Location
    Egypt
    Posts
    1,040

    Re: SQL rules to live by

    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

  4. #4
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: SQL rules to live by

    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:
    1. SELECT DISTINCT fldMonth FROM tblFOO
    2. 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.
    Tengo mas preguntas que contestas

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL rules to live by

    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:
    1. SELECT DISTINCT fldMonth FROM tblFOO
    2. 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...

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: SQL rules to live by

    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.
    Tengo mas preguntas que contestas

  7. #7
    PowerPoster
    Join Date
    May 2002
    Posts
    25,462

    Re: SQL rules to live by

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL rules to live by

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9
    Fanatic Member
    Join Date
    Oct 2001
    Location
    Three Anchor Bay, Cape Town, South Africa
    Posts
    769

    Re: SQL rules to live by

    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.

  10. #10
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: SQL rules to live by

    Quote Originally Posted by shunt
    I don't really agree with #5
    He does actually say that you should not use it.

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

  11. #11

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL rules to live by

    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.
    Last edited by szlamany; Jan 7th, 2006 at 12:34 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    Junior Member
    Join Date
    Apr 2008
    Posts
    31

    Re: SQL rules to live by

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

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,191

    Re: SQL rules to live by

    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.

  14. #14

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL rules to live by

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    Junior Member
    Join Date
    Apr 2008
    Posts
    31

    Re: SQL rules to live by

    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)?

  16. #16
    Hyperactive Member
    Join Date
    Jan 2008
    Location
    Merseyside
    Posts
    456

    Re: SQL rules to live by

    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)

  17. #17

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,817

    Re: SQL rules to live by

    @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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.