kk1173 (4/2/2013)
If I have a query like
Select * from table1 WHERE ColVal IN (Select AllVals from table2)Is it not better to have the following instead? It will bring in those values that have trailing and leading spaces also.
Select * from table1 WHERE LTRIM(RTRIM(ColVal))
IN (Select LTRIM(RTRIM(AllVals)) from table2)
The query you posted second is not sargable. What that means is that if there is an index on ColVal it will be ignored and the engine will perform a scan instead of a seek.
From Jeff's post on page 1:
SARG stands for "Search ARGument" and "SARGable" has come to mean that the search arguments in a WHERE clause, ORDER BY, and/or ON clause are capable of doing an INDEX SEEK if the appropriate index is available and used.
Search arguments that modify a column will only allow for either a table scan (includes Clustered Index Scan) or a non-Clustered Index Scan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/