Database Reference
In-Depth Information
Listing 33-8.
Creating a memory-optimized table type and memory-optimized table variable
create type dbo.mtvIDList as table
(
ID int not null
primary key nonclustered hash
with (bucket_count=10000)
)
with (memory_optimized=on)
go
declare
@IDList dbo.mtvIDList
You can pass memory-optimized table variables as table-valued parameters (TVP) to natively compiled and
regular T-SQL procedures. As with on-disk based table-valued parameters, it is a very efficient way to pass a batch of
rows to a T-SQL routine.
You can use memory-optimized table variables to imitate row-by-row processing using cursors, which are not
supported in natively compiled stored procedures. Listing 33-9 illustrates an example of using a memory-optimized
table variable to imitate a static cursor. Obviously, it is better to avoid cursors and use set-based logic if at all possible.
Listing 33-9.
Using a memory-optimized table variable to imitate a cursor
create type dbo.MODataStage as table
(
ID int not null
primary key nonclustered
hash with (bucket_count=1000),
Value int null
)
with (memory_optimized=on)
go
create proc dbo.CursorDemo
with native_compilation, schemabinding, execute as owner
as
begin atomic
with (transaction isolation level = snapshot, language=N'us_english')
declare
@tblCursor dbo.MODataStage
,@ID int = -1
,@Value int
,@RC int = 1
/* Staging data in temporary table to imitate STATIC cursor */
insert into @tblCursor(ID, Value)
select ID, Value
from dbo.MOData
