I would like to change some of my stored procs to use memory optimized tables. Before I do this, could you help me to clarify some things?
Here I can read, the tempdb doesn’t support it for insert-operation (What's the difference between a temp table and table variable in SQL Server?). But what about with data retrieving, like select?
With memory optimized tables could it be possible, that I get an error, if the server is less of RAM? In this thread (https://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/) I can read about this.
Memory optimized tables do the boost, or not (What's the difference between a temp table and table variable in SQL Server?)? This thread (https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/) describes, that tempdb (starting with SQL 2014) can also reduce disk IO.
Should I use tempdb or memory optimized table variables?
I'm using Microsoft SQL 2014 (12.0.5000.0) Enterprise 64 bit. The SQL queries are a bit complex: e.g. there is a query, that uses 2 views and each of these views depending on other views.
The result of the whole query is not big (a couple thousands of rows). Some of the tables, from which the views are depending, have 40 - 50k rows. I alraedy realized, if rewrite a view into a table valued function, it increases the performance. Especially if the original view is embedded in another view, and used more time.
But what about the 3 points above? For me the links are somehow conflicting.