Database Reference
In-Depth Information
As you can see, the list of limitations is pretty small. However, the flexibility of query interop access comes at a
cost. Natively-compiled stored procedures are usually several times more efficient as compared to their interpreted
T-SQL counterparts. In some cases, for example joins between memory-optimized and on-disk tables, query interop
is the only choice; however, it is usually preferable to use natively-compiled stored procedures when possible.
Memory-Optimized Table Types and Variables
SQL Server allows you to create memory-optimized table types. Table variables of those types are called
memory-
optimized table variables
. In contrast to regular disk-based table variables, memory-optimized table variables live in
memory only and do not utilize
tempdb
.
Memory-optimized table variables provide great performance. They can be used as a replacement for disk-based
table variables and, in some cases, temporary tables. Obviously, they have the same set of functional limitations as
memory-optimized tables.
Contrary to disk-based table types, you can define indexes on memory-optimized table types. The same
statistics-related limitations still apply, however, as we already discussed. Due to the nature of the indexes on
memory-optimized tables, cardinality estimation errors yield a much lower negative impact as compared to those of
on-disk tables.
SQL Server does not support inline declaration of memory-optimized table variables. For example, the code
shown in Listing 33-7 would not compile and it would raise an error. The reason behind this limitation is that
SQL Server compiles a DLL for every memory-optimized table type, which would not work in the case of inline
declaration.
Listing 33-7.
(Non-functional) inline declaration of memory-optimized table variables
declare
@IDList table
(
ID int not null
primary key nonclustered hash
with (bucket_count=10000)
)
with (memory_optimized=on)
Msg 319, Level 15, State 1, Line 91
Incorrect syntax near the keyword 'with'. If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause, the previous statement must be
terminated with a semicolon.
You should define and use a memory-optimized table type instead, as shown in Listing 33-8.
