Basic Logics · Did you Know! · Performance tune · Query Performance · Real Scenario Logic's

Unique Filtered Index in SQL Server

Introduction

In this blog post, we will see what is Unique Filtered Index and how to create it and what are the challenges while creating it and what are the advantages of using it in SQL Server.

Unique Filtered IndexWhat it is?

As we all know, the UNIQUE Index will allow only Unique values. Only one NULL value is acceptable. What if we need to allow multiple NULL values and accept Unique Non-Nullable Values? Here comes the purpose of Unique Filtered Index. The Unique Filtered Index filters the whole result set/data based on the user given filter conditions and applies the uniqueness only on the remaining data aka filtered result set.

How to Create it?

SQL ScriptSample 1

IF OBJECT_ID('Test_Products') IS NOT NULL
	DROP TABLE Test_Products;
GO
	
CREATE TABLE Test_Products
(
TestProduct_Key INT IDENTITY(1,1) NOT NULL,
ItemName VARCHAR(200) NOT NULL,
ItemCode VARCHAR(20) NULL
);
GO

SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;

CREATE UNIQUE NONCLUSTERED INDEX U_NCI_Test_Products_ItemCode
ON Test_Products(ItemCode)
WHERE ItemCode IS NOT NULL;
GO

INSERT INTO Test_Products (ItemName,ItemCode)
SELECT 'Ball','BA' UNION ALL
SELECT 'BandWidth','BW' UNION ALL  
SELECT 'Balloon',NULL UNION ALL  
SELECT 'BattleGround',NULL ;
GO

SELECT TestProduct_Key,ItemName,ItemCode FROM Test_Products;
GO

Sample 1 – Result set and Table Structure

Sample 1

SQL ScriptSample 2

IF OBJECT_ID('Test_Products') IS NOT NULL
	DROP TABLE Test_Products;
GO
	
CREATE TABLE Test_Products
(
TestProduct_Key INT IDENTITY(1,1) NOT NULL,
ItemName VARCHAR(200) NOT NULL,
ItemCode VARCHAR(20) NULL
);
GO

SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;

CREATE UNIQUE NONCLUSTERED INDEX U_NCI_Test_Products_ItemCode
ON Test_Products(ItemCode)
WHERE ItemCode<>'BW';
GO

INSERT INTO Test_Products (ItemName,ItemCode)
SELECT 'Ball','BA' UNION ALL
SELECT 'BandWidth','BW' UNION ALL  
SELECT 'BandWidth','BW' UNION ALL  
SELECT 'Balloon','BL' UNION ALL  
SELECT 'BattleGround','BT' ;
GO

SELECT TestProduct_Key,ItemName,ItemCode FROM Test_Products;
GO

Sample 2 – Result set and Table Structure

sample2

What are the challenges while creating (Unique) Filtered Index?

Can

  • Applies for all NOT NULL/NULL values.
  • Use IN for a range of values.
  • Use operators like =, <>, < etc., in the WHERE clause.
  • Create multiple filtered indexes on one column.

Cannot

  • Not Applies for BETWEEN, NOT IN, or CASE statement.
  • The query optimizer won’t consider filtered indexes if we are using local variables or parameterized SQL for predicate that matches the filter.

For more info-

Benefits

  • Improved query performance and plan quality
  • Reduced index maintenance costs
  • Reduced index storage costs

Summary

Thus through this blog post, we came to know Unique Filtered Index and how to create it with 2 simple sample scripts and the challenges while creating it and advantages/benefits of using it in SQL Server. This works well in both on-premises as well as Azure SQL DB.

Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “Unique Filtered Index in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s