quoted identifiers on filtered indices

Question

I´ve 2 questions. I am using a T-SQL Server 2014.

When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?

I´ve some SPs which have set quoted_identifiers to OFF, my question is, if I call the SP with QUOTED_IDENTIFIER ON, is it possible to inject these with values which have doublequotes inside?

The SP makes an insert and just uses the parameters. As I understand it, it should not take an effect if QUOTED_IDENTIFIER is set to ON or OFF.

This is my first SP which calls the second:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DECLARE @PreviousVersion bigint

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

IF @ID = 0 BEGIN
    EXEC dbo.GetNextDataID 'Object', @ID OUTPUT
    SET @PreviousVersion = 0
END
ELSE BEGIN
    SET @PreviousVersion = @Version
END

EXEC dbo.GetNextVersion 'Object', @Version OUTPUT

EXEC dbo.Insert_Object
    @ID,
    @Version,
    @PreviousVersion,
    @deleted,
    @Parent

The second looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON;

IF ISNULL(@ID, 0) = 0
BEGIN
    RAISERROR('Die ID ist 0 oder leer.', 11, 1)
    RETURN
END

IF ISNULL(@Version, 0) = 0
BEGIN
    RAISERROR('Die Version ist 0 oder leer.', 11, 2)
    RETURN
END

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

UPDATE dbo.ObjectTable 
SET ID = @ID 

INSERT INTO dbo.ObjectTable
            ( ID
            , Version
            , PreviousVersion
            , deleted
            , Parent)
        VALUES
            ( @ID
            , @Version
            , @PreviousVersion
            , @deleted
            , @Parent)

If I add the filtered index without setting QUOTED_IDENTIFIER to ON, it raises an error.

score 0 · Accepted Answer · answered May 29 '20 at 10:50

Parameter values containing double-quotes are not affected by the QUOTED_IDENTIFIER setting within the proc unless you use dynamic SQL. The setting affects how the T-SQL statements are parsed.

As long as the T-SQL proc code encloses string literals using ISO SQL standard single quotes like your example, you can safely recreate the proc from a session with QUOTED_IDENTIFIER ON to persist the setting as object meta-data. The QUOTED_IDENTIFIER ON setting will be used at run time and allow you to use filtered indexes and other SQL Server features that require the ISO standard setting.

I recommend you ensure both QUOTED_IDENTIFIER and ANSI_NULLS session settings are ON when creating creating objects to avoid surprises later.

quoted identifiers on filtered indices

1 Answers1