---
title: "How to create a unique index on a NULL column?"  
description: "How to create a unique index on a NULL column?"  
author: "Revati S Misra"  
published: 2023-04-27  
updated: 2023-07-02  
canonical: https://www.mindstick.com/forum/158049/how-to-create-a-unique-index-on-a-null-column  
category: "mssql server"  
tags: ["database", "sql server", "sql"]  
reading_time: 2 minutes  

---

# How to create a unique index on a NULL column?

How to create a [unique index](https://www.mindstick.com/forum/159374/transaction-with-multiple-updates-and-unique-index) on a [NULL](https://www.mindstick.com/forum/33922/how-to-use-null-coalescing-operator-in-c-sharp) [column](https://www.mindstick.com/forum/33860/how-to-calculate-column-summary-in-sql-server)?

## Replies

### Reply by Aryan Kumar

Sure. You can create a [unique](https://www.mindstick.com/blog/12870/how-to-be-unique-in-business) [index](https://www.mindstick.com/blog/198/index-in-sql-server) on a null column in SQL, but it will not prevent you from inserting duplicate null values into the column. This is because NULL values are considered unique in SQL.

To create a unique index on a null column, you use the `CREATE UNIQUE INDEX` statement. The syntax for the `CREATE UNIQUE INDEX` statement is as follows:

SQL

```plaintext
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
```

The `index_name` is the name of the index. The `table_name` is the name of the table that the index is associated with. The `column_name` is the name of the column that the index is created on.

Here is an example of how to create a unique index on a null column:

SQL

```plaintext
CREATE UNIQUE INDEX unique_null_column
ON customers (customer_id);
```

This will create a unique index on the `customer_id` column in the `customers` table. However, it will not prevent you from inserting duplicate null values into the `customer_id` column.

If you want to prevent duplicate null values in a column, you can use a **filtered unique index**. A filtered unique index is a unique index that only applies to rows where the specified column is not NULL.

To create a filtered unique index, you use the `CREATE UNIQUE INDEX` statement with the `WHERE` clause. The syntax for the `CREATE UNIQUE INDEX` statement with the `WHERE` clause is as follows:

SQL

```plaintext
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
WHERE column_name IS NOT NULL;
```

Here is an example of how to create a filtered unique index on a null column:

SQL

```plaintext
CREATE UNIQUE INDEX unique_null_column
ON customers (customer_id)
WHERE customer_id IS NOT NULL;
```

This will create a unique index on the `customer_id` column in the `customers` table. However, it will only apply to rows where the `customer_id` column is not NULL. This will prevent you from inserting duplicate null values into the `customer_id` column.


---

Original Source: https://www.mindstick.com/forum/158049/how-to-create-a-unique-index-on-a-null-column

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.