October 4, 2016 at 2:48 pm
How does SQL Server handle casts in the WHERE clause? Logical query processing (LQP) in SS says that the WHERE is the second step of LQP, but if a user is casting a value to something else in the WHERE, does it apply the cast first? Or does it wait to apply the cast with the SELECT portion?
Here's my example (and yes, I know the design is bad and needs to be reworked):
There is a table that stores a numeric value in varchar(3). (It does this because the leading zero is required for reporting.) If I wanted to find all values that are larger than 100 and used the following query, where is the cast applied?
SELECT
column1,
column2,
column3,
column4
FROM My_Table
WHERE
CAST(my_value AS INT) > 99
ORDER BY column2 DESC
My understanding is that LQP would grab the data from the table, apply the cast and filter out any "my_value" less than 100, then move on to the select list and finally order the columns.
However, there has been some dissension with that theory. The debated point is that the table would grab the data, select the columns, filter the data on the cast, then apply the order.
Can someone please help me understand this better?
October 4, 2016 at 3:11 pm
SQL Server will read all qualifying rows (in this case, ALL of them) then eliminate the nonqualifying rows in either a residual predicate i.e. as rows are read (common and usually preferable) or alternatively, and less common, a later filter step.
Try your query with equals instead of greater than, it's easier to visualise and easier too, to map to the execution plan. I think you're approaching this from slightly the wrong angle.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 4, 2016 at 3:38 pm
I think the main issue is a confusion between logical query processing and physical query processing. The physical query processing does not need to match the logical query processing as long as it can guarantee that the final results will match. The logical query processing order is strictly set. You can determine the physical query processing order by reading the query plan.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2016 at 4:01 pm
drew.allen (10/4/2016)
I think the main issue is a confusion between logical query processing and physical query processing. The physical query processing does not need to match the logical query processing as long as it can guarantee that the final results will match. The logical query processing order is strictly set. You can determine the physical query processing order by reading the query plan.Drew
I guess I'm not following. What do you mean by the physical query processing? Can you please elaborate on that?
October 4, 2016 at 8:15 pm
First, ditto everything Chris said.
How does SQL Server handle casts in the WHERE clause?
Not all that well. You are going to get an index scan instead of a, much more desirable, index seek. This will be the case regardless of if you CAST/CONVERT in your WHERE clause.
Logical query processing (LQP) in SS says that the WHERE is the second step of LQP, but if a user is casting a value to something else in the WHERE, does it apply the cast first?
Yes. And even if you don't CAST/CONVERT something in the WHERE clause the optimizer may still need to do an Implicit conversion as would be the case if your WHERE clause read, "WHERE myVal > 100".
Switching gears a little, if you wanted to get an index seek you could do so with an indexed view. Consider the following sample data:
CREATE TABLE dbo.MyTable (MyVal varchar(3) UNIQUE CLUSTERED NOT NULL);
INSERT dbo.MyTable VALUES ('11'),('050'),('101'),('098'),('504'),('145');
Both of these queries will give you the same results and an index scan:
SELECT * FROM dbo.MyTable WHERE MyVal > 100;
SELECT * FROM dbo.MyTable WHERE CAST(MyVal as smallint) > 100;
...but if you created this indexed view:
CREATE VIEW dbo.vwMyTable WITH SCHEMABINDING AS
SELECT MyVal = CAST(MyVal AS smallint)
FROM dbo.MyTable;
GO
CREATE UNIQUE CLUSTERED INDEX xxx ON vwMyTable(MyVal);
GO
...you can get the same result with an index seek like so:
SELECT MyVal
FROM dbo.vwMyTable WITH (NOEXPAND)
WHERE MyVal > 20;
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply