#SQLSERVERLearning : Does Unique Index Stored NULL Values in Index?

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as difference between Oracle and SQL Server.

Hopefully it might be helpful for any Oracle guy also working with SQL Server.
All finding or Observations with SQL Server of mine can be find here.

In Oracle, Single Column index does not store NULL Values with in Index Structure.
Whenever we query based on column having null values with NULL condition’s (Specially for IS NULL, for IS NOT NULL it might fetch from index also.) it fetches through Table access, as index does not store NULL Values.
For more information on INDEX and NULL Values in Oracle, please check here.

Unique index in Oracle allows multiple NULL values getting stored with uniqueness getting imposed ONLY on Non Null values.
Ideally with DBMS core concepts, Null can’t be Comparable.
Unique index in oracle also does not enforce uniqueness on NULL data by comparing NULL with NULL, hence it allows multiple NULL by not storing NULL data within Index Structure for Unique Column.

lets walk through an example in Oracle, then will move to SQL Server part.

CREATE TABLE TAB_UNIQ_NULL(COL1 NUMBER , COL2 VARCHAR2(1));
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1);

INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A');
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B');
COMMIT;

Output:
1 rows inserted.
1 rows inserted.

As expected, though we had unique index created on Col1, it allows Multiple NULL values to get stored.

now try to fetch only col1 data based on IS NULL conditions, let check from where data would be fetch Index or Table level.

EXPLAIN PLAN FOR
SELECT COL1 FROM TAB_UNIQ_NULL WHERE COL1 IS NULL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 2 |
|* 1 | TABLE ACCESS FULL| TAB_UNIQ_NULL | 2 |
---------------------------------------------------

From Execution Plan we can say, in Oracle unique index does not store Null data and null data can only be fetched at table storage.
Ideally not pushing NULL in Index structure allow Oracle to push multiple NULL data for Unique index.

Now lets try same set of example for SQL Server and see how it behave for Unique and NULL data!.

CREATE TABLE TAB_UNIQ_NULL(COL1 INT , COL2 VARCHAR(1))
GO
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1)
GO

INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A')
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B')
GO

Msg 2601, Level 14, State 1, Line 15
Cannot insert duplicate key row in object 'dbo.tab_uniq_null' with unique index 'unq_col1_idx'. The duplicate key value is ("NULL").
The statement has been terminated.

First Insert statement got inserted successfully, above Exception was for Second Insert.

SQL Server actually compare NULL with NULL for Uniqueness.!
It only allows One NULL data due to Unique index on column.
Ideally for any Database design related to Unique index in SQL Server above behaviour need to be taken into considerations.

Now let see how exactly single null data is getting fetched that got successfully inserted within table.

SELECT * FROM TAB_UNIQ_NULL;


COL1 COL2
----------- ----
NULL A

Will try to fetch COL1 data based on NULL Condition and check underlying execution plan for query.
(Execution plan reading are the best way to learn internal processing within any Database)

SELECT COL1 FROM TAB_UNIQ_NULL WHERE COL1 IS NULL;

Execution plan for same,

blog1

Index SEEK Access path tell us our data is fetch from Index itself and not at table level through any key look up.(Yet to learn a lot on SQL Server Query tuning and plan reading :-|)
Within SQL Server for Unique index, First NULL was store at Index level or got push-in index structure.
hence any further null data are not allowed with Unique Column.

SQL Server provide Filter Index were we can filter data getting push into index.
For Uniqueness and allowing multiple NULL getting inserted we would need to restructure our DDL as below.

Let Restructure our table with Filter Unique Index.

CREATE TABLE TAB_UNIQ_NULL(COL1 INT , COL2 VARCHAR(1))
GO
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1) WHERE COL1 IS NOT NULL;
/* Added Where condition to restrict inclusion of NULL data within Index*/
GO


INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A')
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B')
GO

Now our insertion would be Successful without any exception.

let recheck execution plan for same statement,

blog2

Now for NULL data, SQL Server need to perform table access as NULL data are not getting pushed into Index.!!
Post filter Index, conceptually it works similar as Oracle.

Final thoughts:
Ideally it was confusing as NULL is not comparable and we cant enforce uniqueness by comparing NULL with NULL.
So SQL Server can have only one NULL on Single Column with Unique index, as compare to Oracle allowing multiple NULL with Unique Index.
With Filter index in SQL Server we can achieve same.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, SQL Server Learning and tagged , , , , , . Bookmark the permalink.

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 )

Google+ photo

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

Connecting to %s