SQL Standards - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Standards

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-17 : 12:22:43

Copy of topic as last one went off a bit

Trying to produce a coding standards document.
Not so much naming standards but methods of coding.
Like avoiding cursors, not in, not exists, tables without unique indexes

Lets try building a list - I'll edit this with any additions/removals

(lot of this came from http://vyaskn.tripod.com/coding_conventions.htm by VyasKN and is worth a look as it includes explanations)

It may be that in some situations the best solution would be to violate these suggestions.

Don't use Cursors
Every table should have a unique index
Every identity column should have a unique index
Avoid "not in", "<>"
Avoid "in"
Avoid correlated subqueries
Utilise indexes i.e. make sure you include the first field of the index in the join.
Take into account the effect (locking, resources) of other processes running on the server.
Put a "set nocount on" at the top of every SP.
Limit cross database processes - take into account the backup/restore process (especially for updates) and permissions.
Limit cross server processes - these should be via a single channel and the system should allow for long term failure of the link / database restores.
if possible, use table variables or derived tables instead of temp tables - but be wary of overcomplicating queries.
if using temp tables, determine if creating indexes on them would help performance
Include a test call in comments at the top of every stored procedure.
Any import/export of data should be coded as an asynchronous call so that the method could be changed if necessary without impacting any processing.
Always use yyyymmdd format for dates.
Try not to use system tables directly. System table structures may change in a future release. Wherever possible, use the sp_help* stored procedures or INFORMATION_SCHEMA views.
Do not use SELECT * in your queries.
Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword.
Use ANSI-Standard Join clauses instead of the old style joins.
Do not prefix your stored procedure names with 'sp_'.
Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements.
If you have a choice, do not store binary files, image files (Binary large objects or BLOBs) etc. inside the database.
Avoid dynamic SQL statements as much as possible.
Always use a column list in INSERT statements.
Do not call functions repeatedly within your stored procedures to return the same value.
If your stored procedure always returns a single row resultset, consider returning the resultset using OUTPUT parameters instead.
Always check the global variable @@ERROR immediately after executing a data manipulation statement
Do not use the column numbers in the ORDER BY clause.
Always code existence checks as "exists (select * from ...)" never "0 = (select count(*) from ...)"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 10/17/2002 12:46:32

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-17 : 12:46:59

Personal experience
The unique key on a table should not be too many columns or too large. The design (I'm still fighting) here has 5 columns that combine to make a unique key. 3 of them are varchar 40's. Keys should preferably be integer and smaller sized varchars if nessacary... varchar(50)'s and higher should never be used as keys. (the problem with natural keys hey?)

This makes join syntax in general difficult
select column_now from table1 t1
inner join table2 t2 on t1.key1 = t2.key2
and t1.key2 = t2.key2
and ..........

Very slow queries

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 10/17/2002 12:50:58

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-17 : 13:08:53

along the same lines as "don't use select *..."

Don't use the ordinal position in an ORDER BY clause ...

Jay White
{0}

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-10-17 : 13:59:15

quote:

along the same lines as "don't use select *..."

Don't use the ordinal position in an ORDER BY clause ...

Jay White
{0}

That's covered in my list :-)

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources