(last updated: 2019-01-05 @ 23:15 EST / 2019-01-06 @ 04:15 UTC )

Let’s say that you execute the following T-SQL:

SET CONTEXT_INFO 1234;
SELECT CONVERT(INT, CONTEXT_INFO());

The result will be:

0

Why is that? The value passed in is an INT, which is only 4 bytes, yet CONTEXT_INFO can hold up to 128 bytes. Certainly 4 can fit within 128.

The underlying cause of this (frustrating / annoying) behavior is in three parts:

1. The setting of CONTEXT_INFO by the INT value causes an implicit conversion of that INT into either VARBINARY(anything) or BINARY(4).

2. Adding a smaller BINARY / VARBINARY value to a larger BINARY variable or column simply adds 0s to the end (i.e. right side) of the value (similar to blank padding with CHAR ). This is because a binary datatype should be viewed as a binary string, not a binary representation of a numeric value. A binary representation of a number starts from the far right side of the value, but a binary datatype can also hold binary data, such as a PDF file, or PNG / JPG / MP3, etc. And when dealing with binary data, the value starts from the far left. Meaning:

   • Binary representation of a number: 0x00{padding}00000{value}
   • Binary data: 0x{value}0000{padding}00

      

3. The CONTEXT_INFO() built-in function is doing something that it probably should not be doing.

Investigation

As you can see below, when converting to VARBINARY it only takes the amount of bytes that it needs, and it only needs 4 bytes for an INT. But in both cases, the value starts on the right side of the alloted space.

SELECT CONVERT(VARBINARY(128), 1234) AS [ToVARBINARY];
-- 0x000004D2
SELECT CONVERT(BINARY(128), 1234) AS [ToBINARY];
-- 0x000000...00000000004D2  (was 128 total bytes; only removed 0s)

The next two queries show that converting both of the resulting values from the two queries above returns the correct value. This is due to reading from the right side when dealing with binary representations of numeric values:

SELECT CONVERT(INT, CONVERT(VARBINARY(128), 1234)) AS [ToVARBINARY];
-- 1234
SELECT CONVERT(INT, CONVERT(BINARY(128), 1234)) AS [ToBINARY];
-- 1234

Next, the following two queries show that converting the smaller value to a larger BINARY container (variable or column) simply adds 0s to the right of the initial value instead of moving the value down to the right (since doing that would never make sense for actual binary data). And then, once you have those extra 0s, converting back to VARBINARY(128) doesn’t get rid of them.

SELECT CONVERT(BINARY(128), CONVERT(VARBINARY(128), 1234))
           AS [ToVARBINARYthenBINARY(128)];
-- 0x000004D200000...0000  (was 128 total bytes; only removed 0s)
SELECT CONVERT(VARBINARY(128), CONVERT(BINARY(128), CONVERT(VARBINARY(128), 
           1234))) AS [ToVARBINARYthenBINARY(128)thenVARBINARY(128)];
-- 0x000004D200000...0000  (was 128 total bytes; only removed 0s)

Of course, the same behavior can be seen when using VARCHAR and CHAR. Once you add in the blank padding, it is part of the value:

DECLARE @C VARCHAR(10) = 'c';
SELECT '-' + @C + '-' AS [Original],
       '-' + CONVERT(CHAR(10), @C) + '-' AS [ConvertedToCHAR],
       '-' + CONVERT(VARCHAR(10), CONVERT(CHAR(10), @C)) + '-'
           AS [ConvertedToCHARthenBackToVARCHAR];
/*
Original    ConvertedToCHAR    ConvertedToCHARthenToVARCHAR
-c-         -c         -       -c         -
*/

With all of that in mind, what exactly is CONTEXT_INFO() returning?

SET CONTEXT_INFO 1234;
SELECT SQL_VARIANT_PROPERTY(CONTEXT_INFO(), 'BaseType') AS [BaseType],
       SQL_VARIANT_PROPERTY(CONTEXT_INFO(), 'TotalBytes') AS [TotalBytes],
       CONTEXT_INFO() AS [CONTEXT_INFO()];
