NOT NULL Constraints

NOT NULL  in  SQL Server allow you to specify that a column may not contain NULL values. When you create a new NOT NULL constraint on a database column, SQL Server checks the column’s current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.
The following index supports the query only if the column LAST_NAME has a NOT NULL constraint:

DROP INDEX emp_dob;
CREATE INDEX emp_dob_name
          ON employees (date_of_birth, last_name);

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL;

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DOB_NAME |    1 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DATE_OF_BIRTH" IS NULL)

Removing the NOT NULL constraint renders the index unusable for this query:

ALTER TABLE employees MODIFY last_name NULL;

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL;

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------