Sql-server – Convert varbinary(max) with CONVERT(nvarchar/varchar(max) ,value,0) gives no logic results

Hey there!

It has been quite a long time since I asked this question so I want to share my progress with you.

Introduction and Context:

There is a measurement system that is measuring 20 different values like temperature or pressure every minute and stores them into a database. My task is to access these values directly. So far, so easy, theoretically…

Tables of Interest in the Database

In order to access the measurement values directly, I only need two tables in the database.
The measurements are saved in the first table. Every measurement has a start- and stoptime and a discrete number of measurementvalues ("valueCount") stored in the column "value":

Header:  analogChannelRef | startTime           | stopTime            | valueCount | value     | crc
DBKey:   PK, FK           | PK                  |                     |            |           |
Type:    bigint           | datetime            | datetime            | int        | varbinary | int
Exmpl:   10181            | 03.08.2018 00:01:00 | 03.08.2018 00:10:00 | 10         | (BLOB)    | 61776375

The column "analogChannelRef" is in a relationship to the second Table that contains informations about the measured value. The column "channel number" states which input of the the measurement system is used for a specific measurement. The columns "tag" and "unit" are used to give further informations about the measurement. The column "precision" defines how many decimals are shown to the enduser:

Header:  id     | channel number | tag           | unit          | precision   | crc
DBKey:   PK     |                |               |               |             |
Type:    bigint | int            | nvarchar(255) | nvarchar(255) | tinyint     | int
Exmpl:   10181  | 15             | Pressure      | bar           | 2           | -740224624

So, where is the Problem?

There isn't one value for each minute, so i cannot simply read out the values. The table with the measurement data condenses ten values into one row, which is indicated by a "ValueCount"-Column with a Value of "10" in each row. For the actual "Value"-Column I can just see "(BLOB)". After a bit of research I found the datatype of the "Value"-Column is "varbinary(max)". So i tried to convert it via sql, this is the result for one example value:

SELECT CONVERT(nvarchar(max), value, 0) FROM mydatabase

_ᩲ웹퓤_耀㣽_____렀᳝ࣔ_愀·_____ꃾ은퓤_ 㤈_____䐀摤ࣔ_ꈀ8_____➊있퓤_뀀㤻_____퀀ꯪࣔ_珀8_____긖쟏퓤_䀀㥅_____尀ࣔ_畀9_____㒢젗퓤__㥳_____㫷ࣔ_ꤠ8_____

(Underscore represents Space in result)

SELECT CONVERT(nvarchar(max), value, 1) FROM mydatabase

0x0100721AF9C6E4D408000080FD380000000000000000000000B8DD1CC7E4D40800000061B70000000000000000000000FEA040C7E4D40800002008390000000000000000000000446464C7E4D408000000A23800000000000000000000008A2788C7E4D4080000B03B390000000000000000000000D0EAABC7E4D4080000C07338000000000000000000000016AECFC7E4D408000040453900000000000000000000005C71F3C7E4D40800004075390000000000000000000000A23417C8E4D40800000073390000000000000000000000E8F73AC8E4D408000020A93800000000000000000000

As Max Vernon answered (thanks again your help), it looks like the result is structured in 10 packages, what would fit to the number of measurements stored into one "value"-cell. But I couldn't find a solution on how to get readable data based on that information.

Possible Solution

To find a solution on how to get the actual values, I further investigated the Microsoft SQL Server Management Studio and found so called "procedures". After a bit of research on procedures I found one that looked really promesing for my task:

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[report_getAnalogvalue](
    @channelId BIGINT,
    @startDate DATETIME,
    @endDate DATETIME,
    @configurationId BIGINT,
    @userId BIGINT,
    @maxNumber BIGINT)
AS
    DECLARE @AnalogValueTable TABLE
    (
        "timestamp" DATETIME, 
        "timestampoffset" BIGINT,
        "value" INT,
        "valueState" VARBINARY, 
        "dataIntegrity" TINYINT -- 1 = true, 0 = false
    )

    DECLARE @values VARBINARY(MAX),
            @record_length INT,
            @valueCount INT,
            @crc INT,

            @record_data VARBINARY(23),
            @record_timestamp DATETIME,
            @record_value VARBINARY(4),
            @record_valuestate VARBINARY,
            @record_timestampoffset BIGINT

    DECLARE @record_dataIntegrity TINYINT
    SET @record_dataIntegrity = 0

    DECLARE @rawTime VARBINARY(8)

    DECLARE @record_counter BIGINT
    SET @record_counter = 1

    DECLARE @Ticks BIGINT,
            @Days FLOAT

    DECLARE @isAdministrator BIT
    SET @isAdministrator = 0
