Now that we have a memory-optimized table, the next logical step is to start querying the table and manipulating the data stored inside it.

We have two methods of interacting with these memory-optimized objects. Firstly, we can issue standard T-SQL queries and allow the SQL Server Query Optimizer to deal with accessing this new type of table. The second method is to use natively compiled stored procedures:

In the preceding figure, we can see a simplified diagram of a query that is either querying normal tables or memory-optimized tables. In the center of the diagram is a node titled Query Interop. This is a mechanism that is responsible for enabling normal interpreted T-SQL statements to access memory optimized tables. Please note that this is a one-way mechanism and that the natively compiled stored procedures are not able to access traditional objects, only memory-optimized objects.

Back to querying memory-optimized tables! Let's take a look at the following code:

SELECT * 
FROM dbo.InMemoryTable; 

This SELECT statement is standard T-SQL and has no special syntax to query the memory-optimized table we created earlier. At this point, we are unaware that this table is a memory-optimized table and that is a good thing. This is further proof of Microsoft's commitment to integrating this high-performance feature as a seamless extension of the storage engine. Essentially, it should not be of major interest whether we are using In-Memory OLTP or the traditional storage engine for our table—it should just be fast. We could even extrapolate that, in future, it may be possible that the entire SQL Server storage engine becomes purely memory-optimized.

If we can select data without any syntax changes, then we can expect that inserting data is equally simple, as you can see from the following code:

INSERT INTO dbo.InMemoryTable 
        ( UserId , 
          UserName , 
          LoginTime , 
          LoginCount 
        ) 
VALUES  ( 1 , 
          'John Smith' , 
          SYSDATETIME() , 
          1 
        ) 
; 
SELECT * 
FROM dbo.InMemoryTable 
; 

Of course, this insert works flawlessly and the final select returns a single row:

UserId      UserName        LoginTime           LoginCount
----------- ------------- ------------------- ---------------------
1           John Smith     2016-12-09           20:27:04.3424133 1
  

The important thing to realize here is that we are using normal T-SQL. This may sound rather uninteresting; however, this means that if we want to start migrating some of our tables to be memory-optimized, then we are free to do so without needing to refactor our code (provided we adhere to the limitations of the engine). This makes the possibilities of using this feature much more attractive, especially in legacy projects where logical design limitations are leading to locking and latching issues.

Due to the way that this interoperability works and the lock-free nature of memory-optimized tables, there are some further limitations with the T-SQL language and querying itself that we need to consider. Luckily, the large majority of T-SQL language features are available to us, so the list of unsupported commands is mercifully short:

• The MERGE statement when a memory-optimized table is the target of the MERGE
• Dynamic cursors (to adhere to memory-optimized rules; these are automatically changed to static cursors)
• TRUNCATE TABLE
• Cross database transactions
• Cross database queries
• Linked servers
• Locking hints—XLOCK, TABLOCK, PAGLOCK, and others
• READUNCOMMITTED, READCOMMITTED, and READCOMMITTEDLOCK isolation level hints

If we consider how each of these unsupported features work, it becomes clear that these limitations exist. Many, if not all, of these limitations are linked to how locking and latching is controlled in one way or another with those features. The only way to continue our high-performance adventure is to accept these limitations and adjust our system design accordingly.

There is a similar list of limitations for natively compiled stored procedures. This list is unfortunately quite a bit longer, and if we look back at figure Overview of SQL Server engine illustrating Query Interop between In-Memory OLTP and normal OLTP with the diagram of the SQL Server engine, we see that natively compiled stored procedures are only able to access memory-optimized tables and not the traditional storage engine objects. This main limitation causes the list of unsupported features to be quite a lot longer. You can find an exhaustive list of which features are supported and which aren't by visiting Microsoft's Books Online site and reading the list. You will be able to also change the documentation website to a different version of SQL Server to see that newer versions support an increasing number of features inside the In-Memory OLTP engine.