filtered INDEX on Sql Server table causes errors during Insert-CodePudding

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.

CodePudding user response：

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;