Post reply

SQL Rules base

  • December 5, 2006 at 2:12 pm

    #170640

    here is a post to my blog looking for sql rulez - and offering sweet sweet swag in return

    http://www.apexsql.com/blog/2006/12/da-rules.htm

    Brian Lockwood
    President
    ApexSQL - SQL Developer Essentials

    http://www.apexsql.com/blog

    Stand up for an Independent SQL Community - be Informed

  • December 6, 2006 at 6:38 pm

    #676491

    I'm confused after reading the examples.  Is this for purposes of humor?  That is, the stupidest rules you can think of?  Rules for the sake of rules? 

    (Why does nobody ever take the time to define metastandards?)

    If it is for humor purposes, then I propose:

    "No vowels may be used in naming any database objects except views, in which case only vowels or special characters may be used."

    or "Table names must include the month in which they were created or the middle name of the creator, but never both."

    and who could do without, "All columns must be of datatype Nvarchar(255) regardless of use."

  • December 7, 2006 at 2:17 pm

    #676741

    here are some more stupid rules!

    1. Stored Procedures start with sp (i.e. spGetEmployee)
    2. Stored procedures/user defined functions/triggers/tables (all objects) that are not used by more than one project (most of ours) have to have another prefix indicating the project.   For example a get employee stored procedure that is for our Checkout Project would be spCHKGetEmployee.  (This allows us to easily group stored procedures and know which app uses them.)
    3. User Defined Functions start with fn  (i.e. fnGetPriority)
    4. Stored procs that always return one row and one column should use an output value instead.  (Not always followed here)
    5. Should not use string values as PKs.  (Again not always followed.)
    6. Each stored procedure/user defined function/trigger should have a header that states the author, the purpose of the item and a modification list.  This list should be updated for every change to the stored procedure.
    7. Object names must NOT have brackets.  (This is because SourceSafe Analyze (a SourceSafe tool from Microsoft) will remove them and put _ in instead when it runs.)
    8. Cursors are the GOTO of SQL at my company.  They are almost always not allowed.  (I have yet to see one allowed in my almost 3 year here.)  Instead a table variable is used with an identity column.  This table variable is iterated through using a while loop.  This eliminates the possibility of a cursor not being deallocated.
    9. An object name should not longer than 45 chars.
    10. Don’t use DISTINCT.  Use GROUP BY instead.  (It is usually faster.)
    11. Don’t use sub selects, rather join the table into the main query.  (Of course there are times when this does not work, but most newbies will use a sub select when a join gives the same results and is a lot faster.)
    12. Every table should have a PK.
    13. Every table starts with tbl (i.e. tblEmployee)
    14. All columns are in CAPS.
    15. No spaces in columns.  Use _ instead.  (i.e. EMPLOYEE_NAME)
    16. No spaces or underscores in objects.  (ie spGetEmployeeBirthdate not spGet_Employee_Birthdate)
    17. Camel case all objects except abbreviations and prefixs from rules 1 and 2 (above) (i.e. spGetEmployeeIDNumber)
    18. PKs should be identities if possible.
    19. Variable names are camel case @EmployeeBirthDate
    20. Words like select, from, where, values are lower case
    21. NoLock is as written (NoLock)
    22. @@ERROR @@ROWCOUNT are as written  (All caps)

    Brian Lockwood
    President
    ApexSQL - SQL Developer Essentials

    http://www.apexsql.com/blog

    Stand up for an Independent SQL Community - be Informed

  • December 7, 2006 at 2:18 pm

    #676742

    and more ... (send in my people)

    1. Follow the defined SP naming convention
    2. Follow the defined SP Documentation Template
    3. Follow the defined variable naming Convention
    4. Structure the SP for easy readability
    5. Return only  the required rows back to the client
    6. Return only  the required columns to the client
    7. DO Not use SELECT *
    8. Always specify ORDER BY When TOP is used.
    9. Use UNION ALL instead of UNION when possible.
    10. Avoid using DISTINCT
    11. USE EXISTS Instead OF IN
    12. Avoid using functions on columns in WHERE clause.
    13. Avoid data Conversion in the WHERE Clause
    14. Use CREATE TABLE… INSERT INTO Syntax instead of SELECT INTO
    15. Specify Column level filter conditions in the WHERE clause and GROUP level filter conditions in the HAVING clause.
    16. Always SET NOCOUNT ON
    17. Avoid using Cursors and other LOOP constructs. USE SET based solutions whenever possible
    18. Remove unused code
    19. Avoid using dynamic SQL
    20. When Dynamic SQL is required, use sp_executesql with parameter list.
    21. Avoid Nesting of stored procedures.
    22. Validate parameters at the beginning of the SP
    23. Use TRUNCATE instead of DELETE whenever possible
    24. Do not Intermix DDL and DML statements
    25. All DDL and variable declarations should be at the top of the SP
    26. Use same datatype in the JOIN clause.
    27. Join data types should be preferably numeric.
    28. Put the most restrictive filter condition first
    29. Avoid using <> (!=)
    30. Provide Redundant joins for better performance.
    31. Use derived tables instead of temp tables.
    32. Use the same datatype and length of table columns for stored procedure parameters.
    33. RETURN a status value indicating success or failure
    34. Use table variables instead of temp tables when the number of rows returned is less than 100
    35. All transactions should have a COMMIT and ROLLBACK logic.
    36. Use Error Handling with Transactions
    37. Select the proper ISOLATION LEVEL to avoid unnecessary locks on objects.
    38. Always explicitly add the ORDER BY clause when any ORDER is expected at the client side.

    Brian Lockwood
    President
    ApexSQL - SQL Developer Essentials

    http://www.apexsql.com/blog

    Stand up for an Independent SQL Community - be Informed

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply