UNIQUE Key Constraint with Multiple NULL values in SQL Server

This was a really interesting situation when we development team members were discussing over this topic. It was a situation where it was not possible to allow multiple NULL values into a column with UNIQUE Key defined in a table. Here I will give the problem first then we will go with the alternate solution for this.

Problem:

In SQL Server it is not allowed to insert multiple NULL values into a UNIQUE Key column. Below is the situation:

--Create employee table
CREATE TABLE EMPLOYEE
(EMPLOYEEID INT NOT NULL
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED
,FNAME VARCHAR(100)
,LNAME VARCHAR(100)
,UNIQUEID INT UNIQUE)
GO
--Insert data values (used row constructor)
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME, UNIQUEID)VALUES
(1,'RAHUL','MISHRA','112342115'),
(2,'SHEEL','KURANI','1725421455'),
(3,'SHEETAL','BAJAJ','1423721455'),
(4,'KAUSHIK','NARANG','1123721955'),
(5,'ADRIAN','THOULISS','1452342145')
GO
--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(6,'SAUGAT','KIOHLI')
--See below the result 
SELECT EMPLOYEEID, FNAME, LNAME, UNIQUEID FROM EMPLOYEE
GO

When I try to enter another record with NULL value to UNIQUEID column, it does not allow me to do that and throws an error.

--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU')

--ERROR MESSAGE
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__04FF5B33023D5A04'.
Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
The statement has been terminated.

Solution:

Here is the alternate solution for the above situation. Instead of define an UNIQUE Key to UNIQUEID column I will create another COMPUTED Column with UNIQUE Key. See how it is done:

--ALTERNATE SOLUTION
--Drop the UNIQUE key
ALTER TABLE EMPLOYEE
DROP CONSTRAINT UQ__EMPLOYEE__04FF5B33023D5A04

--Add a computed column with UNIQUE key
ALTER TABLE EMPLOYEE
ADD ALTUNIQUEID AS CASE WHEN UNIQUEID IS NULL THEN EMPLOYEEID ELSE UNIQUEID END
GO
ALTER TABLE EMPLOYEE
ADD CONSTRAINT UKey_QNIQUEID UNIQUE (ALTUNIQUEID)
GO

--Now insert multiple records with NULL to UNIQUEID column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU'),(8,'TARUN','KISHORE')
GO
--See below the result
SELECT * FROM EMPLOYEE
GO

Your valuable comments are most appreciated.