Category Archives: 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]

SESSION_CONTEXT in Sql Server 2016

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

In .Net we have Session object which provides a mechanism to store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the SESSION_CONTEXT built in function.

This article explains in detail with extensive list of examples on how we can store the information that can be accessed anywhere in that session.

In this article for the Session Context key value pairs I will be interchangeably referring them as Session Variables. I hope most of the .Net developers too prefer calling them as session variables.

EXAMPLE 1: This example demonstrates how we can set the session context key named EmployeeId with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value
EXEC sp_set_session_context 'EmployeeId', 5000
--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

RESULT:
SessionContext Sql Example 1 1

Let us try to re-get the session context key EmployeeId value, but this time while specifying the key EmployeeId parameter skipping the prefix N:

--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT('EmployeeId') AS EmployeeId

RESULT:

Msg 8116, Level 16, State 1, Line 2
Argument data type varchar is invalid for argument 1 of session_context function.

From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.

Let us re-execute the above statement by prefixing the key parameter by N

--Retrieve the session variable EmployeeId value
SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

RESULT:
SessionContext Sql Example 1 2

EXAMPLE 2: Below example shows how we can update the Session variable value

--Set session variable EmployeeName value
EXEC sp_set_session_context @key = 'EmployeeName', 
  @value='Basavaraj'
--Retrieve the session variable EmployeeName value
SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName
--Re-Set the EmployeeName session variable value
EXEC sp_set_session_context @key ='EmployeeName',
  @value='Basavaraj Biradar'
--Retrieve the update session variable EmployeeName value
SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

RESULT:
updating session context value Example 2

EXAMPLE 3: Below example demonstrates a session variable can be declared as readonly

First create the readonly session variable City by using the following statement. We can mark a session variable as readonly by specying the parameter @read_only value as 1. This parameter is optional, if we don’t specify it or if we specify this parameter value as 0 then Sql Server will not allow to update this value later.

--create a readonly session variable City
EXEC sp_set_session_context @key = 'City', @value='Bangalore', 
  @read_only = 1
--Retrieve the session variable City value
SELECT SESSION_CONTEXT(N'City') City

RESULT:
SessionContext Setting Readonly Variable value Example 3

Now try to update the read-only session variable City value by the following statement

--try to update the read-only City session variable value
EXEC sp_set_session_context @key ='City',@value='Bengaluru'

RESULT:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 1
Cannot set key ‘City’ in the session context. The key has been set as read_only for this session.

SYNTAX of sp_set_session_context:

sp_set_session_context [ @key= ] 'key', [ @value= ] 'value'
    [ , [ @read_only = ] { 0 | 1 } ]

Where: @key parameter is of type SYSNAME, @value is of type SQL_VARIANT and @read_only parameter is of type BIT

SYNTAX of SESSION_CONTEXT

SESSION_CONTEXT(N'key')

Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT

EXAMPLE 4: Below example demonstrates how a session variable can be accessed in stored procedure which is set somewhere outside.

Let us create a stored procedure GetCountry by the following script:

CREATE PROCEDURE GetCountry
AS
BEGIN
	SELECT SESSION_CONTEXT(N'Country') Country
END

Then by using the following script create the session variable Country

--create a readonly session variable Country
EXEC sp_set_session_context @key = 'Country', @value='India'
--Retrieve the session variable Country value
SELECT SESSION_CONTEXT(N'Country') Country

RESULT:
Session Context Example 4 1

Now in the same session execute the stored procedure GetCountry and see whether this SP has access to the Session variable Country:

EXEC GetCountry

RESULT:
Accessing SessionContext from the Stored Procedure Example 4 2

EXAMPLE 5: Below example demonstrates the behavior when we try to fetch a non-existent session variable

SELECT SESSION_CONTEXT(N'Continent') Continent

RESULT:
Non Existent SessionContext Sql Example 5

From the result it is clear that, NULL value will be returned by SESSION_CONTEXT function if we try to fetch non-existent session variable.

The maximum size of the session context is limited to 256 kb. So there is no specific limit or the number of key and value pairs that can be stored in the Session Context as long as the total size is less than 256 Kb and it raises an error if the total size cross this max size limit. Note this limit of 256 Kb is at session level, so we can have multiple sessions at the same time with each session max limit is 256 kb.

We can check the overall memory usage for the session context across all the session by using the below statement:

SELECT cache_address, name, pages_kb, pages_in_use_kb, 
	entries_count, entries_in_use_count
FROM sys.dm_os_memory_cache_counters 
WHERE TYPE = 'CACHESTORE_SESSION_CONTEXT'

RESULT:
SessionContext Memory Usage Details in Sql Example 6

From the above result we can see that we have two active sql sessions with session context info stored. Out of these two session, the 2nd session is having 200 key value pairs stored in it and it is using 88 kb. And below is the script which is used to generate the 200 key value pairs:

DECLARE @Counter INT = 0,  @KeyName SYSNAME
WHILE @Counter < 200
BEGIN
  SET @KeyName = N'Key' + CAST(@Counter AS NVARCHAR);
  EXEC sys.sp_set_session_context @key = @KeyName, @value = 20
  SET @Counter += 1;
END

[ALSO READ]

Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

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

This article gives an introduction to the DATEDIFF and DATEDIFF_BIG functions by listing out the differences and similarities between them.

[ALSO READ]: How to get difference between two dates in Years, Months and days in Sql Server

DATEDIFF Function DATEDIFF_BIG Function
This function is available from very old versions of Sql Server. This function is introduced in Sql Server 2016
This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate
Syntax:
DATEDIFF( datepart, startdate, enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

Syntax:
DATEDIFF_BIG(datepart,startdate,enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

This functions return value data type is INT This functions return value data type is BigINT
Example: Below is an example getting difference between two dates in days using DATEDIFF function:

SELECT DATEDIFF(DAY,
 '12/01/2015','12/02/2015')
 AS 'Difference in days'

RESULT:
Difference in days
——————
1

Example: Below is an example getting difference between two dates in days using DATEDIFF_BIG function:

SELECT DATEDIFF_BIG(DAY,
 '12/01/2015','12/02/2015')
  AS 'Difference in days'

RESULT:
Difference in days
——————–
1

Example: This example demonstrates the behavior of the DATEDIFF function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(MILLISECOND,
 '12/01/2015','12/30/2015')
 AS 'Difference in MILLISECOND'

RESULT:

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

 Example: This example demonstrates the behavior of the DATEDIFF_BIG function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF_BIG(MILLISECOND,
 '12/01/2015','12/30/2015') 
AS 'Difference in MILLISECOND'

RESULT:
Difference in MILLISECOND
————————-
2505600000

The minimum and maximum value that this function can return is: -2,147,483,648 and +2,147,483,647 The minimum and maximum value that this function can return is: -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

ALSO READ