/*
BaseType     TotalBytes    CONTEXT_INFO()
varbinary    132           0x000004D200000...0000  (was 128 total bytes;
                                                    only removed 0s)
*/

That’s odd. It shows VARBINARY as the type, but 132 bytes? I was expecting to see a value of 128.

SELECT SQL_VARIANT_PROPERTY(0x, 'TotalBytes') AS [TotalVarbinaryBytes],
       SQL_VARIANT_PROPERTY('', 'TotalBytes') AS [TotalVarcharBytes];
/*
TotalVarbinaryBytes    TotalVarcharBytes
4                      8
*/

Ah, the 4 bytes is the overhead / metadata. Probably the length of the value. Same with the 8 bytes for the VARCHAR value: length and CollationID (or at least LCID)

Somehow the CONTEXT_INFO() built-in function is returning a VARBINARY(128) value that, at some point, got converted to BINARY(128). What to do now with our value “stuck” on the left side of the binary value?

We just force it back into a smaller container, which truncates everything to the right of the new container size, which started counting on the left side because, again, this is binary data:

SELECT CONVERT(BINARY(4), CONVERT(BINARY(128), CONVERT(VARBINARY(128),
           1234))) AS [ToVARBINARYthenBINARY(128)thenBINARY(4)];
-- 0x000004D2

If we use the correct number of bytes (i.e. 4) then it doesn’t matter whether we specify BINARY or VARBINARY.

So where did the value get converted to BINARY(128)? Is that how it is stored internally? Or, maybe it happened when setting the value? To figure this out, we can check another source for getting the “context_info” value:

SELECT sess.[context_info], CONVERT(INT, sess.[context_info]) AS [Converted]
FROM   sys.dm_exec_sessions sess
WHERE  sess.[session_id] = @@SPID;
/*
context_info     Converted
0x000004D2       1234
*/

Now isn’t that interesting? The DMV shows the “context_info” value as it was originally. This means that setting the value did not change it, nor is it stored as BINARY(128). That would seem to narrow down the issue to being entirely within the CONTEXT_INFO() built-in function. So this is most likely a bug. But I probably won’t report it since at this point so much code has been written to work with the current behavior that it doesn’t make much sense to fix. It is probably better to keep it as annoying rather than break a ton of existing code.

Putting it All Together

Given all of the above, why exactly does the following code (same code as we saw at the beginning) return 0?

SET CONTEXT_INFO 1234;
SELECT CONVERT(INT, CONTEXT_INFO());

Because the following steps are how the code above is being processed:

1. The integer value of “1234” is implicitly converted to VARBINARY by SET CONTEXT_INFO.
   1. An INT is 4 bytes, so the resulting binary representation will be 4 bytes
   2. A binary representation of a number starts on the right side of the value (or maybe think of it as right-justified); this is the same as with decimal numbers.
   3. The result is that 0x4D2 (the hex representation of the decimal value 1234) is stored as 0x000004D2
2. Even though the actual context_info value is stored as 0x000004D2, the CONTEXT_INFO() built-in function assumes the stored values to be BINARY(128) and so adds 0s to the right of the stored value such that it always returns 128 bytes.
   1. The result is that the returned value is: 0x000004D2{240 zeros}00000000

      (that’s 8 + 240 + 8 hex digits = 256 hex digits = 128 bytes)

3. Converting to INT means interpreting the binary value as a binary representation of a number instead of a binary string / binary data.
   1. A binary representation of a number starts on the right side of the value.
   2. An INT is 4 bytes, so 4 bytes will be read starting from the right side of the value.
   3. The right-most 4 bytes of the returned value are: 00000000 .
   4. A binary value of 0x00000000 equates to an integer value of 0.

Binary Representation of a Number vs. Binary Data

The difference is that a binary representation of a number treats the entire binary value as a single numeric entity. Given that a BIGINT can hold a rather large number and is only 8 bytes (i.e. 0x0000000000000000 ), our value here of 4D2 followed by 248 0s is an incredibly large number. That’s like having a decimal value of 42 and adding 1000 zeroes to the right of it (i.e. 420,000,000,000,... ).

