---
title: "With SQL Server I want a unique index on only the non-null values"  
description: "With SQL Server I want a unique index on only the non-null values"  
author: "Revati S Misra"  
published: 2023-04-27  
updated: 2023-07-02  
canonical: https://www.mindstick.com/forum/158048/with-sql-server-i-want-a-unique-index-on-only-the-non-null-values  
category: "mssql server"  
tags: ["database", "sql server", "sql"]  
reading_time: 2 minutes  

---

# With SQL Server I want a unique index on only the non-null values

With [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) I want a [unique index](https://www.mindstick.com/forum/159374/transaction-with-multiple-updates-and-unique-index) on only the non-[null values](https://www.mindstick.com/interview/34072/optional-class-in-java-handling-null-values-more-effectively)

## 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 only the non-[null](https://www.mindstick.com/forum/33922/how-to-use-null-coalescing-operator-in-c-sharp) [values](https://www.mindstick.com/forum/327/sum-textbox-values) in a column in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) using 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;
```

For example, to create a unique index on only the non-null values in the `customer_id` column in the `customers` table, you would use the following code:

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.

Here are some additional points to keep in mind when creating a filtered unique index:

- The `WHERE` clause must be a Boolean expression that evaluates to TRUE or FALSE.
- The `WHERE` clause cannot reference any other columns in the index.
- The `WHERE` clause cannot reference any columns that are not part of the index.
- The `WHERE` clause cannot reference any columns that are nullable.


---

Original Source: https://www.mindstick.com/forum/158048/with-sql-server-i-want-a-unique-index-on-only-the-non-null-values

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