Use a Conditional Constraint or Unique Filtered Index

John Couch 21 Reputation points
2022-12-06T15:16:51.64+00:00

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')  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,707 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2022-12-06T22:33:56.027+00:00

    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.)


1 additional answer

Sort by: Most helpful
  1. Anonymous
    2022-12-07T02:40:29.893+00:00

    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:
    267949-image.png
    The running time is generally around 20 milliseconds.

    Use a Unique Filtered Index:
    267938-image.png
    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.