Sql extrusion plan shows different results for the same input

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 the column [PNAME] in [tbltest] has a NoneClustered Index. when starting queries, in the list of indicators, the Seek For Query 1 index and Index Scan for Query 2 are called up. I expected Excitation Paln Show Index Scan. For both queries, but due to the use of the parameter in the first query, it shows an index search. So what am I mater? in both queries we used "%" on the one hand, and we know that in this state sql does not take into account the index, but why in the first search query the query index? thank

Answer 1

, 2 .

2 , .

1 . SQL Server () .

AKA: .

, 1. ...

Answer 2

DBCC SHOW_STATISTICS , "String Index = YES". SQL Server , "% x"

, , - LikeRangeStart ('%' + @name + '%'). % sara%.

, . SQL Server , 'sara' , . , .

% sara% , . % + @name +% RangeStart/RangeEnd/RangeInfo , - .

Answer 3

, , . , , . , - . , , , , .

, %. ( ) node , "sara". (.. , ).