Index in In Memory OLTP Tables.

In Memory OLTP is a new revolution in OLTP for SQL Server users.
The feature supports extremely fast transaction but as every feature we have to also
Handling in memory table is quite different compare to normal tables and we have to be ready for it.

Lets see changes related to indexes here.
We will look into more details in coming articles.

1. Clustered indexes are not supported
   Only nonclustered indexes are supported

2 .Two types of nonclustered index
    1.Nonclustered hash index
    We will see in more detail about it in next article
    2.Nonclustered index
    Same as normal tables

3.Maximum 8 indexes are supported

4.Index can be created only when creating tables.
We can not alter index or drop index.

5.Columns which are using index keys must be not null.

6.If table is durable then primary key is must for the table.
    If table is not durable at least one index is required


7.Nonclustered Hash index is used for point lookup or index seek.
When  there is equality match in query it can be used and it gives very fast result.
But as hash values are not ordered its not useful for range scan.
We can use it for index scan but performance will degrade

8.For range scan Nonclustered index gives better performance.
Hash index are not useful here.


9.Indexes are uni directional.
It means index supports only order in which its defined during creating table.
If the query requires reverse sort order than index sort order index will not be used.

10.Index pages are not stored in database.
Index will be rebuild while reading data from server after restart or crash.

11.If we want to create index on any type of character column Bin2 collation must be used .
   

Lets see example here

CREATE TABLE test_3

  (

     id   INT,

     data INT NOT NULL Index IX,

     name VARCHAR(100) COLLATE latin1_general_100_bin2 NOT NULL Index IX1)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)