I have created a memory optimized table type in my database like so:
create type tblLocation_InMem as table(
IndexLocation int not null index IX_LocInMem,
IndexRootLocation int not null,
fldLocCrumbs nvarchar(1024) not null
)
with (memory_optimized=on)
This table type is to be used in a big query that needs to access the data stored in it over multiple select statements like so:
declare @myLocTbl as tblLocation_InMem;
insert into @myLocTbl <some select statement>;
select <stuff> from <some table> join @myLocTbl on <join stuff>;
select <other stuff> from <other table> join @myLocTbl on <more join stuff>;
Everything works fine, but I looked into my SQL data folder for the database's File_MemOpt file group and I see stuff like this for my In-Memory table type under the $HKv2 folder:
Apparently my in memory table type isn't quite in-memory. Is there a way I can use this table type and keep it strictly in memory without all this disk writing. I thought by creating just an in-memory table type I was avoiding any disk activity. I also tried creating an in-memory table definition and specifying durability=schema_only, but this still created stuff on the disk. Should I just use table variables?
I'm not worried about persistence over a restart or anything like that. The select request happens in the context of a REST web request. If the server restarts during the request, then the request will just have to start all over.
