Wednesday, 21 March 2018

Temporary Tables - SQL Server

SQL Server Temporary tables are a special type of tables that are written to the TempDB database and act like regular tables.

There are four main types for the temporary tables;
  • Local Temporary table, which is named starting with a # symbol (e.g. #TempShipments), that can be referenced only by the current database session and discarded by its disconnection,
  • a Global Temporary table, which is named starting with ## (e.g. ##TempShipments), that can be referenced by any process in the current database and discarded when the original database session that created that temp table disconnected or until the last statement that was referencing the temp table has stopped using it, as anyone who has access to the system TempDB database when that global temporary table is created will be able to use that table,
Both temporary tables and table variables are stored in the TempDB database, but there are many differences between them such as:
  • Temporary tables are created using CREATE TABLE T-SQL statement, but table variables are created using DECLARE @name Table T-SQL statement.
  • You can ALTER the temporary tables after creating it, but table variables don’t support any DDL statement like ALTER statement.
  • Temporary tables can’t be used in User Defined Functions, but table variables can be.
  • Temporary tables honor explicit transactions defined by the user, but table variables can’t participate in such transactions.
  • Temporary tables support adding clustered and non-clustered indexes after the temporary table creation and implicitly by defining Primary key constraint or Unique Key constraint during the tables creation, but table variables support only adding such indexes implicitly by defining Primary key constraint or Unique key constraint during tables creation.
  • Temporary tables can be dropped explicitly, but table variables can’t be dropped explicitly, taking into consideration that both types are dropped automatically when the session in which they are created is disconnected.
  • Temporary tables can be local temporal tables at the level of the batch or stored procedure in which the table declared or global temporal tables where it can be called outside the batch or stored procedure scope, but table variables can be called only within the batch or stored procedure in which it is declared.

1 comment:

  1. Hi Bro,

    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.

    In AngularJs 1, in case of ngRepeat there is syntax called limitTo which basically restrict the for loop for a certain number inspite of main array may contains more than that limitTo value. But in angular 5, limitTo has been dropped in case of ngFor. Then what is the alternative of that ?
    I look forward to see your next updates.

    Thank you,

    ReplyDelete

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)