MS SQL Server - Rewrite to Sargable

Author

Rewrite to Sargable

cbrichards via SQLMonster.com

2006-11-29, 7:13 pm

Is there a way to rewrite either of the following where clause items to allow
them to be SARGABLE?

1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).

2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).

--
Message posted via http://www.webservertalk.com

Tom Moreau

2006-11-29, 7:13 pm

For #2, try:

Team.Codes not like '-%'

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via webservertalk.com" <u3288@uwe> wrote in message
news:6a089625d4de6@u
we...
Is there a way to rewrite either of the following where clause items to
allow
them to be SARGABLE?

1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
field is a date. TextValue datatype is varchar(25).

2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
Codes datatype is varchar(10).

--
Message posted via http://www.webservertalk.com

John Bell

2006-11-30, 7:14 pm

Hi

For #1

If Team.TextValue should only be dates, you may want to validate it on the
UI. If it can be dates and other entries you may want to hold a datetype flag
instead.

John

"cbrichards via webservertalk.com" wrote:

> Is there a way to rewrite either of the following where clause items to allow
> them to be SARGABLE?
>
> 1. ISDATE(Team.TextValue) = 1 -- Ensure that the value in the TextValue
> field is a date. TextValue datatype is varchar(25).
>
> 2. LEFT(Team.Codes, 1) <> '-' -- Exclude the Codes that begin with a '-'.
> Codes datatype is varchar(10).
>
> --
> Message posted via http://www.webservertalk.com
>
>

Sponsored Links