On the other hand, binary data is a binary string. This is similar to a text string, but made up of bytes instead of characters. In both cases, you start on the left and keep adding on to the right.

Binary data does not care about the extra 0-padding to the right of the value. So even though the CONTEXT_INFO() built-in function adds them in, it doesn’t hurt converting back to the original value.

Below we see that UNIQUEIDENTIFIER, which is essentially a binary value, works just fine:

DECLARE @GUID UNIQUEIDENTIFIER = NEWID();
SET CONTEXT_INFO @GUID;
SELECT @GUID AS [Original],
       CONTEXT_INFO() AS [CONTEXT_INFO()],
       CONVERT(UNIQUEIDENTIFIER, CONTEXT_INFO()) AS [ToUniqueIdentifier];
/*
Original           - A88D38D0-4AE7-4031-838C-FDF393BF744E
CONTEXT_INFO()     - 0xD0388DA8E74A3140838CFDF393BF744E000...000
ToUniqueIdentifier - A88D38D0-4AE7-4031-838C-FDF393BF744E        
*/

And below we see that converting text works just fine (just be sure not to switch between VARCHAR and NVARCHAR, in either direction, when converting back):

DECLARE @TextBin VARBINARY(20) = CONVERT(VARBINARY(20), 'test stuffs');
SET CONTEXT_INFO @TextBin;
SELECT @TextBin AS [Original],
       CONTEXT_INFO() AS [CONTEXT_INFO()],
       CONVERT(VARCHAR(20), CONTEXT_INFO()) AS [ToVARCHAR],
       CONVERT(NVARCHAR(20), CONTEXT_INFO()) AS [ToNVARCHAR];
/*
Original       - 0x7465737420737475666673
CONTEXT_INFO() - 0x7465737420737475666673000...000
ToVARCHAR      - test stuffs
ToNVARCHAR     - 整瑳猠畴晦s
                 ( U+6574 , U+7473 , U+7320 , U+7574 , U+6666 , U+0073 )
Read as UTF-16LE: 0x 7465     7374     2073     7475     6666     7300
*/

Conclusion

To get the expected value out of context_info when not dealing with binary data, you have the following options:

1. If the value did not get set as BINARY(128), then convert the value coming back from CONTEXT_INFO() to the number of bytes used by the value that was set.
2. Prior to setting, convert the value to set to be BINARY(128)
3. Get the value from the [context_info] field of the sys.dm_exec_sessions DMV as it returns the expected VARBINARY value.

Also, if you are using SQL Server 2016 or newer, you might want to take a look at the following new feature as it has several benefits over context_info :

• sp_set_session_context
• SESSION_CONTEXT

Update Regarding Working With Strings (2019-01-05)

In a comment (below), Kevin C. pointed out that working with strings is not as simple / easy as I have represented above. He is correct. The problem is, when getting the value out of CONTEXT_INFO(), the 0x00 bytes are still there, at the end of the string. You can actually see them in the code block directly above the “Conclusion” header (scroll up just a little). The “Original” value is 0x7465737420737475666673, while the value returned by CONTEXT_INFO() is 0x7465737420737475666673000...000. I was being overly simplistic because, even though the 0x00 bytes are not visible when converting the value to VARCHAR, they are definitely still there, in the variable or column. For example:

DECLARE @TextBin VARBINARY(128) = CONVERT(VARBINARY(128), 'test stuffs');
SET CONTEXT_INFO @TextBin;
SELECT @TextBin AS [Original],
       LEN(@TextBin) AS [LenOriginal],
       LEN(CONVERT(VARCHAR(20), @TextBin)) AS [LenOriginalToVARCHAR],
       CONTEXT_INFO() AS [CONTEXT_INFO()],
       CONVERT(VARCHAR(20), CONTEXT_INFO()) AS [ToVARCHAR],
       LEN(CONVERT(VARCHAR(20), CONTEXT_INFO())) AS [LenToVARCHAR],
       CONVERT(VARCHAR(20), CONTEXT_INFO()) + 'A' AS [ToVARCHAR+A],
       LEN(CONVERT(VARCHAR(20), CONTEXT_INFO()) + 'A') AS [LenToVARCHAR+A];
