Even after reading [The Data Loading Performance Guide] , I am still unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging.
Obviously the temp table is created in TempDB which operates in the SIMPLE recovery mode so I would have thought that it was a perfect candidate for minimal logging, however I can not find a passage to confirm it.
Is a temporary table a candidate for minimal logging, and if so is it worth adding the TABLOCK hint as recommended for permanent tables?
> *I am unsure if its necessary to add the `TABLOCK` table hint to an empty temporary table, defined with a clustered index in order to get minimal logging.*
No. Local temporary tables (`#temp`) are *private* to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (`##temp`) or a regular table (`dbo.temp`) created in `tempdb`, because these can be accessed from multiple sessions.
Note that even where the full minimal logging optimizations are not applied, tables created in `tempdb` benefit from other optimizations like not needing to log `REDO` information. You can test to see whether rows or pages are being logged using the undocumented `sys.fn_dblog`. Regular logging will feature row-logged records like `LOP_INSERT_ROWS`.
Note that adding `TABLOCK` to a local temporary table is required to get parallel execution with `INSERT...SELECT` queries in SQL Server 2016, see the Microsoft Knowledge Base article:
[Poor performance when you run INSERT.. SELECT operations in SQL Server 2016]