Is there any difference between UNIQUE NONCLUSTERED INDEX and NONCLUSTERED INDEX?

Question

I am using SQL Server 2008 R2 and this index in my database was already created:

CREATE UNIQUE NONCLUSTERED INDEX IndexT1 
ON T1 (
    [ID] ASC,
    [logID] ASC
)
INCLUDE ([MasterID]) 
WHERE ([logID] IS NOT NULL)

I executed this query :

SELECT 
    R.X 
FROM
    R
LEFT JOIN 
    T1 ON T1.MasterID = R.MasterID
WHERE 
    T1.ID IS NULL 
    AND R.logID IS NULL
    AND T1.ItemID IN (SELECT MAX(R.ItemID)
                      FROM R
                      LEFT JOIN T1 ON T1.MasterID = R.MasterID
                      WHERE T1.ID IS NULL 
                        AND R.logID IS NULL
                      GROUP BY R.MasterID)  

And in the execution plan, SQL Server offer to me to create this index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON T1 (
  [ID],
  [logID]
)

Does anybody know what is a difference between those indexes?

Because, I think the first ones cover the second ones! Why my query can not use the first ones?

Answer 1

Apart from the obvious difference that one enforces uniqueness, and the other one does not, there is another big difference.

Your first index:

CREATE UNIQUE NONCLUSTERED INDEX IndexT1 
ON T1 (
    [ID] ASC,
    [logID] ASC
)
INCLUDE ( [MasterID]) 
WHERE ([logID] IS NOT NULL) -- notice the WHERE clause here!

...is a filtered index (notice the where clause).

On the other hand, your 2nd index:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON T1 (
  [ID],
  [logID]
)

... is not.

What this means is that your filtered index does not include rows where logID IS NULL, and therefore, only enforces uniqueness of the ID/logID combination if logID IS NOT NULL.

The point here is that SQL Server is noticing that your query contains conditions on logID IS NULL that cannot benefit from the filtered index because the index doesn't include rows where logID IS NULL. So it naturally suggests a new index.