SQL Server allows repeated null fields and does not have null values (1/2)

Solution 1: for this problem, the first thought may be: Can I add a unique key to the caption field? Well, let's proceed with this idea and create a unique index first. & Nbsp; Code: createuniquenonclusteredindexun_test_tbontest_tb (caption) go index has been created. Let's test the effect & nbsp; Code: I solution 1:
For this question, the first thought may be: Can I add a unique key to the caption field? Well, let's proceed with this idea and create a unique index first.
The Code is as follows:
Create unique nonclustered index un_test_tb
On test_tb (caption)
Go

After the index is created, let's test the effect.
The Code is as follows:
Insert into test_tb (caption)
Values (null)
Go
Insert into test_tb (caption)
Values (null)
Go

After running, we will receive the following error message:
Reference content is as follows:
Message 2601, Level 14, status 1, 1st rows
Rows with duplicate keys cannot be inserted in the 'dbo. test_tb 'object with the unique index 'un _ test_tb.
The statement has been terminated.
Therefore, this solution does not work.
Solution 2:
Add constraints so that SQL server can first verify whether the value to be inserted exists in the existing data when inserting data. Because this constraint is not a simple operation, we first create a function and then call it in the constraint.
Create a verification logic function:
The Code is as follows:
Create function [dbo]. [fn_ck_test_tb_caption] ()
Returns bit
As
Begin
If (exists (
Select 1
From test_tb as
Where (caption is not null) and exists
(Select 1 as expr1
From test_tb
Where (caption is not null) and (caption = a. caption) and (a. testid <> testid ))
))
Return 0
Return 1
End
Go