Can you give an example whats dangerous about converting a nvarchar with only ascii (0-127) then using the index otherwise fallback to a scan?

If we simply went to UTF-8 collation using varchar then this wouldn't be an issue either, which is why you would use varchar in 2026, best of both worlds so to speak.

▲

wvenable 2 hours ago | parent [-]

For a literal/parameter that happens to be ASCII, a person might know it would fit in varchar, but the optimizer has to choose a plan that stays correct in the general case, not just for that one runtime value. By telling SQL server the parameter is a nvarchar value, you're the one telling it that might not be ASCII.

▲

jstrong an hour ago | parent [-]

optimizer can't inspect the value? pretty dumb optimizer, then.

	▲	wvenable an hour ago \| parent [-]
		Running the optimizer for every execution of the same query is... not very optimal.