All Index are Non-Clustered . Must be NOT NULL. NONCLUSTERED keyword is optional .
Alter table is not allowed. Index needs to be defined at the table creation time. There are two type of Index in the memory optimized table.
- single item lookup
- Static size determined at creation time.
- slow performance in the LIKE operation
- Bucket count : recommend value to 1.5 ~ 2X of the distant value.
- Range Index –> similar to the B-tree index
- Good for range queries ( <, >, IN)
- Size will grow.
- slow performance on single item look up
Statistic is not being auto created on the memory optimized table. We have to manually create it.
- Below test is base on the TPC-H database.I user HammerDB to create the TPCH database and test dataset with scalar factor = 1 . Refer here
- Setup the test tables:
For single value lookup, memory optimized table perform better than disk table.
In the storage type, it indicates, it is memoryOptimized
Statistic IO only has IO information for the disk base table.
However, hash index is not good for the range type of query. It is slower than the disk base table.
Disk base table: 22secs
Memory base table: 24 secs
Even we force the index on the memory optimized table. It is still take 22 secs and it is Index Scan not the fast Index Seek.
Another Index is range index.
Below test shows the range index is covering index therefore it does not need to do the key lookup like non cluster index in the row base table.
For range query, it perform better than row base table
Oder does matter more in the index in memory optimized table. If we want to retrieve the order that is different than index . SQL server needs to do the expensive index scan instead of index seek.
Because the index on the orderDate column is created base on ASC order. Therefore when we want to get the DESC order result, it needs to sort again.
If we run the same query on the row bases table, it does not matter .
Index on the character column
When create the index on the character column, we need to specify the collation otherwise, we will get below error:
Msg 12328, Level 16, State 102, Line 119
Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.
Msg 1750, Level 16, State 0, Line 119
Could not create constraint or index. See previous errors.
The column will become case sensitive and the order in the WHERE clause does become matter.
Hash Index: For composite key, the hash is created on the combination of the both value. Therefore, we for the single value lookup, it will result in the index scan .
For the range index, it does not matter.