T-SQL Tuesday #146 Preconceived Notions

Thanks to Andy Yun for hosting this month of T-SQL Tuesday. Andy’s challenge for this month of T-SQL Tuesday is to share our learnings that have changed our preconceived opinions.

There are many learnings throughout my career as a DBA but in this post, I would like to share something interesting I have learned in the recent months from Pam Lahoud about tempdb.

I always had an opinion of changing the schema of the temp tables or adding indexes later after the temp table creation doesn’t affect in any way but it’s actually not a good idea. Yes, you heard that right. Altering or adding indexes later after temp table creation within the stored procedures will be marked for deletion and so the cached temp table metadata cannot be reused. It is suggestable to create the index as you create the temp table using the inline index syntax.

Please read the full article here written by Pam Lahoud to know in detail how this best practice can help reduce the tempdb metadata contention. Pam also mentioned other best practices in a detailed in-depth presentation on tempdb internals at EightKB Conference. Here is the link to it.

Also, do not forget to read this wonderful article by Erik Darling on indexing temp tables. Erik explained in detail when can you actually be creating the indexes on temp tables by using the inline indexing syntax, when it can be useful, and cases where it is not an option.

I am looking forward to reading and learning from other SQL community members about what their preconceived notions are.

Thanks for reading!