SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: What I Wish Developers Knew About SQL Server (Presentation)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/11/2007 :  08:28:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This is the latest version of this presentation including all demonstration scripts. The presentation has been converted to a PDF to make viewing easier. The presentation provides an overview of query plans, query tuning tips, nulls, error handling and transactions. Here is the ZIP file containing the presentation and demo scripts.

Read What I Wish Developers Knew About SQL Server (Presentation)

xianve
Starting Member

3 Posts

Posted - 10/11/2007 :  17:33:31  Show Profile  Reply with Quote
What is bad about:
-- Using OR @Parm IS NULL
DECLARE @SalesOrderID INT
SET @SalesOrderID = 43671

SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID OR @SalesOrderID IS NULL

I understand that dynamic sql is better probably, but dynamic sql is a pain to work with.

I would re-write the query like this:
SELECT *
FROM Sales.SalesOrderHeader
WHERE (@SalesOrderID IS NULL OR (@SalesOrderID IS NOT NULL AND SalesOrderID = @SalesOrderID))

That way the NULL is checked first and if true, there is no WHERE executed.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 10/11/2007 :  18:02:47  Show Profile  Visit jezemine's Homepage  Reply with Quote
the example on nullable types is a bit off the mark: string? has no point because strings are reference types - all reference types may be null, so there's no no reason to wrap them in a Nullable<T>.

it's more interesting for value types, such as int, short, long, structs, etc. That is, you can't write this:

int i = null;

but you can do this:

int? i = null;




elsasoft.org
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/11/2007 :  18:18:43  Show Profile  Visit graz's Homepage  Reply with Quote
quote:
Originally posted by xianve

What is bad about:
-- Using OR @Parm IS NULL
DECLARE @SalesOrderID INT
SET @SalesOrderID = 43671

SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderID OR @SalesOrderID IS NULL

I understand that dynamic sql is better probably, but dynamic sql is a pain to work with.

I would re-write the query like this:
SELECT *
FROM Sales.SalesOrderHeader
WHERE (@SalesOrderID IS NULL OR (@SalesOrderID IS NOT NULL AND SalesOrderID = @SalesOrderID))

That way the NULL is checked first and if true, there is no WHERE executed.




Those examples won't use the index on the column. You'll get the correct result but it will take a table scan to get it.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/11/2007 :  18:19:01  Show Profile  Visit graz's Homepage  Reply with Quote
quote:
Originally posted by jezemine

the example on nullable types is a bit off the mark: string? has no point because strings are reference types - all reference types may be null, so there's no no reason to wrap them in a Nullable<T>.

it's more interesting for value types, such as int, short, long, structs, etc. That is, you can't write this:

int i = null;

but you can do this:

int? i = null;




elsasoft.org



Yep, should have done a better job there.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

xianve
Starting Member

3 Posts

Posted - 10/11/2007 :  18:29:23  Show Profile  Reply with Quote
quote:
Originally posted by graz

Those examples won't use the index on the column. You'll get the correct result but it will take a table scan to get it.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



So the only way to do it is to have dynamic SQL? Is there no other way to control dynamic what where clauses to execute?
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/12/2007 :  08:35:15  Show Profile  Visit graz's Homepage  Reply with Quote
I've been searching for a better solution than dynamic SQL. I haven't found one yet.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 10/12/2007 :  08:44:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
@xianve:
order of equal importance conditions in the where clause is irrelevant.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/12/2007 :  09:29:55  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
xianve --

there are some options here to eliminate OR's in conditional WHERE clauses here: http://weblogs.sqlteam.com/jeffs/archive/2007/09/18/sql-conditional-where-clauses.aspx

The key is to test different options to see which ones perform better.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000