Tag Archives: Sql Server 2016

Temporal Tables in Sql Server 2016 Part 2: Querying System-Versioned Temporal Table

Sql Server, Sql Server 2016, , , , , , , , , , , , , , ,

This is the second article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article covers the querying of the Temporal tables by new temporal querying clause FOR SYSTEM_TIME and its five sub-clauses in the SELECT…FROM construct. This construct simplifies the querying of the History data and hides the complexity from the users. Even though the temporal table data exists in two tables one is the current table and other is the history table, this construct hides all the complexity of fetching data from these two tables.

Let us create a demo database with Temporal Table dbo.Customer with the following script:

CREATE DATABASE SqlHintsQueryingTemporalDB
GO
USE SqlHintsQueryingTemporalDB
GO
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END 
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))

The above script will create the Temporal Table dbo.Customer and the corresponding History Table dbo.CustomerHistory as shown in the following image:

SSMS View of Temporal and History Table

Let us populate the data in the temporal table by performing the following DML operations:

INSERT INTO dbo.Customer VALUES (1,'Basavaraj')
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Biradar' WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj P Biradar'
WHERE Id = 1
GO
WAITFOR DELAY '00:02:00'
GO
UPDATE dbo.Customer SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1
GO

Here I am keeping a delay of 2 minutes between each DML operations by using the WAITFOR DELAY statement.

Below image shows the records in the dbo.Customer and dbo.CustomerHistory tables after executing the above DML statements:

Records in the Temporal and History Table

Below is the depiction of the above DML operations on the Timeline graph:

System Versioning TimeLine Graph

Now we have the Temporal Table with the sample data, let us start understanding one-by-one the five sub-clauses of Temporal querying clause FOR SYSTEM_TIME:

AS OF <datetime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This sub-clause returns a record for each row that was valid at the specified point-in-time. In other words it will bring all the records from the Temporal Table and the history table which meet the following criteria:

StartTime<= @PointInTime AND EndTime > @PointInTime

Let us try to get the state of the data that was valid at ‘2015-12-29 07:51’. This point-in-time in the past is depicted in the below milestone graph by Yellow milestone point circle.

FOR SYSTEM_TIME AS OF TimeLine Graph

Execute the following query to get the records which were valid in the past point-in-time ‘2015-12-29 07:51’ by enabling the execution plan

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2015-12-29 07:51'

RESULT:
FOR SYSTEM_TIME AS OF Example 1

Below is the execution plan of this query execution.

FOR SYSTEM_TIME AS OF Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME AS OF clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the results from both the tables. One more observation from the execution plan is that on the temporal table we seeing Clustered Index scan, it is because our queries filter predicate is based on the Period columns but we don’t have index on these columns. So if we know that we are going to query by the Period columns it is better to add an index on these columns and by default Sql Server is adding an index on the period columns in the History table.

Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @PointInTime DATETIME = '2015-12-29 07:51'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime 
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime<= @PointInTime  AND EndTime > @PointInTime

RESULT
Equivalent of FOR SYSTEM_TIME AS OF

