August 31, 2004 at 12:50 pm
My problem comes from a statement that casts a varchar to an integer within the WHERE clause. The column being casted is guaranteed to contain an integer (within the varchar(10) column) when several other conditions are true. SQL Optimizer, however, does not always test these several other conditions in the order specified within the WHERE clause. This causes it to try to cast a nonnumeric value to an integer causing it to fail. It works 99% of the time.
Is there a way to force SQL to evaluate the WHERE clause within a specific order?
Simplified Example:
--Setup a table and populate it with a bunch of values:
if object_id('TestWhereOrder') IS NOT NULL
BEGIN
DROP table TestWhereOrder
END
if object_id('TestWhereOrder') IS NULL
BEGIN
create table TestWhereOrder
(
TestWhereOrderID int not null,
TypeCode varchar(1) not null,
Code varchar(20)
)
END
TRUNCATE TABLE TestWhereOrder
DBCC FREEPROCCACHE
DECLARE @intCounter int
SELECT @intCounter = 0
WHILE @intCounter <= 100
BEGIN
INSERT INTO TestWhereOrder (TestWhereOrderID, TypeCode, Code )
VALUES (@intCounter , 'T' , cast(@intCounter as varchar) + 'A')
INSERT INTO TestWhereOrder (TestWhereOrderID, TypeCode, Code )
VALUES (@intCounter , 'T' , cast(@intCounter as varchar) + 'B')
INSERT INTO TestWhereOrder (TestWhereOrderID, TypeCode, Code )
VALUES (@intCounter , 'T' , cast(@intCounter as varchar) + 'C')
INSERT INTO TestWhereOrder (TestWhereOrderID, TypeCode, Code )
VALUES (@intCounter , 'T' , cast(@intCounter as varchar) + 'D')
SELECT @intCounter = @intCounter + 1
END
--Setup one value as numeric and flag it with a TypeCode to N for Numeric
UPDATE TestWhereOrder
SET Code = '100',
TypeCode = 'N'
WHERE TestWhereOrderID = 100
AND Code = '100D'
--FAILS (The cast is done before it tests TypeCode for ‘N’ (Numeric)
SELECT *
FROM TestWhereOrder
WHERE TestWhereOrderID = 100
AND cast(Code as int) = 100
AND TypeCode = 'N'
--Succeeds, since Optimizer Tests the TypeCode for N (Numeric) before it attempts the cast
--At times, however, this will fail, since the Optimizer may rearrange the WHERE clause for performance.
--Is there any way to insure the WHERE clause will be executed in its EXACT order?
SELECT *
FROM TestWhereOrder
WHERE TestWhereOrderID = 100
AND TypeCode = 'N'
AND cast(Code as int) = 100
August 31, 2004 at 12:59 pm
As I recall the cast is going to bypass any indexing for that column anyway so why not use a case?
AND case when isnumeric(code) = 1 then cast(code as int) else 0 end = 100
August 31, 2004 at 1:14 pm
I can actually use a case, which is something that I should have seen earlier. It will not be very elegant, since the cast is in the where clause multiple times. This, however, is what you get, when you inherit a database design that has grown out of control. Looking forward to throwing it all away and starting from scratch.
Thanks!
Sean
September 1, 2004 at 12:23 am
Not elegant, but should work
SELECT *
FROM (Select * FROM TestWhereOrder WHERE TypeCode = 'N') NumericTests
WHERE TestWhereOrderID = 100
AND cast(Code as int) = 100
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply