SQL Server, CONTEXT_INFO(), and varchar size
<p>In table <code>users</code> I have a column <code>username</code> of datatype <code>varchar(50)</code>. The table has no records. I insert a new record with <code>A</code> for the username. The following returns what I would expect:</p>

<pre><code>SELECT username, LEN(username) 
FROM users 
WHERE id = 1 -- returns: A, 1
</code></pre>

<p>So far so good.</p>

<p>Now I update table users from a trigger on another table, using the value from <code>CONTEXT_INFO()</code>:</p>

<pre><code>set @context = cast('B' as varbinary(128))
set CONTEXT_INFO @context

update some_other_table 
set x = 'y' 
where id = 97
</code></pre>

<p>In the trigger for <code>some_other_table</code> I do:</p>

<pre><code>DECLARE @context VARCHAR(128)

SELECT 
    @context = CAST(CONTEXT_INFO() AS VARCHAR(128)) 
FROM 
    master.dbo.SYSPROCESSES 
WHERE 
    SPID = @@SPID

DECLARE @user VARCHAR(50) = LEFT(@context, 50)

UPDATE users 
SET username = LTRIM(RTRIM(@user)) 
WHERE id = 1
</code></pre>

<p>The username is correctly set to "B", but the following now returns 50:</p>

<pre><code>SELECT 
    username, LEN(username) 
FROM 
    users 
WHERE 
    id = 1 -- returns: B, 50
</code></pre>

<p>The solution, when populating the context, is to do:</p>

<pre><code>set @context = cast('B' + replicate(' ', 126) as varbinary(128))
</code></pre>

<p>But why do I need to do this?</p>

<p>When I don't pad the <code>CONTEXT_INFO</code> with spaces what is happening that updating using its value will cause the resulting length to be 50 (even if I <code>ltrim</code> and <code>rtrim</code> the single character value before updating)?</p>

<p>And why must I pad my <code>CONTEXT_INFO</code> to 127 bytes total, not 128? For every character over 127, 1 character is truncated from the value originally set on <code>CONTEXT_INFO</code></p>

<p>Note: ANSI_PADDING is enabled</p>