Sql Excution Plan Shows Different result for same inputs

Question

declare @name varchar(156)
set @name ='sara'
--Query 1:
SELECT [PNAME]  FROM [tbltest] where  [PNAME]  like '%'+@name+'%'

--Query 2:
SELECT [PNAME]  FROM [tbltest] where  [PNAME]  like '%sara%'

suppose that there is a NoneClustered Index on [PNAME] column of [tbltest]. when running Queries, Excution plan show index Seek For Query 1 and Index Scan for Query 2. i expected that Excution Paln Show Index Scan For both queries,but because of using parameter in the first Query,it Show Index Seek. So what i the mater? in both query we used '%' at oth side,and know that in this state ,sql does not consider index but why in first Query Excution Plan Show Index Seek? thanks

Answer 1

If you do DBCC SHOW_STATISTICS on your table and the index that is being used, look for "String Index = YES" in the first row of the output. SQL Server maintains some sort of additional stats for satisfying queries like '%x'

In the first query, you'll probably see computed scalar values - look in the query plan for LikeRangeStart('%'+@name+'%'). The Index Seek is against those values as opposed to the index scan against %sara%.

How this works I don't know. Why SQL Server would not be smart enough to convert 'sara' to a constant and do the query the same way I don't know either. But I think that's what's going on.

Against %sara% it does an index scan, reading the entire index. Against %+@name+% it creates RangeStart/RangeEnd/RangeInfo computed values and uses them to do an index seek somehow taking advantage of the addtional string statistics.

Answer 2

thank u MikeW but still i am confusing. when running Queries against a table with more than 200,0000 records , Cost for Query 2 is more than Query 1 Query 1 ('%'+@name+'%') cost: 4% Query 2 ) cost: 96%

but when i do "set statistics time on " i see that elapsed time for Query 2 (%sara% ) is less than Query 1('%'+@name+'%'). it means that when using parameter it take more time to excute query.

i am confucing because now i do not know hot to tune my queris,by using parameters or not using it. if i use parameter so never understand when index scan will occured,because we see that excution plan show index Seek when using parameter plzz help.

Answer 3

Hi Memmed, I think that Mike is on the right track about whether you are hitting the index or not. Your follow-up regarding cost might need more of an understanding about how your data is distributed within the table. I've seen instances when hitting an index is more costly due to do the need for two disk reads. To understand why, you'll have to know how your data is distributed across the index, how many records will fit into a page, and what your caching scheme is.

I will say that it may be difficult to tune a query with a leading %. The database will need to fully traverse your index (or table) and hit every node looking for a value that contains "sara". Depending on your needs, you might want to consider full-text search (i.e., is the parameter value in this query used because it's provided as input from a user of your application).

Answer 4

Query one uses a parameter, query 2 a constant.

The plan for query 2 will not be reused if you change the constant value.

The query for plan 1 can be. In this case, SQL Server (simply) leaves it's options open for reusing the plan.

AKA: the queries are not the same.

If you force parameterisation, then you should make both queries run like query 1. But I haven't tried...