FROM <StartDateTime> TO <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns all the row versions which were some time active between <StartDateTime> and <EndDateTime> (i.e. all the row versions where StartTime is before the input <EndDateTime> and EndTime after the input parameter <StartDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime < <EndDateTime> AND EndTime > <StartDateTime>

Below is an example of a FROM … TO … clause:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME FROM @StartDateTime TO @EndDateTime

RESULT
FOR SYSTEM_TIME FROM TO Example

Below is the execution plan of this query execution.

FOR SYSTEM_TIME FROM TO Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME FROM … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime < @EndDateTime AND EndTime > @StartDateTime

RESULT
Equivalent of FOR SYSTEM_TIME FROM TO

BETWEEN <StartDateTime> AND <EndDateTime> sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause is same as the previous FROM <StartDateTime> TO <EndDateTime> clause except that it also includes the records which became active on <EndDateTime> (i.e. StartTime = <EndDateTime>). In other words it will return all the row versions which match the below criteria:

StartTime <= <EndDateTime> AND EndTime > <StartDateTime>

Let us execute the below query which is using the BETWEEN clause with the same @StartDateTime AND @EndDateTime as in the FROM clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
        @EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME BETWEEN @StartDateTime AND @EndDateTime

RESULT
FOR SYSTEM_TIME BETWEEN AND Example

From the result we can observe that BETWEEN clause is returning one extra record whose StartTime = @EndDateTime compared to the FROM clause

Below is the execution plan of this query execution.
FOR SYSTEM_TIME BETWEEN AND Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME BETWEEN … TO … clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime <= @EndDateTime AND EndTime > @StartDateTime

CONTAINED IN ( <StartDateTime> , <EndDateTime>) sub-clause of the temporal querying clause FOR SYSTEM_TIME

This clause returns only the row versions which became active and ended in the specified date range. In other words it will return all the row versions which match the below criteria:

StartTime >= <StartDateTime> AND EndTime <= <EndDateTime>

Let us execute the below query which is using the CONTAINED clause with the same @StartDateTime AND @EndDateTime which are used in the previous BETWEEN clause example

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME CONTAINED IN (@StartDateTime, @EndDateTime)

RESULT
FOR SYSTEM_TIME CONTAINED Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME CONTAINED Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME CONTAINED IN clause internally retrieving the data from both the Temporal Table and History Table which satisfies the criteria and then returning the UNION of the results from both the tables. Below is an equivalent query of this query, which we would have written if this construct was not available:

DECLARE @StartDateTime DATETIME = '2015-12-29 07:50',
	@EndDateTime DATETIME = '2015-12-29 07:54'
SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory
WHERE StartTime >= @StartDateTime AND EndTime <= @EndDateTime

FOR SYSTEM_TIME ALL

This clause returns all the row versions from both the Temporal and History table

Let us execute the below query which is using the ALL clause:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
FOR SYSTEM_TIME ALL

RESULT
FOR SYSTEM_TIME ALL Example
Below is the execution plan of this query execution.
FOR SYSTEM_TIME ALL Execution Plan

From the execution plan we can see that the FOR SYSTEM_TIME ALL clause internally retrieving the data from both the Temporal Table and History Table and returning the UNION of the both the results. Below is an equivalent query of this query, which we would have written if this construct was not available:

SELECT Id, Name, StartTime, EndTime 
FROM dbo.Customer
UNION ALL
SELECT Id, Name, StartTime, EndTime 
FROM dbo.CustomerHistory

[ALSO READ] New features in Sql Server 2016:

Temporal Tables in Sql Server 2016 Part 1: Introduction to Temporal Tables and DML operations on the Temporal Table

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , ,

Temporal Table is a new type of user defined table introduced in Sql Server 2016. Temporal table is like any other normal tables the main difference is for Temporal Tables Sql Server automatically tracks the full history of the data changes into a separate history table. Because of the tracking of the fully history of the data, it provides a mechanism to know the state of the data at any point in time.

With regular tables we can only know the current/latest state of the data, we will not be able to see the past state of the updated or deleted records. For regular tables if we need to keep track of the history developer need to create triggers and store the data in separate table there is no built in support for it. But with Temporal Tables Sql Server provides the built-in support for tracking the full history of the data and also provides the temporal querying construct FOR SYSTEM_TIME to query historic data at any point in time or for a period of time. Because of this built in support by the database engine to track the history of the data, Temporal Tables are referred as system-versioned temporal tables/system versioned tables.

  • Below are the some of the common uses of the System-Versioned Temporal Tables
  • It provides a mechanism to Audit the data changes as the complete history is maintained
  • Recovering from accidental data changes. For instance if someone has wrongly deleted a record, because of the availability of the history data we can easily recover these deleted records.

Maintain a Slowly Changing Dimension (SCD) for decision support applications

This is the first article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article will cover how we can create a new Temporal Table and DML operations on the Temporal Table with extensive list of examples

Creating a Temporal Table

Below image explain the syntax of the Temporal Table creation
Create Temporal Table Syntax

Following are the list of attributes/properties a table need to have to consider/make it as System Versioned Temporal Table:

  • Table needs to have a Primary Key. If table doesn’t have the primary key then it raises the following error:

    Msg 13553, Level 16, State 1, Line 5
    System versioned temporal table ‘ SqlhintsTemporalDemoDB.dbo.Customer’ must have primary key defined.

  • Table needs to have two DATETIME2 columns representing the start and end of the row’s validity period.
    • The row validity start time column is marked by the clause: GENERATED ALWAYS AS ROW START
    • The row validity end time column is marked by the clause: GENERATED ALWAYS AS ROW END

    Both these row validity start/end time columns should be NOT NULL, if NOT NULL constraint is not specified for these columns Sql Server will consider them as NOT NULL. If these columns are specified as NULL, then it will raise an error like the below:

    Msg 13587, Level 16, State 1, Line 5
    Period column ‘StartTime’ in a system-versioned temporal table cannot be nullable.

    We can mark these period columns as HIDDEN, if we mark them as HIDDEN then these columns will not appear in the result of query and also INSERT INTO statement without column list works without passing these column values in the values list. So this HIDDEN flag makes the enabling of the System Verionsed tables completely transparent to the users/application. It means the application will continue to work and behave functionally as it was previously without needing any changes in it.

  • PERIOD FOR SYSTEM_TIME (StartTime, EndTime) clause specifies the names of the columns that the Sql Server engine will use to record the period for which a record is valid

  • HISTORY_TABLE : This parameter is used to specify the Name of the History Table. It can be an existing table name whose schema matches with the current/Temporal table or it can be new table name which Sql Server will create.

    This parameter is optional, if it is not specified Sql Server generates history table name like: MSSQL_TemporalHistoryFor<primary_table_object_id>

  • SYSTEM_VERSIONING : This argument is used to enable/disable system versioning on the table

Execute the below script to create a new demo database and TEMPORAL TABLE

--Create demo data base
CREATE DATABASE SqlhintsTemporalDemoDB
GO
USE SqlhintsTemporalDemoDB
GO
--Create Temporal Table dbo.Customer
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START 
              HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END
              HIDDEN NOT NULL ,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CustomerHistory))