BEGIN
    SET NOCOUNT ON  

    EXECUTE @isAdministrator = report_checkAccess @configurationId, @userId

    DECLARE c CURSOR FOR
            SELECT a.value, a.valueCount, a.crc
            FROM "AnalogValue" a INNER JOIN "AnalogChannel" b ON a."analogChannelRef" = b."id"              
            WHERE a."analogChannelRef" = @channelId
                AND (@startDate BETWEEN a."startTime" AND  a."stopTime" 
                    OR @endDate BETWEEN a."startTime" AND  a."stopTime" 
                    OR a."startTime" BETWEEN @startDate AND @endDate
                    OR a."stopTime" BETWEEN @startDate AND @endDate)
                AND (b."configurationRef" = @configurationId)
                AND (@isAdministrator = 1)
    OPEN c

    FETCH NEXT FROM c INTO @values, @valueCount, @crc

    WHILE @@FETCH_STATUS = 0 BEGIN
        SET @record_length = DATALENGTH(@values)
        IF (@record_length <> @valueCount * 23 + 1 OR @record_length < 24)
            BREAK

        IF dbo.GET_CRC32(@values) = @crc
            SET @record_dataIntegrity = 1

        SET @values = SUBSTRING(@values, 2, @record_length-1)
        SET @record_length = DATALENGTH(@values)
        WHILE (@record_length >= 23)
        BEGIN
            IF (@record_counter > @maxNumber)
            BEGIN
                CLOSE c
                DEALLOCATE c

                SELECT * FROM @AnalogValueTable

                RETURN
            END

            SET @record_data = SUBSTRING(@values, 1, 23)
            SET @rawTime = SUBSTRING(@record_data, 1, 8)
            EXECUTE ParseTimestamp @rawTime, @timestamp=@record_timestamp OUTPUT

            IF (@record_timestamp > @endDate)
                BREAK

            IF (@record_timestamp BETWEEN @startDate AND @endDate)
            BEGIN
                -- use REVERSE to convert from little endian to big endian
                SET @record_value = CAST(CONVERT(BINARY(4), REVERSE(SUBSTRING(@record_data, 10, 4))) AS INT)                
                SET @record_valuestate = SUBSTRING(@record_data, 14, 1)

                -- use reverse to convert from little endian to big endian
                SET @record_timestampoffset = CAST(CONVERT (BINARY(8), REVERSE (SUBSTRING(@record_data, 16, 8))) AS BIGINT)

                INSERT INTO @AnalogValueTable("timestamp", timestampoffset, value, valueState, dataIntegrity) 
                VALUES(@record_timestamp, @record_timestampoffset, @record_value, @record_valuestate, @record_dataIntegrity)

                SET @record_counter = @record_counter + 1
            END

            SET @record_length = @record_length - 23
            SET @values = SUBSTRING(@values, 24, @record_length)
        END

        FETCH NEXT FROM c INTO @values, @valueCount, @crc
    END

    CLOSE c
    DEALLOCATE c

    SELECT * FROM @AnalogValueTable
END

If I call this procedure with the right input parameters I actually get values the values for each minute.

So my first question is: How does this procedure work?

So, everything is fine?

I'm not sure. The Values that I get by calling the procedure are in form of Integers. But my measurements are numbers like 80,51 °C. So I have to devide the Integer I get by a specific number to get the real value.

Hence, my second question is: Why are the Values stored as Integers?

Finally, the correlation between the Integer-Values and the real Values is quite interesting. In order to find the correlation, I plotted the Integer-Values over the real Values. I expected a linear function, but i got multiply linear functions for specific values of the real value:

These regions seem to follow a pattern: 0,5 – 1; 1 – 2; 2 – 4; 4 – 8; and so on.

So my last question is: Is there any reason, from the perspective of an database expert, to store values like this?

Lars