#61
  1. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    Make SQL Statements more readable


    To make SQL Statements more readable, start each clause on a new line and indent when needed. Following is an example:

    Code:
    SELECT title_id, title
    FROM titles
    WHERE title LIKE '%Computer%' AND
          title LIKE '%cook%'
    Please give respect to those that helped solve an issue by clicking on the icon
  2. #62
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    Increase timeouts


    When querying a database, the default timeout is often low, like 30 seconds. Remember that report queries may run longer than this, specially when your database grows. Hence increase this value to an acceptable value
  4. #63
  5. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    In SQL Server 2000 - use SCOPE_IDENTITY not SELECT max()


    Dont do SELECT max(ID) from MasterTable when inserting in a Details table. This is a common mistake, and will fail when concurrent users are inserting data at the same instance. Use one of SCOPE_IDENTITY or IDENT_CURRENT. My choice would be SCOPE_IDENTITY as this would give you the identity value from the current context in prespective.
  6. #64
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    Use MS Access/SQL Server SQL analyzers


    The SQL Server query analyzer is a powerful tool. And surely is your friend, and you'll learn a lot of how it works and how the query and index design can affect performance through it. Understand the execution plan that the execution plan window shows for potential bottlenecks
  8. #65
  9. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    When using Joins


    Make sure that there are n-1 join criteria if there are n tables.

    Make sure that ALL tables included in the statement are joined. Make sure that only tables that

    · Have columns in the select clause

    · Have columns referenced in the where clause

    · Allow two unrelated tables to be joined together are included.
  10. #66
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    Apply the latest Security Packs / Service Packs


    Even though this point applies to the network and the database administrators, it is always better to keep up-to date on the software’s. With the "slammer" virus and many more still outside, it is one of the best practices to be up-to date on the same. Consider this strongly.
  12. #67
  13. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    Using Count



    The only 100 percent accurate way to check the number of rows in a table is to use a COUNT(*) operation. The statement might consume significant resources if your tables are very big because scanning a large table or index can consume a lot of I/O. Avoid these type of queries to the maximum. Use short circuting methods as EXISTS etc. Here is one other way you can find the total number of rows in a table. SQL Server Books Online (BOL) documents the structure of sysindexes; the value of sysindexes.indid will always be 0 for a table and 1 for a clustered index. If a table doesn't have a clustered index, its entry in sysindexes will always have an indid value of 0. If a table does have a clustered index, its entry in sysindexes will always have an indid value of 1.

    Code:
    SELECT object_name(id) ,rowcnt 
    FROM sysindexes 
    WHERE indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1

    Comments on this post

    • arunks agrees : Good One
    Last edited by bslintx; August 9th, 2005 at 09:37 AM.
  14. #68
  15. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    nice work Bslintx!
    Can't rep++ ATM, but good job, you working overtime?
    Look! Its a ShemZilla


  16. #69
  17. Contributing User
    ASP Skiller (1500 - 1999 posts)

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

    SQL injection


    Security has been a prime concern for everyone. Hence validate all the incoming parameters at all levels of the application. Limit the scope of possible damage by permitting only minimally privileged accounts to send user input to the server. Adding to it, run SQL Server itself with the least necessary privileges
  18. #70
  19. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Apr 2004
    Location
    United States
    Posts
    1,814
    Rep Power
    17
    Originally Posted by nofriends
    nice work Bslintx!
    Can't rep++ ATM, but good job, you working overtime?

    thanks bud...i can't do yours either but it seems a lil slow in the forum and i took a couple weeks off and i needed to catch up on updating this thread AND i noticed you and elija are tearing it up on the rep points....fantastic...pheeeew, lots of room to make up....good job!!! keep it up
  20. #71
  21. creepy-crawly explorer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Apr 2005
    Location
    India
    Posts
    132
    Rep Power
    14

    Tips for cleaning up query strings


    Found this article to be quite good!!

    http://www.macromedia.com/cfusion/kn...fm?id=tn_16736

    Comments on this post

    • Shadow Wizard agrees : nice, thanks for sharing. :)
    • bslintx agrees : thanks for sharing! good link
    Last edited by Shadow Wizard; August 18th, 2005 at 02:23 AM. Reason: added [url] and [/url] tags around links to make them clickable :)
  22. #72
  23. Senior Fire Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Ashford, Kent. England
    Posts
    5,651
    Rep Power
    318
    Originally Posted by bslintx
    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.
    I would strongly agree with this, but used the rep++ earlier
    Last edited by elijathegold; August 18th, 2005 at 03:04 AM.
    And he picked it all up... in his pick-up.





    Friends of Shemzilla
  24. #73
  25. Expert Learner
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Feb 2005
    Location
    Wisconsin
    Posts
    1,909
    Rep Power
    101
    Originally Posted by bslintx
    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
    Here's a page with a detailed list:
    http://developer.mimer.se/validator/...rved-words.tml
  26. #74
  27. creepy-crawly explorer
    ASP Discoverer (100 - 499 posts)

    Join Date
    Apr 2005
    Location
    India
    Posts
    132
    Rep Power
    14

    Smile Useful Functions.


    Thought this might help u people..


    This function will get variables from forms and/or querystrings and/or cookies. The function will set VBScript variables with identical names, and equal values. This saves the tedious task of request.form("this") request.Querystring("that"), and request.cookies("the_other").

    Usage:- Call SetVars(StrType) Where 'StrType' is a string value of either, "form", "querystring", "cookies",or "all"
    Code:
    <%
    Function SetVars(StrType)
    If lcase(StrType) = "form" or lcase(strType) = "all" then
    For Each Field in Request.Form
    TheString = Field & "=Request.Form(""" _
    & Field & """)"
    EXECUTE(TheString)
    Next
    End If
    
    If lcase(StrType) = "querystring" or lcase(strType) = "all" then
    For Each Field in Request.Querystring
    TheString= Field & "=Request.Querystring(""" _
    & Field & """)"
    EXECUTE(TheString)
    Next
    End If
    
    If lcase(StrType) = "cookies" or lcase(strType) = "all" then
    For Each Field in Request.Cookies
    TheString= Field & "=Request.Cookies(""" _
    & Field & """)"
    EXECUTE(TheString)
    Next
    End If
    END Function
    %>

    Comments on this post

    • barneybabe agrees : Seems to be a nice function. Thanks for sharing.
    • eglons agrees : Thanks for this
  28. #75
  29. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2005
    Posts
    1
    Rep Power
    0

    Simple things to notice


    Good work.Keep it up.These are simple things we dont concentrate upon.Thank u very much

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