Say we have a stored procedure that has two queries in it – the second query uses a recompile hint, and you might recognize it from my parameter sniffing session:
CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles and gets different plans: */ SELECT TOP 10000 * FROM dbo.Users WHERE Reputation = @Reputation ORDER BY DisplayName OPTION (RECOMPILE); END GO
The first query will always get the same plan, but the second query will get different plans and return different numbers of rows depending on