I am trying to gain a better understanding as to how SQL Server caches query plans.
For the following table(Populated with the values 1-500,000):
CREATE TABLE [dbo].[Test] ([ID] INT PRIMARY KEY);
And the following stored procedure:
CREATE PROCEDURE [dbo].[TestGet]
@ID INT
AS
BEGIN;
IF(@ID IS NOT NULL) BEGIN;
SELECT [ID]
FROM [dbo].[Test]
WHERE [ID] = @ID;
END;
ELSE BEGIN;
SELECT [ID]
FROM [dbo].[Test];
END;
END;
Making the following two calls:
EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;
Results in two different plans in the query plan cache:
If the stored procedure were re-written as:
CREATE PROCEDURE [dbo].[TestGet]
@ID INT
AS
BEGIN;
SELECT [ID]
FROM [dbo].[Test]
WHERE [ID] = @ID OR @ID IS NULL;
END;
And the same two calls were made:
EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;
Only a single query_hash, and query_plan_hash are cached:
The calls to the stored procedure have the same shape in both cases. Why does one the first stored procedure and call generate two cached plans, with a different query_hash, and a different query_plan_hash? What criteria is SQL Server using to generate these hashes?
