Page 5 - Best Practices : ASP Coding, SQL Statements, and Database Structuring

ASP Free Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

#61

July 20th, 2005, 03:37 PM

bslintx

Contributing User

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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

#62

July 20th, 2005, 03:39 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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

#63

July 20th, 2005, 03:41 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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.

#64

July 20th, 2005, 03:42 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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

#65

July 20th, 2005, 03:43 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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.

#66

July 20th, 2005, 03:46 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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.

#67

July 20th, 2005, 03:46 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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.

#68

July 20th, 2005, 03:48 PM

nofriends

Senior Water Wizard

ASP Free God 11th Plane (10000 - 10499 posts)

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,178

Folding Points: 92188 Folding Title: Advanced Folder

Time spent in forums: 3 Months 2 Weeks 2 Days 6 h 45 m 36 sec
Reputation Power: 683

nice work Bslintx!
Can't rep++ ATM, but good job, you working overtime?

__________________
Look! Its a ShemZilla

#69

July 20th, 2005, 03:48 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

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

#70

July 20th, 2005, 03:52 PM

bslintx

Contributing User

Join Date: Apr 2004

Location: United States

Posts: 1,814

Time spent in forums: 2 Weeks 3 Days 17 h 26 m 7 sec
Reputation Power: 7

Quote:

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

#71

August 18th, 2005, 02:09 AM

arunks

creepy-crawly explorer

Join Date: Apr 2005

Location: India

Posts: 132

Time spent in forums: 1 Day 17 h 53 m 15 sec
Reputation Power: 4

Tips for cleaning up query strings

Found this article to be quite good!!

http://www.macromedia.com/cfusion/k...cfm?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 :)

#72

August 18th, 2005, 03:02 AM

elijathegold

Senior Fire Wizard

Join Date: Feb 2005

Location: Ashford, Kent. England

Posts: 5,651

Folding Points: 1490596 Folding Title: Super Ultimate Folder - Level 3

Time spent in forums: 2 Months 2 Weeks 2 Days 3 h 57 m 17 sec
Reputation Power: 301

Quote:

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

__________________
And he picked it all up... in his pick-up.

Friends of Shemzilla

Last edited by elijathegold : August 18th, 2005 at 03:04 AM.

#73

September 11th, 2005, 11:26 AM

baseballdude_

Expert Learner

Join Date: Feb 2005

Location: Wisconsin

Posts: 1,856

Folding Points: 22104 Folding Title: Starter Folder

Time spent in forums: 1 Week 5 Days 9 h 30 m 59 sec
Reputation Power: 59

Send a message via Yahoo to baseballdude_

Send a message via Google Talk to baseballdude_

Quote:

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