Results 1 to 8 of 8

Thread: Good SQL Query

  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Good SQL Query

    Folks, i've got a table with a column; ACCOUNT VARCHAR(30). All the values numeric though. (leave abt the datatype yet).
    The column is clustered indexed.

    SELECT * FROM MYTABLE WHERE LEFT(ACCOUNT,3)='123'
    execution plan shows CLUSTERED INDEX SCAN.

    SELECT * FROM MYTABLE WHERE ACCOUNT LIKE '123%'
    execution plan shows CLUSTERED INDEX SEEK.

    How, why. Why doesn't the optimizer works good for the first query?


    Howdy!

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool SQL Query

    Can you refraze the question. Can it be built with the query anylzer?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCrowley
    LEFT is seen by the optimizer as similar to UPPER. In short, the query plan looks at the function result as an unknown value, and thus the table scan.
    well, that's pretty stupid of it, eh

    oh, i don't mean in the general sense, i am forever telling people not to do stuff like

    ... where year(transdate) = year(getdate())

    i mean specifically in the case of the LEFT function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Quote Originally Posted by garrydawkins
    Can you refraze the question. Can it be built with the query anylzer?
    create table mytable (account varchar(10))

    go
    create clustered index myindex on mytable(account)
    go
    declare @v int
    set @v=1
    while @v<9000
    begin
    insert mytable select 'abcdefgh'
    set @v=@v+1
    end

    -- table scan
    select * from mytable where left(account,3)='abc'

    -- index seek
    select * from mytable where account like 'abc%'

    drop table mytable

  6. #6
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    is there an index on that column at all ?

  7. #7
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    It's likely that the scan occurs because left has to retrieve the whole thing while the like only retrieves the first three characters. The clustered index is going to organize that column by varchar physically on the disk, so the like statement will retrieve two indexes and everything in between, versus the left which will have to check every record "in between".

    Does that make sense ?

    Cheers,
    -Kilka

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •