In-Memory OLTP Internals - Pro SQL Server Internals - page 642

Listing 32-4. Composite hash index: Selecting data using both index columns as predicates
select CustomerId, FirstName, LastName
from dbo.CustomersOnDisk
where FirstName = 'Brian' and LastName = 'White';
select CustomerId, FirstName, LastName
from dbo.CustomersMemoryOptimized
where FirstName = 'Brian' and LastName = 'White';
As you can see in Figure 32-4 , SQL Server is able to use an Index Seek operation in both cases.
Figure 32-4. Composite hash index: Execution plans where queries use both index columns as predicates
In the next step, let's check what happens if you remove the filter by FirstName from the queries. The code is
shown in Listing 32-5.
Listing 32-5. Composite hash index: Selecting data using leftmost index column only
select CustomerId, FirstName, LastName
from dbo.CustomersOnDisk
where LastName = 'White';
select CustomerId, FirstName, LastName
from dbo.CustomersMemoryOptimized
where LastName = 'White';
In the case of the on-disk index, SQL Server is still able to utilize an Index Seek operation. This is not the case
for the composite hash index defined on the memory-optimized table. You can see the execution plans for the queries
in Figure 32-5 .