Technology – Microsoft SQL Server Temp Table Types

There are a few basics to get to know when it comes to Microsoft SQL server temp table types. For the SQL server, temp table types global-local are two main types that are employed. DB developers are known for using temporary tables, but at the same time, they also may not be keen to go outside of their comfort zone or look at every single thing that they can do.

Temporary tables can actually accomplish quite a lot. Temporary tables can improve not only the performance of code but its ability to be maintained as well. At the same time, when things start to go left, it can be a massive pain for the DBA and developer, making everything go way slower than would be preferable.

So what do temporary tables do? A lot of the clue is in the name. They are most frequently used to provide users with the workspace they require for intermediate results when they are busy processing data inside a procedure or a batch. Temporary tables can help pass data from between stored procedures to a table-valued function or in Table-valued parameters, sending read-only tables to SQL server routines from applications and passing read-only temporary tables in turn for parameters. At the end of their use, automatic discarding is a process, so the user does not have to do anything.

Temporary tables include a variety, but the main ones you need to know are local temporary tables and global temporary tables (with a special tip of the hat to persistent temporary tables and table variables). Both global and local temporary tables start with their own symbol, which we will get into down further on.

Temporary tables are the superior pick to table variables when conducting complex processing for temporary data or using more data in them that isn’t as small. Users can utilize global or local temporary tables in SQL Server. Still, the server won’t store their definition permanently when it comes to database catalog views, which can cause problems with visibility and scope. While global tables can be seen by all sessions, the local tables can be seen in the current session alone.

Microsoft SQL Server Temp Table Types

SQL Server HAS for two types of temporary tables:

  • Local Temporary Tables, which are visible only in the current session
  • Global Temporary Tables, which are visible to all sessions

Local Temporary Tables (LTT)

  • Starts with the symbol ‘#’
  • Created with a CREATE TABLE statement, table name prefixed with a number sign (#numbersigntablename)
  • Visible in current sessions, cannot be accessed from later sessions
  • When created in a stored procedure, it will be automatically dropped when the procedure finishes
  • Nested stored procedures are needed to reference this LTT
  • Global Temporary Tables Can’t be referenced using stored procedure/application of the stored procedure to make LTT

Global Temporary Tables

  • Starts with the symbols ‘##’
  • Created with a CREATE TABLE STATEMENT, the table name is prefixed using two number signs (##twosignstablename)
  • Visible to all connections/sessions on SQL, can be used from other sessions
  • A global temporary table is dropped automatically as the table-creating session, and the other session’s final active Transact-SQL statement ends

Whether you’re familiar with temporary tables or new to them, the great news is that there’s always something more to learn about this subject as well as coding, programming, and computers. Temporary tables are easy to get the hang of once you get into working with them a bit. Thanks for reading, and happy learning!