How does SQL Server ensure that non-null values are unique in empty fields?

The following code creates a table structure:

Copy codeThe Code is as follows: create table test_tb
(
TestId int not null identity (1, 1) primary key,
Caption nvarchar (100) null
);
GO

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.Copy codeThe 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.Copy codeThe 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:Copy codeThe 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

Reference a function in a constraint:Copy codeThe Code is as follows: alter table test_tb
Add constraint CK_test_tb_Caption CHECK (dbo. fn_CK_test_tb_Caption () = 1)
GO

Now let's test the effect. Test the NULL value first.Copy codeThe Code is as follows: insert into test_tb (Caption)
VALUES (null)
GO
Insert into test_tb (Caption)
VALUES (null)
GO
SELECT * FROM test_tb
GO

It can be run successfully, and multiple NULL behaviors occur. Now let's test the insertion condition that is not empty.Copy codeThe Code is as follows: insert into test_tb (Caption)
VALUES (N 'aaa ')
GO
Insert into test_tb (Caption)
VALUES (N 'bbb ')
GO
Insert into test_tb (Caption)
VALUES (N 'bbb ')
GO
SELECT * FROM test_tb
GO

The result is an error in the third statement. The Caption fields in the table also have 'aaa' and 'bbb', which is exactly the result we want.
So solution 2 is correct. But is it too cumbersome to write such a long paragraph for such a small function? Let's look at the solution below.
Solution 3: (only applicable to SQL Server 2008)
SQL Server 2008 has an elegant solution, that is, filtering indexes. A filtered index is an optimized non-clustered index. It is especially suitable for queries that cover the selection of data from a well-defined data subset. Filter indexes use filter predicates to index some rows in the table. With the screening index, we only need to write a statement to achieve the above effect.Copy codeThe Code is as follows: create unique nonclustered index un_test_tb
ON test_tb (Caption)
WHERE Caption is not null
GO

If we use some of the above test statements for testing, we will find that they fully meet our requirements. The only drawback of this solution is that this statement is only supported by SQL Server 2008. I don't know if you have any elegant and suitable solutions for SQL Server of various versions.