After executing the above statement, if we look into the SSMS window we can see that a dbo.Customer table created and it is marked as System-Versioned table. Inside this table we can see a nested table dbo.CustomerHistory table created and marked as History Table as shown in the below image:

SSMS View of the Temporal Table

DML operations on Temporal Table

Let us now understand one by one the DML INSERT, UPDDATE and DELETE operations on the Temporal Table.

INSERT Operation on Temporal Table

Let us execute the following statement to insert a record in the Temporal Table dbo.Customer by enabling the actual execution plan:

INSERT INTO dbo.Customer
VALUES (1,'Basavaraj Biradar')

RESULT
Inserting data into Temporal Table

The above statement worked even though we have not specified StartTime and EndTime column values. It worked because we have marked these two columns as HIDDEN, otherwise it would have failed. If these columns were not specified as HIDDEN in that case we need to pass these two column values as DEFAULT in the values clause.

From the above results execution plan, we can see that the record is inserted only in the Temporal/Main table dbo.Customer only.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT * FROM [dbo].[Customer]
--Get the records from the history table
SELECT * FROM [dbo].[CustomerHistory]

RESULT
Records in Temporal and History Table after Insert

From the above result we can say that INSERT operation will insert record only in the current (i.e. Temporal) table only, it will not insert the record in the history table.

Also we can observe that even though we have written our queries as SELECT *, still it did not return the period columns StartTime and EndTime of the Temporal Table as these columns are marked as HIDDEN. If we need to get these columns in the result we need to specify them explicitly in the SELECT statement as in the following statement.

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT
SELECT Statement with Period Columns

From the result we can see that Sql Server is recording the Period StartTime column with a datetime value which is present at the time of the execution of the statement and as this record is still open (i.e. still active) so the EndTime populated by the Sql Server is the DATETIME2 max value i.e. 9999-12-31 23:59:59.9999999.

UPDATE Operation on the Temporal Table

Let us execute the below update statement to update the name of the customer from Basavaraj Biradar to Basavaraj Prabhu Biradar by enabling the actual execution plan:

UPDATE dbo.Customer
SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1

RESULT
Updating data in the temporal table

From the execution plan, we can see that first it is updating the record in the dbo.Customer table and then Sql server is inserting a record in the dbo.CustomerHistory table.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT
Records in Temporal and History Table after Update

From the result we can see that update of a record in the temporal table results in inserting the record in the History table with state of the record before update and endtime will be the time at which the update statement is executed. And the Temporal (i.e. current/Main) table will have the updated row with starttime as the time at which the update statement is executed and as this record is still active it will have endtime as DATETIME2 data types max value.

Let us try to update the PERIOD columns and observe the behavior:

UPDATE dbo.Customer
SET Name = 'Basavaraj Prabhu Biradar',
	StartTime = GETUTCDATE(),
	EndTime = GETUTCDATE()
WHERE Id = 1

RESULT

