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!Can you refraze the question. Can it be built with the query anylzer?|||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.|||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|||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|||is there an index on that column at all ?|||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|||It's NonSargable

http://www.sql-server-performance.com/sql_server_performance_audit8.asp