Page 1 of 7 123 ... Last
  • Jump to page:
    #1
  1. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Best Practices : ASP Coding, SQL Statements, and Database Structuring


    Hi all,

    In the past week or so, I have noticed quite a bit of the same syntax errors in ASP code and/or SQL statements. I thought it would be good for newbies and/or seasoned programmers to have a thread to help develop their code better. Please add "Best Practices" techniques and NOT opinions. In other words facts and research that can back it up. Thanks! Also please title the post to allow the user to understand what the best practice is. Links along with your best practice would be great!

    Code:
    Table of Contents    
    
    1.  Watch out for Reserved Words in SQL Statements
    2.  Semicolon after SQL Statements 
    3.  Do not use SELECT * in your SQL queries  
    4.  Extensions for Included Files 
    5.  Paths, Using MapPath 
    6.  Select Case Statement 
    7.  Spaces in Scripts 
    8.  String Concatenation w/ VBS  
    9.  Variable Declaration and Names  
    10. Variable Value Trimming  
    11. Object and Variable Initialization
    12. Using Page Scope for Best Performance
    13. Using Global Variables Sparingly and Efficiently
    14. Avoiding the Use of Public Variables
    15. Using Application Scope for Objects
    16. Avoiding the Use of Server Variables
    17. Declaring Objects with the <OBJECT> Tag
    18. When Not to Use ASP  
    19. Using the Dictionary Object for Lookup  
    20. Timing Out Connection Requests  
    21. Browser Connections and ASP  
    22. Organizing Application Directories and Files  
    23. Combine multiple sequential Response.Write lines  
    24. Type Notation  
    25. Use good script coding style  
    26. Avoid context switching  
    27. Use buffering  
    28. Avoid Redundant Code  
    29. Subs and Functions
    30. Objectives of Good Relational Database Design
    31. Benefits of Good Database Design
    32. Naming Tables and Fields  
    33. Do Not Cache Database Connections in the Application or Session Objects  
    34. Release the database connection to the connection pool as quickly as possible   
    35. Use Option Explicit to Speed up Processing  
    36. Use Local Variables in Subroutines and Functions  
    37. Assign Request Variables to Script Variables  
    38. Avoid Redimensioning Arrays  
    39. Avoid String Concatenation in Loops  
    40. Use Server.Transfer Instead of Response.Redirect Whenever Possible  
    41. Use Trailing Slashes in Directory URLs  
    42. Do Performance Testing  
    43. SQL Server Benchmarks  
    44. Avoid NULLable columns  
    45. Avoid wildcard characters at the beginning of a word  
    46. Use 'Derived tables' wherever possible  
    47. Use the more readable ANSI-Standard Join clauses instead of the old style joins  
    48. Do not prefix your stored procedure names with "sp_" with sql server  
    49. Do not store binary or image files (Binary Large Objects or BLOBs) inside the database
    50. Access tables in the same order in all your stored procedures/triggers consistently  
    51. Make SQL Statements more readable  
    52. Increase timeouts  
    53. In SQL Server 2000 - use SCOPE_IDENTITY not SELECT max()  
    54. Use MS Access/SQL Server SQL analyzers  
    55. When using Joins  
    56. Apply the latest Security Packs / Service Packs  
    57. Using Count  
    58. SQL injection  

    Comments on this post

    • PhilCollins99 agrees : Lots of credit for this. RESPECT!
    Last edited by bslintx; August 11th, 2005 at 10:05 PM. Reason: added addition to thread title
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Watch out for Reserved Words in SQL Statements


    I noticed recently that folks out there are using "reserved" words in their SQL statements. This is a big no-no. Try to make field names unique to what that table is describing. For example: Date is a reserved word and CAN be used ias a field name if put within brackets as so...[Date] but this is not a habit to get into. Let's say you have a table called Events. Then you have an event date. Name it EventDate or Event_Date. It is self-explanatory when someone reviews your code what that field name refers to rather than Date.

    Update: Contrary to belief...Password IS NOT a reserved word


    Here are some reserved words in the SQL standard to stray away from when writing FIELDS for sql statements:

    Code:
    ABSOLUTE   
    ACTION    
    ADD 
    AFTER   
    ALL 
    ALLOCATE 
    ALTER 
    AND 
    ANY 
    ARE 
    ARRAY  
    AS 
    ASC    
    ASENSITIVE  
    ASSERTION    
    ASYMMETRIC 
    AT 
    ATOMIC 
    AUTHORIZATION 
    AVG     
    BEFORE   
    BEGIN 
    BETWEEN 
    BIGINT 
    BINARY 
    BIT
    BIT_LENGTH     
    BLOB  
    BOOLEAN  
    BOTH 
    BREADTH   
    BY
    CALL 
    CALLED 
    CASCADE   
    CASCADED 
    CASE 
    CAST 
    CATALOG  
    CHAR 
    CHAR_LENGTH     
    CHARACTER 
    CHARACTER_LENGTH     
    CHECK 
    CLOB 
    CLOSE 
    COALESCE     
    COLLATE 
    COLLATION    
    COLUMN 
    COMMIT 
    CONDITION  
    CONNECT 
    CONNECTION
    CONSTRAINT 
    CONSTRAINTS    
    CONSTRUCTOR   
    CONTAINS     
    CONTINUE
    CONVERT     
    CORRESPONDING 
    COUNT     
    CREATE 
    CROSS
    CUBE CUBE 
    CURRENT 
    CURRENT_DATE 
    CURRENT_DEFAULT_TRANSFORM_GROUP 
    CURRENT_PATH 
    CURRENT_ROLE CURRENT_ROLE 
    CURRENT_TIME 
    CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE 
    CURRENT_USER 
    CURSOR 
    CYCLE 
    DATA   
    DATE 
    DAY 
    DEALLOCATE 
    DEC 
    DECIMAL 
    DECLARE
    DEFAULT 
    DEFERRABLE   
    DEFERRED    
    DELETE 
    DEPTH   
    DEREF
    DESC  
    DESCRIBE
    DESCRIPTOR    
    DETERMINISTIC 
    DIAGNOSTICS
    DISCONNECT 
    DISTINCT 
    DO
    DOMAIN
    DOUBLE 
    DROP 
    DYNAMIC DYNAMIC 
    EACH
    ELEMENT 
    ELSE 
    ELSEIF 
    END 
    EQUALS   
    ESCAPE 
    EXCEPT
    EXCEPTION    
    EXEC 
    EXECUTE 
    EXISTS
    EXIT 
    EXTERNAL 
    EXTRACT     
    FALSE 
    FETCH 
    FILTER 
    FIRST    
    FLOAT 
    FOR 
    FOREIGN 
    FOUND   
    FREE 
    FROM 
    FULL 
    FUNCTION 
    GENERAL   
    GET
    GLOBAL 
    GO    
    GOTO    
    GRANT 
    GROUP 
    GROUPING  
    HANDLER 
    HAVING 
    HOLD 
    HOUR 
    IDENTITY 
    IF 
    IMMEDIATE 
    IN 
    INDICATOR 
    INITIALLY    
    INNER 
    INOUT 
    INPUT 
    INSENSITIVE
    INSERT 
    INT 
    INTEGER 
    INTERSECT 
    INTERVAL 
    INTO 
    IS 
    ISOLATION
    ITERATE 
    JOIN 
    KEY   
    LANGUAGE 
    LARGE  
    LAST  
    LATERAL LATERAL 
    LEADING 
    LEAVE 
    LEFT
    LEVEL    
    LIKE 
    LOCAL 
    LOCALTIME  
    LOCALTIMESTAMP 
    LOCATOR   
    LOOP 
    LOWER     
    MAP   
    MATCH 
    MAX     
    MEMBER 
    MERGE 
    METHOD  
    MIN     
    MINUTE 
    MODIFIES 
    MODULE 
    MONTH 
    MULTISET 
    NAME
    NAMES    
    NATIONAL 
    NATURAL 
    NCHAR 
    NCLOB NCLOB 
    NEW  
    NEXT    
    NO 
    NONE  
    NOT 
    NULL 
    NULLIF     
    NUMERIC 
    OBJECT   
    OCTET_LENGTH     
    OF
    OLD 
    ON 
    ONLY 
    OPEN 
    OPTION   
    OR 
    ORDER 
    ORDINALITY   
    OUT 
    OUTER 
    OUTPUT
    OVER  
    OVERLAPS
    PAD  
    PARAMETER 
    PARTIAL    
    PARTITION  
    PATH 
    POSITION     
    PRECISION SION 
    PREPARE
    PRESERVE    
    PRIMARY 
    PRIOR    
    PRIVILEGES   
    PROCEDURE 
    PUBLIC   
    RANGE RANGE 
    READ    
    READS 
    REAL 
    RECURSIVE  
    REF  
    REFERENCES 
    REFERENCING  
    RELATIVE    
    RELEASE
    REPEAT 
    RESIGNAL 
    RESTRICT
    RESULT 
    RETURN 
    RETURNS 
    REVOKE 
    RIGHT 
    ROLE   
    ROLLBACK 
    ROLLUP 
    ROUTINE    
    ROW 
    ROWS 
    SAVEPOINT  
    SCHEMA    
    SCOPE  
    SCROLL 
    SEARCH  
    SECOND 
    SECTION    
    SELECT 
    SENSITIVE
    SESSION   
    SESSION_USER 
    SET 
    SETS   
    SIGNAL 
    SIMILAR  
    SIZE
    SMALLINT 
    SOME 
    SPACE  
    SPECIFIC
    SPECIFICTYPE  
    SQL
    SQLCODE     
    SQLERROR     
    SQLEXCEPTION 
    SQLSTATE 
    SQLWARNING
    START  
    STATE   
    STATIC 
    SUBMULTISET 
    SUBSTRING     
    SUM     
    SYMMETRIC
    SYSTEM 
    SYSTEM_USER 
    TABLE 
    TABLESAMPLE 
    TEMPORARY   
    THEN 
    TIME 
    TIMESTAMP 
    TIMEZONE_HOUR 
    TIMEZONE_MINUTE 
    TO 
    TRAILING 
    TRANSACTION    
    TRANSLATE     
    TRANSLATION 
    TREAT  
    TRIGGER
    TRIM     
    TRUE
    UNDER   
    UNDO 
    UNION 
    UNIQUE 
    UNKNOWN
    UNNEST  
    UNTIL 
    UPDATE 
    UPPER     
    USAGE
    USER 
    USING 
    VALUE 
    VALUES 
    VARCHAR 
    VARYING 
    VIEW 
    WHEN 
    WHENEVER 
    WHERE 
    WHILE 
    WINDOW  
    WITH 
    WITHIN  
    WITHOUT  
    WORK   
    WRITE    
    YEAR 
    ZONE

    Comments on this post

    • baseballdude_ agrees : Thoughtful, useful set of posts. :0)
    • matthuxtable agrees : Excellent list of reserved words. Very thoughtful! :) - matty
    • ShwetaAgarwal agrees
    Last edited by bslintx; August 18th, 2005 at 11:57 AM.
  4. #3
  5. Expert Learner
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Feb 2005
    Location
    Wisconsin
    Posts
    1,909
    Rep Power
    101
    I'll keep this thread in mind when I start seriously learning how to code in ASP.
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Semicolon after SQL Statements?


    A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Is this necessary? If using MS Access or SQL Server it is not necessary..... but some database programs do require it. So best practice is to start using it!!!


    Code:
    SQL ="SELECT ProductNameFROM Products;"

    Comments on this post

    • Sony agrees : Yes thats true
    • elijathegold agrees : If you use it routinely you wont forget when you need to
  8. #5
  9. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Do not use SELECT * in your SQL queries


    Do not use SELECT * in your queries. If you have 8 fields and you ONLY need 3 then simply select those 3. Bottom line is, select ONLY the fields you need to query.
    This technique results in reduced disk I/O and better performance
    http://www.codeproject.com/cs/database/sqldodont.asp
    Code:
    SQL="SELECT CustomerID, CustomerFirstName, City FROM Customers;"
    In light of a few comments that believe this is not a best practice procedure I researched more info to help you understand why NOT to even though some think it doesn't matter in some cases when it actually does....so for that...YOU be the judge...should i? or shouldn't I?

    Reasons why you should not use SELECT * in you SQL statements. Instead you should enumerate all the fields that you want to select. There are probably more, but thise should be sufficient into your making your decision.

    source: http://www.adopenstatic.com/faq/selectstarisbad.asp

    By selecting only the fields that you want you don't return any superfluous fields. This results in an immediate speed increase. For example consider the following table:


    Suppose we wish to create a drop down select box. To do this we really only need to the information from the first two fields (CustomerID and CustomerName). However, if we use SELECT * then we are pulling all the information for each customer from the database as we write each record, even if we don't need it (ie we are pulling data in the CustomerAddress and CustomerComments fields even though we don't need this information). By only selecting the fields we need we reduce the amount of data pulled from the database and thus speed up our application.
    1. At some point in time you'll want to look at using the Recordset Object's .getRows() method to convert a recordset into an array and close your recordset objects earlier. However you will not be able to do this effectively unless you know which fields correspond to which array elements (which requires you to enumerate the fields in the SELECT statement).
    2. At some point you will also look at using the Recordset Object's .getString() method which is even faster than the .getRows() method for returning recordsets to the screen. If you use SELECT * you will have no control over the order in which columns are displayed on the screen.
    3. If you have Access Memo type fields, or SQL Server Text type fields these need to be selected last in your SQL statement, otherwise you will start to run into the problem where these records either do not appear on the screen, or are truncated (see Microsoft's KB article: Q200124).
    4. SELECT * is lazy coding practise. It's probably best to start with good habits early. As well the old saying of "a stitch in time saves nine" is very true. Imagine having to trawl through a 1000 line ASP page that you coded 6 months ago trying to find all the fields you used to edit a SELECT statement at the top of the page. Then imagine doing this for 1000 pages! Better to do it right the first time.
    5. This last point is not a fact, but merely supposition at this point in time. I've heard that if you do SELECT * the database needs to find out what fields are actually in the table before it can then select them all. By specifying the field names the database engine can use those names straight away rather than having to do an extra lookup. However there is very little evidence on the web to say one way or the other.
    source: http://www.bann.co.uk/asp/databases/select.asp

    6. ADO makes two queries to the database. Before it fills your recordset, ADO must query the system tables to determine the name and datatypes all
    your fields. It is much more faster to specify your fields names in the SQL query, as the system table lookup no longer happens
    Last edited by bslintx; August 9th, 2005 at 09:12 AM. Reason: Added concrete facts why not to use the SELECT * in sql statement
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Extensions for Included Files


    For consistency, use include files in order to make specific information available to more than one referring page (changes to include files are distributed to all the pages that include them).


    Standards:


    .inc—for large amounts of data with client-side scripting


    .txt—for text-formatted data files without scripting


    Do not use .inc for pages containing server-side scripts. If a user manages to display them, any business rules in the scripts will be exposed. Use the .asp extension for all pages containing scripting, or for which scripting is planned, in order to avoid displaying proprietary information coded as scripts in ASP pages.

    Comments on this post

    • Medic64 agrees : I was taught to use .asp for that very reason
  12. #7
  13. Bug Catching Moderator
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Sep 2004
    Location
    Australia
    Posts
    3,572
    Rep Power
    121
    Originally Posted by bslintx
    Do not use SELECT * in your queries. If you have 8 fields and you ONLY need 3 then simply select those 3. Bottom line is, select ONLY the fields you need to query.
    This technique results in reduced disk I/O and better performance
    If your query is only going to return a few records, which is typically the case if there's a WHERE clause in the SQL statement and you're filtering on a primary key, "SELECT * " isn't going to impose any noticeable performance penalty, plus you avoid those nasty reserved word problems .

    Of course, if you don't know how many records might be fetched and it's a large table, then bslintx is right - request only the columns you need.

    Comments on this post

    • matthuxtable agrees
    Have I made your day? If so, please show your appreciation either by sending money or the cheaper, preferred option of clicking on the scales icon at the top right of this posting. My reputation needs all the help it can get.
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Paths, Using MapPath


    Consider using the MapPath method instead of literal paths in ASP applications. The ASP Server.MapPath method allows you to physically relocate an ASP application without recoding scripts. This saves program modification and maintenance effort.

    Note: Performance is affected slightly, because every time you use MapPath in a script, IIS must retrieve the current server path. Consider placing the result of the method call in an application variable in order to avoid retrieving the server path.

    Code:
     
      Set objConn = Server.CreateObject("ADODB.Connection")
      Set  objRS = Server.CreateObject("ADODB.Recordset") 
      FilePath = Server.MapPath("myDb.mdb")
      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";" 
      objConn.Open strConn
  16. #9
  17. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Select Case Statement


    For readability and efficiency, use the Select Case statement in place of If&Else in order to repeatedly check for the same variable for different values. For example:


    Code:
    <% 
       Dim dat
      dat=WeekDay(Date)
    
    Select Case dat
      case 1
    	response.write("Today is Sunday")
      case 2
    	response.write("Today is Monday")
      case 3
    	response.write("Today is Tuesday")
      case 4
    	response.write("Today is Wednesday")
      case 5
    	response.write("Today is Thursday")
      case 6
    	response.write("Today is Friday")
    	case 7
    	response.write("Today is Saturday")
      end select
    %>
    Last edited by bslintx; May 8th, 2005 at 08:02 PM. Reason: had invalid var..replaced with an easer example to follow...thanks splinters!
  18. #10
  19. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Spaces in Scripts


    To enhance script readability, use spaces before and after operators, such as plus (+), minus (-), and equal (=).


    Example:

    Code:
    intYearsService = intYearCurrent — intYearFirst
  20. #11
  21. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    863
    Bslintx, add this to my tutorial in the code section called ASP and SQL
    Look! Its a ShemZilla


  22. #12
  23. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    863
    I will ask Shadow Wizard to add the coding posts to the thread

    Nice work
  24. #13
  25. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,651
    Rep Power
    2689
    bslintx, very nice idea and also very helping thread!!
    keep it up champ!!
    Laziness is my religion and Sunday is my God

    Get the Mantra!
  26. #14
  27. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    String Concatenation w/ VBS




    For the sake of consistency and to achieve the intended interpretation, use the string concatenator (&) instead of a plus (+) sign in VBScript strings.


    Instead of this:
    Code:
    WholeName = FirstName + " " + LastName
    do this:
    Code:
    WholeName = FirstName & " " & LastName
  28. #15
  29. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17

    Variable Declaration and Names


    Variable Declaration


    Explicitly declaring variables helps expose errors, such as misspelled variable names. To make code more reliable and readable, use the Option Explicit statement in VBScript.


    When you want to use strong variable typing, the logic should be programmed into a component built with a language that supports it, such as Visual Basic 6.0 or Microsoft® Visual J++®. Loosely typed variables (not typed until run time), such as variants in VBScript, can affect performance, especially when mathematical computations are involved.



    Variable Names

    To make the intended use of a variable clear to others reading your script, use three-character prefixes in lowercase to indicate data type. Even though explicit data typing is not supported in either VBScript or JScript, the use of such prefixes is recommended.


    For consistency in naming variables, use initial capital letters in variable names. Do not capitalize prefixes. For example, to denote the data type of the variable named "SwitchOn" as Boolean, use the prefix "bln," to name the variable "blnSwitchOn".

    Suggested Prefixes for Indicating the Data Type of a Variable


    Comments on this post

    • kanenas.net agrees
    Last edited by bslintx; August 11th, 2005 at 12:08 PM.
Page 1 of 7 123 ... Last
  • Jump to page:

Similar Threads

  1. ASP and SQL
    By nofriends in forum Code Bank
    Replies: 12
    Last Post: October 14th, 2008, 01:56 PM
  2. Exception Handling
    By kirandayalin in forum ASP Development
    Replies: 14
    Last Post: April 21st, 2005, 01:16 PM
  3. combine SQL statements
    By leuvenaar in forum ASP Development
    Replies: 3
    Last Post: April 19th, 2005, 06:59 AM
  4. Replies: 3
    Last Post: January 14th, 2005, 02:38 PM
  5. Replies: 2
    Last Post: February 11th, 2004, 02:51 PM

IMN logo majestic logo threadwatch logo seochat tools logo