Unique Index and Ignore Nulls

Access allows you to create a Unique Index that ignores NULL values; we have added an example to the Employees EmploymentCode column in the sample database to demonstrate this problem.

In SQL Server, NULL is a unique value, and you cannot create a unique index to ignore NULL values. You can add a non-unique index and then write the following trigger code to prevent duplicates but allow multiple NULL values. After you have converted the sample database to SQL Server, you can take a look at how the code (UniqueIndexIgnoreNull.sql) that follows can be used to resolve this problem in the converted SQL Server database:

CREATE INDEX idxEmployee ON Employees(EmploymentCode)
GO
CREATE TRIGGER TR_Employees_EmploymentCode
ON Employees
FOR INSERT,UPDATE
AS
IF EXISTS( SELECT COUNT(*),EmploymentCode FROM Employees
WHERE EmploymentCode is NOT NULL
	GROUP BY EmploymentCode
	HAVING COUNT(*) > 1)
	ROLLBACK TRAN

GO

-- Test this
UPDATE Employees
SET EmploymentCode = 'N1123'
WHERE EmploymentCode ='N1156'

It's also worthwhile to consider using the following code to identify potential issues with these indexes prior to converting your database:

Sub modUpsizeTests_UniqueIndexIgnoreNulls()
    ' Example code to search for any unqiue indexes
    ' which ignore null values
    Dim db As DAO.Database
    Dim tdef As DAO.TableDef
    Dim idx As DAO.Index
    Set db = CurrentDb
    For Each tdef In db.TableDefs
	If Left(tdef.Name, 4) <> "MSys" And _
	    Left(tdef.Name, 4) <> "USys" And _
	    Left(tdef.Name, 4) <> "~TMP" Then
	    For Each idx In tdef.Indexes
		If idx.IgnoreNulls = True Then
		    Debug.Print tdef.Name, idx.Name
		End If
	    Next
	End If
    Next
End Sub