Article: What I Wish Developers Knew About SQL Server (Presentation)

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

Site Related Forums

Article Discussion

Article: What I Wish Developers Knew About SQL Server (Presentation)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-10-11 : 08:28:43

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 - 2007-10-11 : 17:33:31

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.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-11 : 18:02:47

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

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-10-11 : 18:18:43

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.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-10-11 : 18:19:01

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.

xianve
Starting Member

3 Posts

Posted - 2007-10-11 : 18:29:23

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?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-10-12 : 08:35:15

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.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-12 : 08:44:55

@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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-12 : 09:29:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources