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.)
Use a Conditional Constraint or Unique Filtered Index
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
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
2022-12-06T22:33:56.027+00:00
1 additional answer
Sort by: Most helpful
-
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:
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.