Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016 – Temporal Table : Hidden Column

In this post we shall check out the hidden column feature for Temporal Table columns

Syntax with [ HIDDEN ] attribute

--

--Simple CREATE TABLE Syntax (common if not using options)  
CREATE TABLE   
    [ database_name . [ schema_name ] . | schema_name . ] table_name   
    ( { <column_definition> } [ ,...n ] )  
	..[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]  
[ ; ]  

--

Now let’s take an example. Create a temporal Table called temporalTable1.

--

CREATE TABLE temporalTable1
(
    Id INT PRIMARY KEY
  , Val Varchar(100)
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END   
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.HistorytesttemporalTable1));  
GO  

--

Now If you execute SELECT * from temporalTable1 you will get data from all the columns.


SELECT * FROM temporalTable1


--OUTPUT

Id          Val        ValidFrom       ValidTo      
----------- ---------- ------------ -- ------------ 
                                                    
(0 rows affected)                                   
                                                    
--

Note that columns like ValidFrom and ValidTo are visible here. Now we can make them hidden by using below method.

--

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidFrom] ADD HIDDEN
GO

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidTo] ADD HIDDEN
GO                              
                                                    
--OUTPUT

	Commands completed successfully.

Now again execute SELECT * from temporalTable1 and see if you get data from all the columns.


SELECT * FROM temporalTable1


--OUTPUT

Id          Val
----------- -----------

(0 rows affected)
                                
                                                    
--

See here you will get only first two columns only. Remaining date columns will not be SELECTED.

If you want unhide the columns then use below command.

--

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidFrom] DROP HIDDEN
GO

ALTER TABLE temporalTable1 
	ALTER COLUMN [ValidTo] DROP HIDDEN
GO
                            
                                                    
--

Notes

1. For hidding a column GENERATED ALWAYS AS ROW START/END is compulsory.
2. System-versioned table cannot have more than one ‘GENERATED ALWAYS AS ROW END’ column
3. System-versioned table cannot have more than one ‘GENERATED ALWAYS AS ROW START’ column

Refer Microsoft Link below for more details-

1 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

Enjoy !!! 

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements