Notes on SQL

Random articles from a puzzled DBA


The Microsoft documentation tells us that the SessionId column, within ASPStateTempSessions, consists of the Session ID plus the App ID. Although accurate, the method used for this does not make it obvious.


Our Developers needed to write a series of queries, to analyse the usage within ASPState – tracking the usage of this database to the applications.

In the default configuration, there are two tables we are interested in – ASPStateTempApplications and ASPStateTempSessions. The first table contains the application ID and the second has that value stored as part of the SessionId column – after some manipulation.


In this test example, we have two Applications within ASPStateTempApplications, with an AppId value of -2010518888 and 168339286.

Figure 1: The contents of the ASPStateTempApplications table

Test Applications

Within the table ASPStateTempSessions there are entries that show the session details. The SessioinId column is what we’re interested in.

Figure 2: The contents of the ASPStateTempSessions table

Test Session Ids

Test Session Ids


At first, I tried to find the value of AppId in SessionId and of course, found no match.

Then I converted the AppId into hexadecimal format.

The value of 168339286 converts to A08A756.

Figure 3: One value converted to hex and located within SessionId

The negative number revealed a slight twist to this methodology – it does not use the entire hexadecimal value. Using the Windows calculator, when I changed the ‘QWORD’ to ‘DWORD’ the exact value matched. Therefore, the integer value is being converted to a double word hexadecimal value.

Figure 4: The Qword value

Can’t find the entire Hex value

Figure 5: The DWord value

There it is

The SQL code to perform this conversion is:

Listing 1: SQL Code to convert AppId



FROM dbo.ASPStateTempApplications;


The AppId is converted from an integer, to a VarBiinary(16) and then appended on the end of a unicode value, to end up in an nvarchar(88).

Somebody, at some time, thought that was a good design.

No, I don’t know why.



Session State Providers

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: