SQL "Commandments"

[ryankbrown]

I was wondering if anybody has a document that states do's and don'ts
when programming for a MS SQL DB. I was under the impression that
Microsoft once produced such a document. Rather than a "Best Practices"
type document it was more of a "Worst Practices" document. Stating
certain procedures that might create slow-downs, bottlenecks, etc. Any
help would be greatly appreciated.

Thank you.

[nib]

ry********@hotmail.com wrote:

I was wondering if anybody has a document that states do's and don'ts
when programming for a MS SQL DB. I was under the impression that
Microsoft once produced such a document. Rather than a "Best Practices"
type document it was more of a "Worst Practices" document. Stating
certain procedures that might create slow-downs, bottlenecks, etc. Any
help would be greatly appreciated.

Thank you.

I haven't seen a document like that (although there could be one), but
here are a couple don'ts:

Dont*
------
Use dynamic sql
Use Cursors
Preface table names tbl or view vw, etc.
Preface column names: chrLastName
* = There are always exceptions to every rule.

I'm sure others will add to the list.

Zach

[Simon Hayes]

<ry********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I was wondering if anybody has a document that states do's and don'ts
when programming for a MS SQL DB. I was under the impression that
Microsoft once produced such a document. Rather than a "Best Practices"
type document it was more of a "Worst Practices" document. Stating
certain procedures that might create slow-downs, bottlenecks, etc. Any
help would be greatly appreciated.

Thank you.

You could start with the Best Practices Analyzer Tool (and of course the
various best practices documents) linked from here:

http://www.microsoft.com/sql/default.mspx

I realise that you say you're looking more for "don't do this" as opposed to
"do this", but in many cases the best practises are stated as "... is not
enabled", "permissions are not granted on ..." or "avoid use of ...".

If this isn't helpful, you might want to mention if you're interested in a
particular topic, such as security, TSQL coding, DTS packages etc. - MS has
several white papers (and also KB articles) for more specific areas.

Simon

[ryankbrown]

Thank you both very much. I am most interested in T-SQL. I have some
applications that are several years old that somebody else developed
that seem to be having significant performance issues now that the load
has grown beyond what was initially expected.

[Nigel Rivett]

http://www.mindsdoor.net/SQLAdmin/BadThings.html

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phil]

Nigel Rivett wrote:

http://www.mindsdoor.net/SQLAdmin/BadThings.html

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Here's a list of my own that I try and preach wherever possible:

Always use a SARG (Search ARGument) in a WHERE clause.
Avoid using the "LIKE" operator with no wildcard.
Don't use the HAVING clause on table columns - use WHERE. (Reserve
HAVING for aggregates)
Use >= instead of > , especially if data contains large number of
duplicate values.
Avoid use of SELECT * FROM ...
Don't Use Cursors! Use a WHILE loop instead.
Avoid use of !=, <> and 'not' operators where possible.
Don't use huge IN clauses.
Don't use DISTINCT in a UNION.
Don't use functions on table columns in WHERE clauses.
Avoid mismatched datatypes in WHERE and join clauses.
Avoid using derived tables where large rowsets are involved - you can't
index derived tables.
Beware of unknown run-time values in WHERE clauses - may result in
tablescans.

[Serge Rielau]

ry********@hotmail.com wrote:

I was wondering if anybody has a document that states do's and don'ts
when programming for a MS SQL DB. I was under the impression that
Microsoft once produced such a document. Rather than a "Best Practices"
type document it was more of a "Worst Practices" document. Stating
certain procedures that might create slow-downs, bottlenecks, etc. Any
help would be greatly appreciated.

Thank you.

1. Commandment:
It's "MS SQL Server". Not "SQL". SQL is an ANSI and ISO standard.
The reason why I say that is that its commandmends will look very
different than those of the MS product with its own strength and
weaknesses that need to be accounted for.

Cheers
Serge

[--CELKO--]

There is a series of articles like that on SQLl Server Central right
now, I also have a book coming out this year undert the workinG title
"SQL PROGRAMMING STYLE", but it is generic.

[Erland Sommarskog]

Phil (ph********@btopenworld.com) writes:

Here's a list of my own that I try and preach wherever possible:
Some good advice - and some poor advice.
Always use a SARG (Search ARGument) in a WHERE clause.
Which means: don't stick your indexed columns into a function, and
make sure that they are not implicitly converted.
Use >= instead of > , especially if data contains large number of
duplicate values.
I guess which to use, depends on the actual problem at hand.
Avoid use of SELECT * FROM ...
Yes!
Don't Use Cursors! Use a WHILE loop instead.
No! Avoid iterating altogether, but if you have to iterate, use a
cursor (and be sure to make INSENSITIVE). Some of my colleagues are
fond of "poor man's cursor" with a WHILE loop. I have more than once
identified performance problems, because they were looping a non-
indexed temp table. Had they used a cursor, the damage would have been
limited-
Avoid use of !=, <> and 'not' operators where possible.
There is no reason for this. If the condition calls for a <>, use it.
(Although <> is not that very commonly used.)
Don't use huge IN clauses.
Yes! There is a tremendous cost for finding the query plan in this
case. Note that rewriting to OR clauses changes nothing - IN is just
syntactic sugar for that. Rewrite and use a table. Also look at my
web site for arrays and lists in SQL Server.
Don't use DISTINCT in a UNION.
Yup. UNION implies DISTINCT.
Don't use functions on table columns in WHERE clauses.
Yup. Scalar UDFs can be expensive. Always benchmark.
Avoid mismatched datatypes in WHERE and join clauses.
Yup. See above under SARG.
Avoid using derived tables where large rowsets are involved - you can't
index derived tables.
So what? You express the query - the optimizer evaluates it. You
may have:

UPDATE smalltable
SET col = col + b.sumval
FROM smalltable s
JOIN (SELECT keycol, sumval = SUM(sumval)
FROM bigtable
GROUP BY keycol) b ON s.keycol = b.keycol
WHERE s.last_updated < datedadd(DAY, -1, getdate())

That is necesarily how the optimizer will actually compute the query.

Specifically, my experience is that the above is a lot faster than

UPDATE smalltable
SET col = col + (SELECT SUM(sumval)
FROM bigtable b
WHERE smalltable.keycol = b.keycol)
WHERE last_updated < datedadd(DAY, -1, getdate())

which is how the ANSI purists would write the query.
Beware of unknown run-time values in WHERE clauses - may result in
tablescans.

So can known values as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[tperovic]

Check out http://www.sql-server-performance.com/

<ry********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I was wondering if anybody has a document that states do's and don'ts
when programming for a MS SQL DB. I was under the impression that
Microsoft once produced such a document. Rather than a "Best Practices"
type document it was more of a "Worst Practices" document. Stating
certain procedures that might create slow-downs, bottlenecks, etc. Any
help would be greatly appreciated.

Thank you.

[ryankbrown]

Thank you all very much. I have been able to put together a great
document to distribute to my developers. They are hard at work combing
through this old code.

And Serge, I certainly understand your sentiment. However, my omission
was based on the casual nature of the group and the fact that when you
precede SQL with MS most, if not all, DB professionals know of what I
am referring. I sincerely apologize if I offended anyone!

Thanks again!