Re: Priority Order On Where Clauses

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/23/04 

Date: Wed, 23 Jun 2004 15:25:01 +0200

You cannot trust SQL Server to not hit the bad rows. Not even by pushing the filter inside a derived
table. I've been there. Put the stuff in a temp table and work against that. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed Fultz" <efultz@yahoo.com> wrote in message news:O6blHRSWEHA.4056@TK2MSFTNGP11.phx.gbl...
> Is there a priority order on a where clause?  Or does SQL check all of the
> where clause conditions all of the time?  I have a case where I can,
> sometimes, hit non-numeric data in a column (caused by some bad
> conversions).  Right now we have the following where clause:
>
> WHERE convert(integer, ?) BETWEEN convert(integer, low_street_num) AND
> convert(integer, high_street_num)
>
>  AND (street_name = ? OR street_alias_name = ?)
>
>  AND city = ?
>
>  AND state_cd = ?
>
>  AND zip_cd LIKE ?
>
>  AND (juris_cd = ? OR juris_cd IS NULL)
>
>  AND (side = ? OR side = 'B')
>
>  AND status_cd = 'A'
>
> AND (side = ? OR side = 'B')
>
>  AND status_cd = 'A'
>
> I am wondering if we were to change to put the status_cd = 'A' first would
> the query skip all entries whose status_cd is not 'A' and not even try to do
> the convert(integer) statement at all?  Or is it going to try to do the
> convert(integer) regardless and my only solution is to make some routine to
> fix the non-numeric data or adjust this sql to ensure the data is numeric
> before trying to do the convert?
>
>
> -- 
> Edward Fultz
> Sr. Software Engineer
> Pamet Systems
> Acton, Ma
> www.pametsystems.com
>
>

Relevant Pages

  • Re: Getting list of recently added IDENTITY items
    ... num_of_hits for each hit within the same 3ms timeframe. ... The problem is fairly apparent here, because SQL Server has a fairly ... floating-point as a primary key. ... IDENTITY is a discrete set of value that is easy ...
    (comp.databases.ms-sqlserver)
  • Re: Null Values
    ... Am using SQL Server 2000. ... Have been using VB for years and never hit any serious data problems like ... sure where to place the fix code, or what data object to apply it to. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Null Values
    ... > Am using SQL Server 2000. ... > Have been using VB for years and never hit any serious data problems like ... > not sure where to place the fix code, or what data object to apply it to. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recurring import of linked text file data
    ... indication that the amount of data being imported was exceeding ... calculate how much time you have before you would hit my arbitrary size. ... The mdb file was about 256mb, ... migrate it to SQL Server 2000. ...
    (microsoft.public.access.externaldata)
  • Re: Just passed 70-228 !
    ... After the exam I received a printed report with a sectional report. ... There are 6 sections, each one represented with a bar graph that shows how good did you perform in each area, but it doesn't tell you how many questions per area or how many did you hit. ... I didn't have previous experience in SQL Server, but 4 years working with DB2 on mainframe. ... This was my first exam after getting MCSA in W2K. ...
    (microsoft.public.cert.mcdba)