How To Create Unique Index In Ms Sql That Will Allow Null

Hello everybody,

today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

How to achieve it? Following T-SQL can do this:

CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;

in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition.

If you're looking to optimize your Acumatica development with smart database indexing strategies like this, we can help! Whether you need a custom solution or a performance boost for your system, our team is ready to assist.

Leave a request for customization today, and let's build something great together!