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 server standards

Author  Topic 

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 04:36:53
A friend needs 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 (that'll be fun - none there at the moment :( ).

I'm sure I've been involved in a discussion about this and seen some quite good documents - of course I can't find any now.

==========================================
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.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2002-10-17 : 05:45:16
Got to be honest, I'd love to see this, esp. if it carries reasonings behind the points, and not just - "don't do that, do this"

****
I've seen some mails on another group saying that one should rather use the "table data type" rather than temp tables (SQL 2K from what I can see). Apparently they are more efficient.
****
Go to Top of Page

smccreadie
Aged Yak Warrior

505 Posts
Posted - 2002-10-17 : 05:51:44
I can't find a document that explicitly states these. There is a tool which we're considering (http://www.fmsinc.com/Products/sqlanalyzer/Guide.htm) that appears to identify bad coding practices.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 06:24:50
Ok lets try building a list - I'll edit this with any additions/removals

Move of these apply to large sets and can be ignored if the operation applies to a small amount of data.
Also 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.

==========================================
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 08:17:40
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-17 : 06:55:33
What's wrong with "<>" and correlated subqueries?

How bout something like "All procs with subtree cost > 1 must be reviewed by entire db team..." I always thought that was a good way to force people to look at the execution plan before moving on to the next proc.

Jay White
{0}
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2002-10-17 : 06:59:44
- if possible, use table variables or derived tables instead of temp tables
- if using temp tables, determine if creating indexes on them would help performance
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 08:14:01
<> won't use an index.
Correlated subqueries tend to be slow - especially when nested.

Just something to watch out for

==========================================
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.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-17 : 08:33:58
quote:

<> won't use an index.


What's the alternative?

Gimme everybody from pubs.dbo.authors where au_lname <> 'white'

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 09:14:23
since you asked (not serious).

assuming au_lname is the PK - can't be bothered to look.

select a.*
from pubs.dbo.authors a left outer join pubs.dbo.authors b
on a.au_lname = b.au_lname and b.au_lname = 'white'
where b.au_lname is null

The point is that a <> will table scan so if you can use another index you should or reduce the size of the result set before using it.
In your example you want to return almost the whole table (probably) so a table scan is a good idea.

==========================================
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.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-17 : 09:46:20
nr, I don't mean to derail your thread here, but I don't get the '<>' thing.

drop table #test
go
create table #test (
    rownum int identity(1,1) not null primary key,
    n int not null )

create nonclustered index idx_n on #test(n)    

insert #test (n)
select
    n%10
from
    numbers  --tally table with ints from 1 to 10000
go

 
Looking at the plan of ...

select
    *
from
    #test
where
    n <> 1

 
I am seeing a 0.0268 cost and a index seek of idx_n ... now looking at ...

select
    *
from
    #test a
    left join #test b
        on a.rownum = b.rownum and
           b.n = 1
where
    b.rownum is null

 
...It is presenting a 0.107, with a scan on the pk, a seek on idx_n. I don't see how the latter is better? What am I missing?

Jay White
{0}
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2002-10-17 : 10:03:10
The join version should be selecting a.*
You didn't mention that the join version gives completely the wrong row count estimate.

Shouldn't you be testing this too, just for completeness?

select *
from #test
where rownum not in (select rownum from #test where n = 1)

 


Edited by - Arnold Fribble on 10/17/2002 10:07:13
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-17 : 10:08:09
The a.* doesn't effect the plan, but yeah, I noticed that after the post ...

The not in version has roughly the same plan as the left join, except without the filter on b.rownum is null.

Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 10:12:21
I was just showing how you could avoid a <> - that's why I said it wasn't serious.

You are returning 99999 of 100000 rows so of course a scan is going to be most efficient. For this it is better to not use an index.
In fact I'm a bit surprised it does an index seek seeing as it should know that it has to return all rows but one - but then a <> is not easy to optimise.

==========================================
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.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-17 : 10:15:48
No, the #test is n%10, so it is returning 9999 records.... and it does use the index (idx_n) ... and its a seek at that? It's the exact same query plan and cost as an n=1 ... this is what I am trying to say ...

Jay White
{0}
Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts
Posted - 2002-10-17 : 10:20:22
I would say that every table should have a unique clustered index. Some problems have arisen that can be fixed when readding the clustered index, like freeing up data pages.

*************************
Someone done told you wrong!
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2002-10-17 : 10:26:17
So something more like this?

drop table #test
go
create table #test (
    rownum int identity(1,1) not null primary key,
    n int not null )

create nonclustered index idx_n on #test(n)    

insert #test (n)
select
    case when n % 2702 = 0 then n%10 else 1 end
from
    numbers  --tally table with ints from 1 to 10000
go

 
what the hell is a Merge Interval, anyway?


Edited by - Arnold Fribble on 10/17/2002 10:27:46
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 10:38:02
quote:

No, the #test is n%10, so it is returning 9999 records.... and it does use the index (idx_n) ... and its a seek at that? It's the exact same query plan and cost as an n=1 ... this is what I am trying to say ...

Jay White
{0}


OK doesn't really matter how many recs are in the table.
Using the index would be good for the = but is it good for <>?
Why would you traverse index pages to get get to all recs bar one when you can just use the page chain - in this case I suspect a table scan would have been better.
<> does not restrict rows very much usually and doesn't allow useful index usage usually - that's why you should be cautious about it, same as "not in".

==========================================
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.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 10:52:19
(v7 sp2)

set nocount on
create table #a (i int, j char(2000))
create table #b (i int, j char(2000))

declare @i int
select @i = 10000
while @i > 0
begin
select @i = @i -1
insert #a select convert(int,rand() * 1000), ''
end

create index ix on #a (i)
create clustered index ix1 on #a (i)

insert #b
select *
from #a
where i <> 50

With the clustered index it does a range scan of the clustered index. With the same index as non-clustered it does a table scan.


==========================================
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 10:52:49
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2002-10-17 : 11:10:47
quote:

With the clustered index it does a range scan of the clustered index. With the same index as non-clustered it does a table scan.

Er, yes. I'm obviously missing something here, because that's exactly what I'd expect to happen.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-10-17 : 11:19:22
Yep - the point is a <> won't make effective use of an index.

==========================================
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.
Go to Top of Page

VyasKN
SQL Server MVP &amp; SQLTeam MVY

313 Posts
Posted - 2002-10-17 : 12:02:40
I do have a similar article at: http://vyaskn.tripod.com/coding_conventions.htm

Been a while since I wrote it. Some of the points might be out dated or need updating.

quote:

A friend needs 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 (that'll be fun - none there at the moment :( ).

I'm sure I've been involved in a discussion about this and seen some quite good documents - of course I can't find any now.

==========================================
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.


--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site