It's uses the index either way, specifically it still index seeks but ends up doing the same thing as an index/table scan although slower. It normally makes a p

It's uses the index either way, specifically it still index seeks but ends up doing the same thing as an index/table scan although slower. It normally makes a plan based on first execution and uses parameter sniffing, so it might optimize for no index seek if the first request has wildcard in front. You can pass it a hint OPTION (RECOMPILE) that will make it act like PG and make new plan every time, if its a query with wildly varying parameters that doesn't execute as often this can make sense, most of the time it doesn't due to planning overhead. There is also the OPTIMIZE FOR hint that can have it make a plan optimized for the more common of the parameter values if you don't want it planning every execution regardless of value passed on first execute.