Use a Conditional Constraint or Unique Filtered Index

Question

Which is a better approach? I know one is simpler to create, but I am looking to constrain the data by a value.

CREATE FUNCTION CheckStatus(  
  @ID INT  
) RETURNS INT AS BEGIN  
  
  DECLARE @ret INT;  
  SELECT @ret = COUNT(*) FROM [Test] WHERE [ID] = @ID AND [Status] = 'Active';  
  RETURN @ret;  
  
END;  
GO  
  
CREATE TABLE [Test] ([ID] INT   
                    ,[Name] VARCHAR(50)  
                    ,[Status] VARCHAR(10)  
                    ,[Date] DATE);  
GO  
  
ALTER TABLE Test  
  ADD CONSTRAINT CheckStatusConstraint CHECK (NOT (dbo.CheckStatus(ID) > 1 AND [Status] = 'Active'));  
  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Active', '1/1/2021');  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Inactive', '5/1/2021');  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Active', '7/21/2022');  

OR

CREATE TABLE [Test] ([ID] INT   
                    ,[Name] VARCHAR(50)  
                    ,[Status] VARCHAR(10)  
                    ,[Date] DATE);  
GO  
  
CREATE UNIQUE NONCLUSTERED INDEX [unidx__Test__ID__Status_eq_Active] ON [Test] (ID)   
WHERE (Status = 'Active')  

Answer 1

I much prefer the filtered index. Having a user-defined function in a constraint definition can adversely slow down mass-inserts, because the function will be called once for each row. (You may have heard of inlining of scalar UDF in SQL 2019, but this is a situation where inlining does not happen.)

Answer 2

Hi @John Couch

I prefer the unique filtered index. In the above code, you use a UNIQUE NONCLUSTERED INDEX, which has a separate structure from the data table, so it can be placed in different filegroups and use different I/O paths, which means that SQL Server can access the index and table in parallel, making lookups faster.
I did a simple test to compare the efficiency of the two methods.

declare @begin_date datetime;  
declare @end_date datetime;  
select @begin_date = getdate();  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Active', '1/1/2021');  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Inactive', '5/1/2021');  
INSERT INTO [Test] VALUES (1, 'Jim Jones', 'Active', '7/21/2022');  
select @end_date = getdate();  
select datediff(ms,@begin_date,@end_date) as 'Elapsed time/Milliseconds';  

Use a Conditional Constrain:

The running time is generally around 20 milliseconds.

Use a Unique Filtered Index:

The running time is generally around 10 milliseconds.

Therefore, when the amount of data inserted is large, it is advantageous to use a Unique Filtered Index.

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.