SQL Server: Temporary Objects

Learn how to use temporary tables and table variables correctly, for developers and DBAs from SQL Server 2005 onward
Course info
Rating
(248)
Level
Intermediate
Updated
Oct 29, 2013
Duration
1h 9m
Table of contents
Course Introduction
Temporary Tables
Table Variables
Tempdb and Temporary Objects Planning
Description
Course info
Rating
(248)
Level
Intermediate
Updated
Oct 29, 2013
Duration
1h 9m
Description

SQL Server offers different types of temporary objects that you can use for storing, querying and transferring data, however which temporary object type you choose can have a significant influence on the performance and scalability of your workloads. In this course we’ll walk through what the different SQL Server temporary objects are, syntax fundamentals, how to use them and most importantly, how to leverage them effectively. This course is perfect for developers, DBAs, and anyone responsible for writing Transact-SQL code, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onward.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Course Introduction
Hi this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server temporary objects course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. SQL Server provides the ability to create and leverage temporary objects for use within the scope of your query session or batch. There are many reasons why you might decide to use temporary objects, and we'll talk about that throughout the course. And it's also important to know that under some circumstances, temporary objects can help with overall performance and scalability of specific query workloads. This course has a heavy emphasis on demonstrations. And across the several demos I'll cover the main temporary object choices and SQL Server. And along the way provide guidelines around there proper usage.

Temporary Tables
Hi this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server temporary objects course and you're watching module 2, where we'll do an in-depth overview of temporary tables. Temporary tables are available in SQL Server in order to store non-persistent result sets that can be used for a variety of purposes. In this module we'll be going through several demonstrations that cover temporary table creation, temporary table management, and an overview of temporary table functionality, including strengths and limitations.

Table Variables
HI this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Temporary Objects course and you're watching module 3 where we'll do an in depth overview of table variables. As with temporary tables, table variables can be used to store non-persistent result-sets that can be used for a number of different purposes. In this module, I'll demonstrate the declaration of table variables, how to reference table variables, and several demos that show their capabilities, strengths, and limitations.

Tempdb and Temporary Objects Planning
Hi this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Temporary Objects course and you're watching module 4, where we'll cover Tempbd and Temporary Object Planning and Implementation Considerations. For high through put applications that make heavy use of temporary objects, you need to consider your strategy for supporting, configuring, and troubleshooting tempdb usage. You also need to be cognizant of which temporary object types to use under which circumstances, as this decision can definitely introduce performance and scalability issues down road, if you're not careful. So in this module, we'll be covering tempdb contention issue identification and troubleshooting, and also review the various advantages, disadvantages, and appropriate use cases for temporary objects, in general.