Msg 13537, Level 16, State 1, Line 1
Cannot update GENERATED ALWAYS columns in table ‘SqlhintsTemporalDemoDB.dbo.Customer’.

From the result it is clear that we can’t update the PERIOD column values when SYSTEM_VERSIONING is ON. TO do this we need to first disable the system versioning and then drop the PERIOD definition from the Customer table. Then these two tables will become like any other regular tables and we can perform any operations on it.

DELETE Operation on the Temporal Table

Let us execute the following statement to delete the record from the customer table by enabling the execution plan

DELETE FROM dbo.Customer WHERE Id = 1

RESULT
DELETE data from Temporal Table

From the execution plan we can see that Sql Server is deleting the record from the Temporal Table i.e. dbo.Customer and Inserting the deleted record in the History table dbo.CustomerHistory.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT
Records in Temporal and History Table after Delete

From the results we can see that the record is deleted from the Temporal table and the state of the record before delete is stored in the History table with endtime of the record as the DELETE statement execution time.

TRUNCATE Operation on the Temporal table

Let us try executing the following truncate statement on the Temporal Table

TRUNCATE TABLE dbo.Customer

RESULT

Msg 13545, Level 16, State 1, Line 1
Truncate failed on table ‘SqlhintsTemporalDemoDB.dbo.Customer’ because it is not supported operation on system-versioned tables.

From the result it is clear that TRUNCATE operation is not supported on the TEMPORAL table

DML OPERATION on the HISTORY table

Let us try executing the following delete statement on the History Table

DELETE FROM dbo.CustomerHistory WHERE Id = 1

RESULT

Msg 13560, Level 16, State 1, Line 1
Cannot delete rows from a temporal history table ‘SqlhintsTemporalDemoDB.dbo.CustomerHistory’.

From the result it is clear that DML operation is not supported on the HISTORY table as long as the SYSTEM_VERSIONING is enabled

CLEAN-UP

Let us drop the Customer and CustomerHistory tables by executing the following script

--Disable the system versioning
ALTER TABLE dbo.Customer SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition
ALTER TABLE dbo.Customer
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Customer and CustomerHistory tables
DROP TABLE dbo.Customer 
DROP TABLE dbo.CustomerHistory

[ALSO READ] New features in Sql Server 2016:

GZIP COMPRESS and DECOMPRESS functions in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , , , ,

COMPRESS and DECOMPRESS are the new built in functions introduced in Sql Server 2016. This article explains these two new functions with extensive list of examples.

COMPRESS function compresses the input data using the GZIP algorithm and returns the binary data of type Varbinary(max).

DECOMPRESS function decompresses the compressed input binary data using the GZIP algorithm and returns the binary data of type Varbinary(max). We need to explicitly cast the output to the desired data type.

These functions are using the Standard GZIP algorithm, so a value compressed in the application layer can be decompressed in Sql Server and value compressed in Sql Server can be decompressed in the application layer.

Let us understand these functions with examples:

Example 1: Basic Compress and Decompress function examples

SELECT COMPRESS ('Basavaraj')

RESULT:
0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000

Let us decompress the above compressed value using the DECOMPRESS function by the following script

SELECT DECOMPRESS(
 0x1F8B0800000000000400734A2C4E2C4B2C4ACC0200D462F86709000000)

RESULT:
0x42617361766172616A

From the above result we can see that the result of the DECOMPRESS function is not the actual value but instead it is a binary data. We need to explicitly cast the result of the DECOMPRESS function to the datatype of the string which is compressed.

Let us cast the result of the DECOMPRESS function to Varchar type by the following statement.

SELECT CAST(0x42617361766172616A AS VARCHAR(MAX))

RESULT:
Basavaraj

Example 2: In this example COMPRESS and DECOMPRESS functions are used in one SELECT statement

SELECT 'Basavaraj' ValueToCompress,
  COMPRESS('Basavaraj') CompressedValue, 
  DECOMPRESS(COMPRESS('Basavaraj')) DeCompressedValue,
  CAST(DECOMPRESS(COMPRESS('Basavaraj')) AS VARCHAR(MAX))
     AS CastedDeCompressedValue

RESULT:
CompressAndDecompress

Example 3: In this example the value to be compressed is set to a variable

