question

Rob Farley avatar image
Rob Farley asked

Can a lookup use a NCIX?

Suppose I have a wide table, but generally I only use a few of the columns. I can make a NCIX on the CIX keys, and the system might use this NCIX for many queries instead of the CIX.

SELECT ProductID, Name, ListPrice
FROM Production.Product
ORDER BY ProductID;

CREATE UNIQUE INDEX ncix_PK ON Production.Product(ProductID) 
  INCLUDE (Name, ListPrice);

Will this index be used for Key Lookups as well? Surely if it's a smaller index, then it will be preferred, right? (And why / why not?)

PS: If you're reading this, then I encourage you to come with a good seeder question and post it too! Without good questions, the site will never catch on as a useful resource.

EDITED:

I don't think I've explained myself well enough.

Consider the query:

SELECT ListPrice
FROM Production.Product
WHERE Name = 'Chain';

On a standard installation of AdventureWorks, this query will use an index on Name, and then do a lookup to get the ListPrice (please don't change the index that's on Name, I know you could remove the need for a lookup by including the ListPrice column).

The lookup uses the Clustered Index. But I'm asking to consider the scenario where we have the index ncix_PK as defined above. Will the system use ncix_PK for the lookup? After all, it's unique, has the same keys as the CIX, but is smaller. What happens if you remove the CIX? What happens if you change the Fill Factor? Can you make the plan use a NCIX for a lookup?

indexingexecution-planoptimizationlookups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Remus Rusanu avatar image
Remus Rusanu answered

The SELECT query in your example will most likely use the NCIX, since is a full scan covered by the NCIX and the narrower b-tree is significantly less expensive to scan.

For a look up the cost is more complicated problem (I'm understanding a 'look up' as the key look up operator, ie. the seek of an exact key). The cost of the look up is dependent on the depth of the b-tree, not it's length. Both a CDX on ProductID and the NCIX use the same key so they can fit the same number of entries on the non-leaf pages, but the NCIX being narrower, hence fewer leaf pages, needs fewer entries on the last non-leaf level (there is one entry per leaf page), which means fewer entries on the next level so overall the NCIX can have fewer levels than CIX. But remember that we're talking logarithmic scale, so it takes a LOT of records to add one more level to the index depth. And at the end the difference will be a cost of 3 pages vs. a cost of 5 pages, not exactly overwhelming. And the look up on NCIX would only be useful if the intent is to read Name or ListPrice.

On your comment you mention another query: SELECT ListPrice FROM Production.Product WHERE NAME = 'Chain' and ask if the NCIX can be used to answer this. If there is no index on Name, then the NCIX (ProductId) INCLUDE (Name, PriceList) can be used instead of the CIX, because a scan on the NCIX involves fewer pages than one on the CIX, but you'll need enough pages in the table for the optimizer to fall for it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

The index can only be used for seek by selects with a filter on ProductID (where or join clause), and in your select there is no need for a key lookup due to :

  • the included columns. Every column in your statement exists in the index.
  • you do not have any where clause, it will do an index scan or a clustered index scan, and I suppose you have some more columns in your table, so I would say that it will be an index scan.

Do you have a clustered index on Name and ListPrice? If you have, there shouldn't be necessary to include them in the ncix. These columns are already included in the leaf level of the index, but please correct me if I am wrong, you should get an index scan even if you drop the included columns from your index.

Quote from Microsoft SQL Server 2008 Books online:

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

Consider the following. I make a wide table, and populate it with data. I make a NCIX to help find the data that I want, but as I don't include col02 in the NCIX, lookups are involved.

create table dbo.widetable (id int identity(1,1) primary key
  ,col01 char(800) not null default ''
  ,col02 char(800) not null default ''
  ,col03 char(800) not null default ''
  ,col04 char(800) not null default ''
  ,col05 char(800) not null default ''
  ,col06 char(800) not null default ''
  ,col07 char(800) not null default ''
  ,col08 char(800) not null default ''
  ,col09 char(800) not null default ''
  ,col10 char(800) not null default ''
  );
go
insert dbo.widetable (col01)
select cast(num as char(800))
from dbo.nums
where num <= 1000
go
create nonclustered index ix_col01 on dbo.widetable (col01)
go
select col02
from dbo.widetable
where col01 = '3';
go

Now I create a unique NCIX on the CIX key.

create unique index ix_ncix_pk on dbo.widetable (id) include (col02);
go

But the system still does lookups on the CIX.

Interestingly, if I look at index_depth in sys.dm_db_index_physical_stats, I can see that the NCIX is less deep than the CIX (2 v 3, because I carefully chose 1000 rows in the table). So a Lookup that used the NCIX would cost less. But still it uses the CIX, because Lookups have to use the Clustered Index.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.