filtered INDEX on Sql Server table causes errors during Insert

Question

I have a table in SQL Server 2019 which defined like this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[productionLog2](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [itemID] [binary](10) NOT NULL,
    [version] [int] NOT NULL,
 CONSTRAINT [PK_productionLog2] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

This table is going to log produced items and it is a checkpoint to avoid generation of items with duplicate (itemId,version) in case version >0. In other words we should have no rows with same itemId and version (this rule should only apply to rows with version greater than 0).

So I've added below constraint as a filtered INDEX:

SET ANSI_PADDING OFF GO

CREATE UNIQUE NONCLUSTERED INDEX [UQ_itemID_ver] ON [dbo].[productionLog2] 
(
    [itemID] ASC,
    [version] ASC
)
WHERE ([version]>=(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The problems is when I want to execute transactions which contain several commands, such as below one using C++ OLE APIs (for VC V7/Visual Studio 2000), the insertion fails after adding above index to the table, although the insert command itself will run individually inside SQL Server management studio with no errors.

C++ follows such a sequence:

--begin C++ transaction
--excute sub-command 1 in C++
SELECT ISNULL(MAX(version),-1)  
FROM [dbo].[productionLog2]         
WHERE [itemID]=0x01234567890123456789


--increase version by one inside C++ code
-- consider fox example max version is 9
-- will use 10 for next version insertion


--excute sub-command 2 in C++
INSERT INTO [dbo].[productionLog2]([itemID]          ,[version]          )          
VALUES          (0x01234567890123456789,10);


--end  C++ transaction

Above transaction will fails to run when it reaches to insert command, but below scripts runs without errors in for the first time (for next runs, it will fail due to constraint):

INSERT INTO [dbo].[productionLog2]([itemID]          ,[version]          )          
VALUES          (0x01234567890123456789,10);

Can you imagine is what is wrong with defined constraint? Or what causes that it will avoid running C++ commands but is working well inside SSMS?

P.S. Prior to this I had no requirment to add WHERE ([version]>=(0)) on my INDEX so I was using UNIQUE constraint but since I want to have filtered CONSTRAINT I changed constraint as an INDEX with filters and nothing went wrong before this change during my code execution.

Your question is not very clear but adding a filtered index can break inserts for connections that do not have the required SET options. The error message in that instance would be quite clear and tell you the problem. e.g. `INSERT/Update failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.` — Martin Smith, Dec 25 '21 at 12:44
The required session `SET` options for filtered indexes are in [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15#required-set-options-for-filtered-indexes). These are set properly with modern APIs but it seems you have old code. Add this to your T-SQL statement batch: `SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;SET NUMERIC_ROUNDABORT OFF;` — Dan Guzman, Dec 25 '21 at 13:07
@DanGuzman Yep, the problem was about `QUOTED_IDENTIFIER`. As I understood these are session parameters, but is it possible to change default SQL server session values for an specific database, somehow? Besides thanks for link to the documentation, it was very useful. — VSB, Dec 25 '21 at 13:35
Your application code is what is changing the QOUTED_IDENTIFIER setting. There is a default but it is generally ignored by applications since it is typically set by the connection interface. TBH your schema needs correcting anyways. You can't possibly need an INT for version. You probably SHOULD have a constraint so that is must be >= zero due to some rather obvious assumptions. So the filtered unique index makes little sense with this assumption. And for future reference, post the compete error when you encounter one - "fails to run" is not a useful thing to write. — SMor, Dec 25 '21 at 13:51
@SMor I'm developing in a quite old SDK which have it own proprietary DB APIs. The exception returned by database or related error codes are not available since the API manages database calls and I only could track the problem by rolling back many things. So unfortunately I could not provide more details. Besides, are such errors are traceable using SQL Server Profiler? — VSB, Dec 26 '21 at 11:01

Dan Guzman · Accepted Answer · 2021-12-26T12:10:18.030

The required session SET options for filtered indexes are listed in the CREATE INEX documentation:

+-------------------------+----------------+----------------------+-------------------------------+--------------------------+
|       SET options       | Required value | Default server value | Default OLE DB and ODBC value | Default DB-Library value |
+-------------------------+----------------+----------------------+-------------------------------+--------------------------+
| DB-Library value        |                |                      |                               |                          |
| ANSI_NULLS              | ON             | ON                   | ON                            | OFF                      |
| ANSI_PADDING            | ON             | ON                   | ON                            | OFF                      |
| ANSI_WARNINGS*          | ON             | ON                   | ON                            | OFF                      |
| ARITHABORT              | ON             | ON                   | OFF                           | OFF                      |
| CONCAT_NULL_YIELDS_NULL | ON             | ON                   | ON                            | OFF                      |
| NUMERIC_ROUNDABORT      | OFF            | OFF                  | OFF                           | OFF                      |
| QUOTED_IDENTIFIER       | ON             | ON                   | ON                            | OFF                      |
+-------------------------+----------------+----------------------+-------------------------------+--------------------------+

These are set properly by modern SQL Server APIs but it seems you have old code and/or driver.

Add these SET statements to T-SQL batches that modify tables with filtered indexes:

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

In the case of an outdated driver that doesn't set session options at all, the default database SET options will be used. These are mostly set to OFF for backwards compatibility. The script below will set the database defaults needed for filtered indexes but, again, an explicit setting by the driver or session will override these.

ALTER DATABASE YourDatabase SET ANSI_NULLS ON;
ALTER DATABASE YourDatabase SET ANSI_PADDING ON;
ALTER DATABASE YourDatabase SET ANSI_WARNINGS ON;
ALTER DATABASE YourDatabase SET ARITHABORT ON;
ALTER DATABASE YourDatabase SET CONCAT_NULL_YIELDS_NULL ON;
ALTER DATABASE YourDatabase SET QUOTED_IDENTIFIER ON;
ALTER DATABASE YourDatabase SET NUMERIC_ROUNDABORT OFF;

filtered INDEX on Sql Server table causes errors during Insert

1 Answers1