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

database, mysql, sql, sql-server

Solution

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.

Problem

Original source