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%'
guess that there's a NoneClustered Index on [PNAME] column of [tbltest]. when running Queries, Excution plan show index Look For Query 1 and Index Scan for Query 2. i was expecting that Excution Paln Show Index Scan For queries,but due to using parameter within the first Query,it Show Index Seek. What exactly i the mater? both in query we used '%' at oth side,and realize that within this condition ,sql doesn't consider index why in first Query Excution Plan Show Index Seek? thanks
Query one utilizes a parameter, query 2 a continuing.
The program for query 2 won't be used again should you alter the constant value.
The query for plan 1 could be. Within this situation, SQL Server (simply) leaves it's options open for re-using the program.
Also known as: the queries are not exactly the same.
Should you force parameterisation, then you definitely should make both queries run like query 1. However I haven't attempted...
Should you choose DBCC SHOW_STATISTICS you are cooking and also the index that's getting used, search for "String Index = YES" within the first row from the output. SQL Server keeps some kind of additional stats for satisfying queries like '%x'
Within the first query, you'll most likely see calculated scalar values - try looking in the query arrange for LikeRangeStart('%'+@title+'%'). The Index Seek is against individuals values instead of the index scan against %sara%.
The salt water evaporates I'm not sure. Why SQL Server wouldn't be wise enough to transform 'sara' to some constant and perform the query exactly the same way I'm not sure either. However I think that's what's happening.
Against %sara% it will a catalog scan, reading through the whole index. Against %+@title+% it produces RangeStart/RangeEnd/RangeInfo calculated values and uses these to do a catalog seek in some way benefiting from the addtional string statistics.
I believe that Mike is on course about regardless if you are striking the index or otherwise. Your follow-up regarding cost may need much more of an awareness about how exactly your computer data is shipped inside the table. I have seen occasions when striking a catalog is much more pricey because of do the requirement for two disk reads. To know why, you will need to understand how your computer data is shipped over the index, the number of records will squeeze into a webpage, and what your caching plan is.
I'll state that it might be hard to tune a question having a leading %. The database will have to fully traverse your index (or table) striking every node searching for something that consists of "sara". Based on your requirements, you might like to consider full-text search (i.e., may be the parameter value within this query used since it is provided as input from the user of the application).