DECLARE @valueToCompress VARCHAR(MAX) = 'Basavaraj'
SELECT @valueToCompress ValueToCompress,
  COMPRESS(@valueToCompress) CompressedValue, 
  DECOMPRESS(COMPRESS(@valueToCompress)) DeCompressedValue,
  CAST(DECOMPRESS(COMPRESS(@valueToCompress)) AS VARCHAR(MAX))
   AS CastedDeCompressedValue

RESULT:
CompressAndDecompressVariable

Example 4: This example demonstrates the Compression of the same value of different type returns different encoded value

DECLARE @varcharValue VARCHAR(MAX) = 'Basavaraj', 
		@nVarcharValue NVARCHAR(MAX) = N'Basavaraj'
SELECT COMPRESS (@varcharValue) VarcharsCompressedValue 
SELECT COMPRESS (@nVarcharValue) NVarcharsCompressedValue

RESULT:
Different compressed result of the same value of different types

From the above result it is clear that both the variables have been set with same value but the compressed result is different because one variable is of type Varchar and other variable is of type NVarchar.

Example 5: This example demonstrates the importance of selecting the correct data type while casting the decompressed value

DECLARE @varcharValue VARCHAR(MAX) = 'Basavaraj', 
		@compressedValue VARBINARY(MAX)
--Compress the varchar value
SET @compressedValue = COMPRESS(@varcharValue) 
--Try to CAST the decompressed value as VARCHAR and NVARCHAR
SELECT @varcharValue varcharValue,  
 CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX)) 
         AS DecompressValueCastedToVarchar,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) 
         AS DecompressValueCastedToNVarchar

RESULT:
Casting decompressed value to different types

DECLARE @nVarCharValue NVARCHAR(MAX) = 'Basavaraj', 
		@compressedValue VARBINARY(MAX)
--Compress the nvarchar value
SET @compressedValue = COMPRESS(@nVarCharValue) 
--Try to CAST the decompressed value as VARCHAR and NVARCHAR
SELECT @nVarCharValue nVarcharValue,  
 CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX))
         AS DecompressValueCastedToVarchar,  
 CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX))
         AS DecompressValueCastedToNVarchar

RESULT:
Casting decompressed value to different types 2

From the above results it is clear that we need to CAST the decompressed value to the type of the actual original strings type to get the correct value

Example 6: This example demonstrates when the data compression will be effective

Let us execute the below statements

DECLARE @valueToCompress VARCHAR(MAX) = 'COMPRESS and DECOMPRESS'
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'
GO
DECLARE @valueToCompress VARCHAR(MAX) 
 = 'COMPRESS and DECOMPRESS are the new built in functions introduced in
    Sql Server 2016. This article explains these two new functions with 
	extensive list of examples.'
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'
GO
DECLARE @valueToCompress VARCHAR(MAX) 
 = 'COMPRESS and DECOMPRESS are the new built in functions introduced in Sql
    Server 2016. This article explains these two new functions with extensive
	list of examples. COMPRESS function compresses the input data using the 
	GZIP algorithm and returns the binary data of type Varbinary(max) ...  '
SELECT DATALENGTH(@valueToCompress) 'Data length of the value to compress', 
 DATALENGTH(COMPRESS(@valueToCompress)) 'Data length of the compressed value'

RESULT:
Compression Effectiveness
From the above results it is clear that as length of the string to compress increases the compression effectiveness increases.

Example 7: This example demonstrates how we can use COMPRESS and DECOMPRESS functions while inserting and retrieving data to/from the table column

Let us create a Customer table by executing the following script. In this table the AdditionalInfo column holds the value compressed by the COMPRESS function

CREATE TABLE dbo.Customer
(Id INT IDENTITY(1,1), Name NVARCHAR(100),
  AdditionalInfo VARBINARY(MAX))

Let us insert a record in the customer table by using the following script. Here we are compressing the value for the AdditionalInfo column before storing in it

INSERT INTO dbo.Customer (NAME, AdditionalInfo)
VALUES('Basavaraj Biradar', 
	 COMPRESS(N'Holds master''s degree in computer
	  applications with gold medals from Gulbarga university'))

Let us now execute the following statement to get the inserted record from the Customer table. Here we are using the DECOMPRESS function to return the decompressed value of the AdditionalInfo column.

SELECT Id, Name, AdditionalInfo CompressedAdditionalInfo, 
  CAST( DECOMPRESS(AdditionalInfo) AS NVARCHAR(MAX))
				AS DecompressedAdditionalInfo 
FROM dbo.Customer

RESULT:
Compress and Decompress Table Example
[ALSO READ]