Issue about the SQL server handling of "where"

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 2008 Forums

Transact-SQL (2008)

Issue about the SQL server handling of "where"

Author

Topic

Mosk
Starting Member

1 Post

Posted - 2010-10-29 : 05:32:46

Hello all!

I have a issue about the SQL Server handling of the "Where" clause.

It seems that the "Where" variables is not running at the same time, is it possible that the SQL Server has some kind of internal order it will produce the resultset from there "Where" clause?

Look at this example:

--Build Table
CREATE TABLE [dbo].[Table_1](
[ActNo] [int] NOT NULL,
[BsNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ActNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--Insert 2 rows

Insert into Table_1(ActNo, BsNo) Values(1,'19')
Insert into Table_1(ActNo, BsNo) Values(2,'197203081111')

--Select 1
select actno,BsNo
from table_1
where
isdate(left(BsNo,8))=1
and (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)
and len(bsno) =12

--Select 2
select actno,BsNo
from table_1
where
isdate(left(BsNo,8))=1
and (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)

--Select 3
select actno,BsNo
from table_1
where
isdate(left(BsNo,8))=1

The "Select 1" will result in 1 row. The row with ActNo=19 is not selected.

The "Select 2" will end with "Conversion failed when converting date and/or time from character string."
Why will this happen? The Row with "19" should not be selected becourse it is not a date.

THe "Selct 3" will result in 1 row. The row with ActNo=19 is not selected. That is correct, becourse "19" is not a date.

My conclusion is this:
In the "Select 2" the SQL Server will break down the TSQL "Where" clause in 2 different lines:
1. isdate(left(BsNo,8))=1
2. (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)
after this the SQL Server will try to run line 2 first and this will end with conversion error. Same as if I try to run this TSQL query:
select actno,BsNo
from table_1
where
(datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)

Does anyone now how this works? This makes me wonder if my "Where" clauses is always correct and should I write them in any different way?

Best regards,

Krister Selenius

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 06:14:14

The order that predicates will be evaluated depends on available indexes, on the plan generated, on which ones SQL thinks will be quicker to do first. You cannot make assumptions about the order that predicates will be evaluated.

If you have something like an ISDate and conversion combo, use a case statement or a subquery.

--
Gail Shaw
SQL Server MVP

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 06:47:35

No reason for sql server (or any database engine) to execute in the order you specify unless you tell it to.
Doubt if even a force order hint would help.
Even if the query happened to work without error that would be dependent on the optimiser. A change of data/statistics could cause it to fail at any time.

You could try
select actno,BsNo
from table_1
where
case when isdate(left(BsNo,8))=1
then case when (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18) then 1
else 0 end
else 0 end = 1

or maybe
select actno,BsNo
from (
select actno,BsNo
from table_1
where isdate(left(BsNo,8))=1
) a
where (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)

This used to be fairly safe but the optimiser is fairly smart now and can merge into a single query to give an error. creating 2 derived tables is safer

select actno,BsNo
from
select * from
(
select actno,BsNo
from table_1
where isdate(left(BsNo,8))=1
) a
) b
where (datediff(yyyy,left(BsNo,8),convert(char(8),getdate(),112))>18)

I would be tempted to use a temp table for this.

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

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 08:25:14

The forceorder hint forces the order of joining for tables, not the order of evaluation of the where clause.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources