Hands-On-Lab: SQL Server Temporary Tables and Table Variables

Home/HOLs/Hands-On-Lab: SQL Server Temporary Tables and Table Variables

Hands-On-Lab: SQL Server Temporary Tables and Table Variables

Hello Friends,

Here is another popular lab from Hands-On-Lab library: SQL Server Temporary Tables and Table Variables.

As a TSQL Developer or DBA, while writing TSQL code for the business login or DBA maintenance tasks, we come across the situation to work on temporary data. To accomplish this, we generally use temporary table or table variable. The most confusing problem is “which one is the best for my algorithm?” This is a question which creates lots of confusion between TSQL professionals. The reason for this confusion is the lack of knowledge about the differences between temporary table and table variable. This lab is divided into nine exercise. In the first exercise, we will learn scope of temporaary objects. In the second exercise, we will learn Schema modifications of temporary objects. In the third exercise, we learn the behavior of temporary objects with SELECT INTO statement. In the fourth exercise, we will learn usage of IDENTITY INSERT with temporary objects. In the fifth exercise, we will learn about cardinality estimation on temporary objects. In the sixth exercise, we will learn about temporary tables within explicit transactions. In the seventh exercise, we will learn about temporary objects within user-defined functions and with user-defined types. In the eighth exercise, we will learn, indexing and collation in temporary objects. In ninth exercise, we will learn the behavior of temporary objects with in dynamic SQL and with TRUNCATE command. After completion of this lab, we will be able to choose the best one to use in our TSQL code.

Exercise 1: Scope of Temporary Table and Table Variable
Exercise 2: Schema Modification for Temporary Tables and Table Variables
Exercise 3: Use of Temporary Objects with SELECT INTO Statement
Exercise 4: Use of IDENTITY INSERT with Temporary Objects
Exercise 5: Cardinality Estimation on Temporary Objects
Exercise 6: Temporary Objects within Explicit Transaction
Exercise 7: Temporary Objects in user-defined functions and user-defined types
Exercise 8: Indexing and Collation in Temporary objects
Exercise 9: Dynamic SQL and TRUNCATE with temporary objects

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.

Learn More
 

 

Happy Learning – The Practical Way.

Data Platform Virtual Summit 2020

Subscribe to SQLMaestros YouTube channel. If you want more learning content in your inbox, subscribe to SQLMaestros Bulletin.

SQLMaestros YouTube | SQLMaestros Bulletin | SQLMaestros Twitter

By |2020-09-01T12:42:15+05:30July 27th, 2020|HOLs|0 Comments

About the Author:

Leave A Comment