How to obtain a good execution plan for (LIKE @p) when the pattern is of the form 'prefix%'

I'm using an ORM that executes queries of the form:

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

Col is indexed and the index is covering. The predicate is very selective. The tables has 500,000 rows.

I'm always querying for a string prefix (e.g. 'prefix%'). Clearly, this is not statically known to SQL Server here. But I know that SQL Server is basically capable of seeking based on a LIKE pattern with a prefix.

I would like SQL Server to discover at execution time the relevant range of the index to scan and only scan that range. The execution plan that I'm getting has an index scan, though.

The ORM does not support OPTION (RECOMPILE) and I'm not sure it would be a good option since the queries execute in about 20 milliseconds. (However I would like this query to go faster. With a seek this should be able to run in ~1ms).

Can I somehow make SQL Server perform a seek here?

I would like SQL Server to discover at execution time the relevant
range of the index to scan and only scan that range.

This is exactly the plan I get.

CREATE TABLE T(Columns VARCHAR(50), 
               Col VARCHAR(50), 
               INDEX IX(Col, Columns));

DECLARE @p0 VARCHAR(50) = 'foobar%'

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

See Dynamic Seeks and Hidden Implicit Conversions for more about this.

Check that the index is indeed covering and that the datatype of the parameter is correct for the column definition (i.e. if the column is varchar and the parameter is nvarchar this can cause a scan).

StackExchange Database Administrators Q#129589, answer score: 6