/*
Original             - 0x7465737420737475666673
LenOriginal          - 11
LenOriginalToVARCHAR - 11
CONTEXT_INFO()       - 0x7465737420737475666673000...000
ToVARCHAR            - test stuffs
LenToVARCHAR         - 20
ToVARCHAR+A          - test stuffs
LenToVARCHAR+A       - 21
*/

As you can see in the output shown above, the “ToVARCHAR” field returns the original string value, but the “LenToVARCHAR” value shows that there are still 9 extra bytes in that string (they just aren’t visible). The last two fields should make it even clearer that: a) 0x00 bytes are not only not visible, but they prevent the display of any characters that follow (most likely due to being the string terminator in C/C++); and b) those characters — the 0x00 and the A — are definitely there.

Fortunately there are (at least) three ways to get the string without the 0x00 bytes:

1. Use a combination of the REPLACE() function and a binary (_BIN2, not _BIN) collation. When this function is used with a non-binary collation, none of the 0x00 bytes are replaced (probably because 0x00 is still being treated as the string terminator). This approach is possible starting in (at least) SQL Server 2005. For SQL Server 2000 (and earlier? ouch!), you will need to use a _BIN collation.

2. Use the TRIM() function (introduced in SQL Server 2016). This funtion does not require using a binary collation. However, it is only available starting with SQL Server 2016.

3. Get from the sys.dm_exec_sessions DMV. As we saw above, this field treats the value as being VARBINARY(128) (as it should be) instead of BINARY(128). This DMV is available starting in SQL Server 2005. For SQL Server 2000 (and earlier? ouch!), the context_info column can be found in master.dbo.sysprocesses.

Please note that RTRIM() is not an option due to it only removing spaces.

GO
DECLARE @TextBin VARBINARY(128) = CONVERT(VARBINARY(128), 'test stuffs');
SET CONTEXT_INFO @TextBin;
SELECT REPLACE(CONTEXT_INFO(), CHAR(0), '') + 'B' AS [REPLACE],
       LEN(REPLACE(CONTEXT_INFO(), CHAR(0), '') + 'B') AS [LenREPLACE],
       REPLACE(CONTEXT_INFO(), CHAR(0) COLLATE Latin1_General_100_BIN2,
               '') + 'C' AS [REPLACE+BIN2],
       LEN(REPLACE(CONTEXT_INFO(), CHAR(0) COLLATE Latin1_General_100_BIN2,
               '') + 'C') AS [LenREPLACE+BIN2],
       TRIM(CHAR(0) FROM CONVERT(VARCHAR(20), CONTEXT_INFO())) + 'D'
               AS [TrimToVARCHAR],
       LEN(TRIM(CHAR(0) FROM CONVERT(VARCHAR(20), CONTEXT_INFO())) + 'D')
               AS [LenTrimToVARCHAR],
       [context_info] AS [FromExecSessions],
       LEN([context_info]) AS [LenExecSessions],
       CONVERT(VARCHAR(20), [context_info]) + 'E'
               AS [ExecSessionsToVARCHAR],
       LEN(CONVERT(VARCHAR(20), [context_info]) + 'E')
               AS [LenExecSessionsToVARCHAR]
FROM   sys.dm_exec_sessions
WHERE  [session_id]  = @@SPID;
/*
REPLACE                  - test stuffs
LenREPLACE               - 129
REPLACE+BIN2             - test stuffsC
LenREPLACE+BIN2          - 12
TrimToVARCHAR            - test stuffsD
LenTrimToVARCHAR         - 12
FromExecSessions         - 0x7465737420737475666673
LenExecSessions          - 11
ExecSessionsToVARCHAR    - test stuffsE
LenExecSessionsToVARCHAR - 12
*/