Does plan guide consider spaces or not?

Question

I'm reading the documentation to sp_create_plan_guide and I find it hard to understand. My questions are:

1. Are the white spaces considered or not when matching?
2. Is there any difference between white spaces and blank spaces?
3. What about leading and trailing spaces?
4. Where is the option to create Git issue against the docs, proudly presented here?

What I am missing here? Thanks in advance!

Answer 1

different worlds

SQL Server's documentation has always been a bit obtuse, but not necessarily incorrect. What you're looking at is information for two different things, plan guides for SQL statements, and plan guides for modules (stored procedures, scalar/multi-statement UDFs).

pudding proof 1

I'm going to use my local copy of the Stack Overflow database here, and create this index.

CREATE INDEX 
    u 
ON dbo.Users
    (Reputation) 
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Then create a plan guide for a statement:

EXEC sp_create_plan_guide 
    @name = N'I Have A Special Plan For This World', 
    @stmt = N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation = @Reputation;', 
    @type = N'SQL', 
    @params = N'@Reputation integer',
    @hints = N'OPTION (OPTIMIZE FOR (@Reputation = 1))';

For the statement, any variation in the executed text will break the plan guide.

/*Works, matches*/
DECLARE
    @s nvarchar(MAX)= N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation = @Reputation;',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO
/Doesn't work, comment/
DECLARE
    @s nvarchar(MAX)= N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation = @Reputation;--I Have A Special Plan For This World',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO
/Doesn't work, comment/
DECLARE
    @s nvarchar(MAX)= N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation = @Reputation;/I Have A Special Plan For This World/',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO
/Doesn't work, new lines/
DECLARE
    @s nvarchar(MAX)= N'
SELECT 
    u.* 
FROM dbo.Users AS u 
WHERE u.Reputation = @Reputation;',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO
/Doesn't work, spacing around =/
DECLARE
    @s nvarchar(MAX)= N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation=@Reputation;',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO
/Doesn't work, double spaces/
DECLARE
    @s nvarchar(MAX)= N'SELECT  u.*  FROM  dbo.Users  AS  u  WHERE  u.Reputation  =  @Reputation;',
    @p nvarchar(1000) = N'@Reputation integer';
EXECUTE sys.sp_executesql
    @s,
    @p,
    2;
GO

So, it is sensitive for this type of plan guide.

pudding proof 2

If we use a stored procedure instead, that sensitivity doesn't apply. For example:

CREATE OR ALTER PROCEDURE
    dbo.GetThatRep
(
    @Reputation integer
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
    SELECT 
        u.* 
    FROM dbo.Users AS u 
    WHERE u.Reputation = @Reputation;
END;
EXEC sp_create_plan_guide 
    @name = N'I Have A Special Plan For This World', 
    @stmt = N'SELECT u.* FROM dbo.Users AS u WHERE u.Reputation = @Reputation;', 
    @type = N'OBJECT', 
    @module_or_batch = N'dbo.GetThatRep',
    @hints = N'OPTION (OPTIMIZE FOR (@Reputation = 1))';  ;
GO
/Works, magically/
EXEC dbo.GetThatRep
    @Reputation = 2;

This will match and work as expected, despite white space differences between the plan guide and the stored procedure text formatting.

And of course, we clean up after ourselves here at Darling Data, LLC.

EXEC sp_control_plan_guide 
    N'DROP', 
    N'I Have A Special Plan For This World';  
GO