Whats the difference between an in-memory table, temp table and a pivot table?

Question

With regards to SQL and queries, whats the difference between an in-memory table, temp table and a pivot table?

Answer 1

• an in-memory table is a table that's been entirely cached, and so doesn't result in any physical (hard disk) reads when queried. Alternatively, it's a table-valued variable, declared in a batch or function, with no persistence. Depends what you mean by "in-memory table" :)

• a temp(orary) table is a table that will be automatically dropped when it's no longer needed, usually when the creating session is terminated. In MS SQL, they begin with a # (or two hashes if they're global temporary tables, shared between multiple sessions), and are often created with a SELECT INTO #TEMPTABLE ... style query.

• a pivot table is a special form of query where the values in several rows are summarised, "pivoted" on an axis, and become columns, where the summary data then becomes the rows. Frequently this happens where you've rows sorted on dates; these may then be "pivoted" so you end up with a column for January, one for February, one for March, etc.

Answer 2

Heres a good read on @temp tables vs #temp tables

I would summarize it as:

@temp table variables are stored in memory... the more you use them the higher processor cost there will be... but these can get cached and as such can run faster most of the time.

#temp tables are stored on disk, if you're storing alot of data in the temp table you may want to go this route, I.E. for reporting purposes.

Using PIVOT and UNPIVOT is just a way "pivoting" your results... so its memory access would be similar to how other standard queries are performed.