"IN verses OR" and other questions 4

[Catadmin]

Okay, here's a weird one.

I was going through some documents and found some recommended coding practices for Oracle (this does relate to this forum, I promise!). When going through it, I noticed a couple of things which are essentially the anti-thesis of my SQL Server coding practices.

This confuses me, so I thought I'd ask if you agree or disagree with this list (and why) when coding in SQL Server.

1) Make sure you code unqualified SQL, i.e., no table owner specified in the FROM clause

[blue]I hope this just refers to the "From dbo.Publishers" verses "From Publishers" part of the code instead of the whole S.D.O.T. stuff[/blue]

2) Instead of coding an inlist within your where clause, code it as an equal and an or (e.g. Where state = 'IL' or state = 'CA' rather than Where state in ('IL','CA')

[blue]That just feels wrong to me. Or maybe I'm just lazy DBA... @=)[/blue]

3) Try the NOT EXISTS clause rather than the NOT IN or HAVING within the Where clause

4) Using Oracle Functions will invalidate the index and can cause a full table scan

[blue]I must admit I don't have a clue on the last one. Does the same thing happen within SQL? I haven't seen that kind of behavior before.[/blue]

These aren't all the ones on the list. Just the ones that confused me or contradicted the way I write my T-SQL. Anyone care to comment? I'm all for learning new things.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

I'm not sure about all of your questions.

#2. I think using IN will cause the same execution plan as the Or's. It would be easy enough to check this.

#4. This one is absolutely true. Take a look at this page:

http://www.sql-server-performance.com/transact_sql.asp

** search within the page for "non-sargable" and start reading.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

#1 TRUE coding without object owner might have a performance penalty
#2 probably same plan
#3 true with not in you also have to worry about nulls
#4 functions on left side of an operator WILL cause scans instead of seeks
instead of left(col,1) use like 'a%' etc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

#1. What happens if the owner changes?

One thing to do is ask those questions in the Oracle forum and ask WHY? Then compare their answers to the way SQL Server does things.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

#1 If I'm reading it right, it says the opposite: don't qualify the table owner. I talked to someone who knows more Oracle than I do and he speculates that the reason for this might be that in Oracle, one usually has the same schema name as owner/user name. In SQL server, at least, specifying the owner name supposedly prevents another lookup and can prevent query recompilation.

#2 I also believe that in SQL server IN() expands to OR.

#3 what SQLDenis said about NULLS but also EXISTS is supposed to stop after it finds one matching value, so theoretically using IN would have to collect all the values. Benefit over HAVING should be obvious.

 

[SQLDenis]

here is an example of the problem with NOT IN that I am talkin about

create table Table1 (id int)

create table Table2 (id int)

insert Table1 values(1) 
insert Table1 values(2) 

insert Table2 values(1) 
insert Table2 values(null) 

select * from table1 where not exists(select * from Table2 where id =table1.id)



select * from table1 where id not in(select * from Table2 )

--you have to add is not null
select * from table1 where id not in(select * from Table2 where id is not null)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

George,

I went and looked up the link you refered me to and found:

In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.

Expressions that have the same column on both sides of the operator? Does that mean JOINS are automatically doomed to never use the indices unless you use hints?


Denis, #1 actually says to NOT use the qualified name. But ESquared seems to have answered that question later on in this thread. And SQLBill has a very good point regarding this.

All, thank you very much for your input. It's nice to see the different takes on all this stuff. @=)





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Cat,

I'm not really sure what that means. I know that joins will use indexes to speed up the join. I suppose what it means is... Same Table and Same Column. This is probably true because of the isnull issue. For example...

Declare @T Table(A int)

Insert Into @T Values(1)
Insert Into @T Values(2)
Insert Into @T Values(NULL)

Select * From @T Where a=a

At a quick glance, you would expect there to be 3 records in the output, but since NULL can never equal NULL. (There are actually 2 records returned.)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Definitely food for thought.

Thanks, All. Stars all around.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"