How to hide SysStartTime and SysEndEtime columns in Temporal Tables

Temporal table was introduced with SQL Server 2016 and it is designed to capture and store changes of data in tables. In other words, similar to Change Data Capture (CDC), Change Tracking (CT), Temporal table maintains the history with changed details.

Temporal table needs two additional columns called SysStartTime and SysEndTime. Once they are added, they can be seen with the table just like other columns and will be appeared with SELECT * statement. Although it is not recommended to write SELECT * type of query against tables, unfortunately it can still be seen with many application and the database I had to analyze today had similar codes in almost all areas in the application. I had to make two tables as Temporal Tables and I had to make sure that it does not break the existing application.

Fortunately, SQL Server has given a solution for handling it. I was able to alter the table and make it as a Temporal Table without making changes to any statement written in the application while making sure that SELECT * does not return newly added SysStartTime and SysEndTime columns.

If you use, HIDDEN keyword when creating the Temporal Table, it makes sure that these two columns are not appeared when SELECT * is performed. However, columns can be explicitly mentioned in the SELECT if required.

-- changing existing table by adding columns
ALTER TABLE dbo.Customer  
ADD  ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN 
  CONSTRAINT DF_SysStartTime 
  DEFAULT CONVERT(datetime2 (0), '2017-02-24 00:00:00')
 , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
  CONSTRAINT DF_SysEndTime 
  DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59')
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);  
GO

-- turning versioning on
ALTER TABLE dbo.Customer
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));  
GO

-- Checking records  
SELECT * FROM dbo.Customer;  
SELECT *, ValidFrom, ValidTo FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;  

Can we access the SQL Server temporary table created in different database?

Temporary tables are nothing new and we have been using this for long time. There are two types of temporary tables; Local that starts with single pound sign (#) and Global that starts with double pound signs (##). Local Temporary Tables are limited to the connection created and will be discarded automatically when the connection is disconnected. Global Temporary Tables are global to the instance and it can be accessed by the anyone connected the instance. It will be dropped automatically when the last referenced connection is dropped.

Now the question is, when a Local Temporary Table is created, can I access it in another database?

Answer is yes and no. See the code below.

USE Sales;
GO

CREATE TABLE #TempTable
(
 Id int
);
GO

-- This works without any issue
SELECT * FROM #TempTable;

The created table can be access without any issue because access is done in the same database using the same connection. If we try the SELECT in another database with different window (different connection);

USE Sales;
GO

-- This will throw "Invalid object name '#TempTable'." error.
SELECT * FROM #TempTable;

You will see an error as above. However if I try to access the table from the same connection but different database;

--USE Sales;
--GO

--CREATE TABLE #TempTable
--(
-- Id int
--);
--GO

--SELECT * FROM #TempTable;

-- Same first connection but different database
USE AdventureWorks2014;
GO

-- This will work
SELECT * FROM #TempTable;

As you see, it is possible. Remember, Local Temporary Tables are limited to the connection, not to the database created, hence the created table can be accessed within any database as long as the connection is same.

Should I consider Collation when creating a temporary table?

We always create temporary tables for manipulating data for temporary processes and reports. However, unawareness of collation related issues with tempdb might introduce unexpected result with processes and reports, should we really consider it?

What is collation? Collation refers to set of rules that describe how to store and compare data. These rules includes order of characters, case-sensitivity of them, accent-sensitivity of them, kana character types and character width. SQL Server supports on setting collation at instance level, database level or even at column level.

Now, why we need to consider tempdb collation? Have a look on following example. It creates a Database. Note that collation of it is SQL_Latin1_General_CP1_CS_AS (Case Sensitive). Then the table is created and two sample records are added. Assume that this is the business requirement and last SELECT should be based on case and it returns only one record.

USE master;
GO

-- creating database with case-sensitive collation
CREATE DATABASE TestDatabase100 COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

USE TestDatabase100;
GO

-- create a table
CREATE TABLE dbo.TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100)
);
GO

-- insert two records
INSERT INTO dbo.TestTable 
VALUES
(1, 'abc'), (2, 'ABC');

-- Looking only for the second record
-- and this will return ONLY second one
-- as database is case sensitive (collation
-- for the columns are derived from top level)
SELECT * FROM dbo.TestTable WHERE Name = 'ABC';

Assume that same logic has to be implemented with another code using a temporary table, as a part of newly created database.

USE TestDatabase100;
GO

-- create a temporary table
CREATE TABLE #TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100)
);
GO

-- Load data from original table
INSERT INTO #TestTable 
SELECT Id, Name FROM TestTable;

-- Check for result
SELECT * FROM #TestTable WHERE Name = 'ABC';

This will be the result of this.

It returns both records instead of last records. The reason for this behavior is, tempdb collation. Since temporary table has been created inside the tempdb, collation of the tempdb has been used instead of collation of user-defined database. This is why we need to consider the collation of tempdb.

Below code shows how to check the collation of tempdb and how to create the temporary table properly.

-- Checking collation of tempdb
SELECT name, collation_name
FROM sys.databases
WHERE name = 'tempdb'
GO

-- create the temporary table properly
CREATE TABLE #TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS
);
GO