Use a Table Variable for Logging Entries that Need to Survive Rollbacks.

• Louis Bravo

  SSC Veteran

  Points: 249

  More actions

  February 9, 2024 at 12:00 am

  #4374455

  Comments posted to this topic are about the item Use a Table Variable for Logging Entries that Need to Survive Rollbacks.

• carsten.saastamoinen

  Old Hand

  Points: 340

  More actions

  February 10, 2024 at 10:01 pm

  #4376595

  Table variables never stores data in memory but always in tempdb. It's also shown in the article referenced in the article.

  It is possible for table variables to define the constraints PRIMARY KEY, UNIQUE and CHECK, but not FOREIGN KEY. It is also possible to create indexes - always created as INLINE indexes.

  This is shown in the following script.

  USE master;
  GO
  DECLARE @TableVar1 TABLE
  ( 
  ID INT, 
  C1 VARCHAR(10)
  );
  
  DECLARE @TableVar2 TABLE
  ( 
  ID INT, 
  C2 VARCHAR(20)
  );
  
  DECLARE @TableVar3 TABLE
  ( 
  ID INT, 
  C3 VARCHAR(30)
  );
  
  SELECT *
  FROM tempdb.sys.tables;
  
  SELECT *
  FROM tempdb.sys.columns
  WHERE object_id < 0;
     
  DECLARE @TableVar4 TABLE
  ( 
  ID INT CHECK (ID > 247) PRIMARY KEY, 
  C4 VARCHAR(30) UNIQUE
  );
  
  DECLARE @TableVar5 TABLE
  ( 
  ID INT PRIMARY KEY CLUSTERED, 
  C5 VARCHAR(30) INDEX nc_C5_IDX,
  C6 VARCHAR(30) INDEX nc_C6_IDX,
  C7 VARCHAR(30) INDEX nc_C7_IDX,
  C8 VARCHAR(30) INDEX nc_C8_IDX,
  INDEX nc_C6_C8_C5 NONCLUSTERED (C6, C8, C5)
  );
  
  SELECT *
  FROM tempdb.sys.tables;
  
  SELECT *
  FROM tempdb.sys.columns
  WHERE object_id < 0;
  
  SELECT *
  FROM tempdb.sys.check_constraints
  WHERE object_id < 0;
  
  SELECT *
  FROM tempdb.sys.indexes
  WHERE object_id < 0;
  
  SELECT *
  FROM tempdb.sys.index_columns
  WHERE object_id < 0;
  GO
  SELECT *
  FROM tempdb.sys.tables;
  
  SELECT *
  FROM tempdb.sys.columns
  WHERE object_id < 0;