How to make this expression SARGable

a quick qn : i have this filter in my where clause and i know this is not
sargable. how can i make it one? ( i want to make use of a covered index on
prodid column)
any help is appreciated
SELECT ... FROM ...
WHERE (left(prodid,11) like replace(left(@.pegid,11), '*', '%'))
col/var datatype and width are :
prodid char(14)
@.pegid varchar(11)Maybe something like this (untested)
declare @.SargID varchar (12)
select SargID = replace(left(@.pegid,11), '*', '%') +'%'
SELECT ... FROM ...
WHERE prodid like SargID
http://sqlservercode.blogspot.com/atom.xml
"paraa" wrote:

> a quick qn : i have this filter in my where clause and i know this is not
> sargable. how can i make it one? ( i want to make use of a covered index o
n
> prodid column)
> any help is appreciated
> SELECT ... FROM ...
> WHERE (left(prodid,11) like replace(left(@.pegid,11), '*', '%'))
> col/var datatype and width are :
> prodid char(14)
> @.pegid varchar(11)
>|||are you trying for something like this
SELECT * FROM EMPLOYEES WHERE prodid like '***********%'
--
Regards
R.D
--Knowledge gets doubled when shared
"paraa" wrote:

> a quick qn : i have this filter in my where clause and i know this is not
> sargable. how can i make it one? ( i want to make use of a covered index o
n
> prodid column)
> any help is appreciated
> SELECT ... FROM ...
> WHERE (left(prodid,11) like replace(left(@.pegid,11), '*', '%'))
> col/var datatype and width are :
> prodid char(14)
> @.pegid varchar(11)
>|||i'm trying to search product ids (prodid) in the database that match the use
r
entered argument in @.pegid which can contain wildcard *
"R.D" wrote:
> are you trying for something like this
> SELECT * FROM EMPLOYEES WHERE prodid like '***********%'
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "paraa" wrote:
>|||"paraa" <paraa@.discussions.microsoft.com> escribi en el mensaje
news:6E02D64A-E654-4511-BC48-BC39FEF6D3B9@.microsoft.com...
> i'm trying to search product ids (prodid) in the database that match the
> user
> entered argument in @.pegid which can contain wildcard *
>
You cant do that, if you want to use a index the where must be some
like this:
where a like 'xxxx%'
The % must be in the last position.
The only solution if you want query like this 'xxx%xxx' it is to use
Full-Text indexes, lok in the BOL for it.
> "R.D" wrote:
>|||To add, this is true in SQL Server 2000. SQL Server 2005 is smarter in this
respect. It collects string summary statistics to improve cardinality
estimations for LIKE predicates with arbitrary wildcards. It collects
information about the frequency distribution of substrings for character
columns. As you mentioned, SQL Server 2000 typically considered using an
index when the LIKE pattern has a constant prefix (col LIKE 'const%'). SQL
Server 2005 can make selectivity estimations even when the pattern doesn't
have a constant prefix (col LIKE '%const%'). In some cases, it can use an
index scan followed by lookups instead of opting for a table scan to begin
with.
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Tako" <mi@.correo.es> wrote in message
news:ulkWTuyyFHA.2540@.TK2MSFTNGP09.phx.gbl...
> "paraa" <paraa@.discussions.microsoft.com> escribi en el mensaje
> news:6E02D64A-E654-4511-BC48-BC39FEF6D3B9@.microsoft.com...
> You cant do that, if you want to use a index the where must be some
> like this:
> where a like 'xxxx%'
> The % must be in the last position.
> The only solution if you want query like this 'xxx%xxx' it is to use
> Full-Text indexes, lok in the BOL for it.
>
>|||thx a lot all for ur time and inputs...greatly appreciated.
"paraa" wrote:

> a quick qn : i have this filter in my where clause and i know this is not
> sargable. how can i make it one? ( i want to make use of a covered index o
n
> prodid column)
> any help is appreciated
> SELECT ... FROM ...
> WHERE (left(prodid,11) like replace(left(@.pegid,11), '*', '%'))
> col/var datatype and width are :
> prodid char(14)
> @.pegid varchar(11)
>|||Couldn't you do...
SELECT
pkID
, blah1
, blah2
FROM
tblSomething
WHERE
pkID IN ( select pkID from tblSomething WHERE <your-clause> )
It might return the records slower, but because it has searched on a list of
indexed keys it should be updatable?
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:8B46071E-60AF-45E9-A951-7D522F772A15@.microsoft.com...
> a quick qn : i have this filter in my where clause and i know this is not
> sargable. how can i make it one? ( i want to make use of a covered index
on
> prodid column)
> any help is appreciated
> SELECT ... FROM ...
> WHERE (left(prodid,11) like replace(left(@.pegid,11), '*', '%'))
> col/var datatype and width are :
> prodid char(14)
> @.pegid varchar(11)
>|||> where a like 'xxxx%'
> The % must be in the last position.
> The only solution if you want query like this 'xxx%xxx'
Actually, the index can be used for at least a *portion* of this plan, e.g.
identifying the rows that start with xxx ...