I'm going to create a table with an specific column which stores 128-bit unsigned values.

I have below constraints:

1. I should query this column for previous duplicate values in shortest possible time (Note: this is not a uniqueness constraint).

2. I should insert values with low timing overhead.

3. Number of records may be around tens of millions (e.g. 90M).

4. I want to display the column value as hex.

5. I want to query the column value using hex input strings.

According to this answer, using binary[16] is suggested for storing 128-bit values (without considering about constraints).

So my questions is which data type can be suitable for my column data type? (Char(32), Binary(16), ...).

I'm currently using char(32) to store 128-bit value in hex representation and I want to know If I can improve my database performance or not? I guess since numbers are stored and processed as text values, it decreases the DB query performance and there might be data-types with better performance.

CodePudding user response：

binary(16) is the most appropriate column data type for 128-bit unsigned values since SQL Server does not have a 128-bit unsigned type. This will reduce storage and memory requirements by half compared to char(32).

I should query this column for previous duplicate values in shortest possible time (Note: this is not a uniqueness constraint).

Create an index on the column to avoid a full table scan.

I should insert values with low timing overhead.

There is a minor insert performance cost with the above index but will be more than offset by the runtime savings.

Number of records may be around tens of millions (e.g. 90M).

A 100M row table will only be a few GB (depending on row size) and will likely be memory resident on an adequate sized SQL instance if the table is used often.

I want to display the column value as hex.

As with all display formatting, this is a task best done in the presentation layer but can be done in T-SQL as well.

I want to query the column value using hex input strings.

Ideally, one should match the column data types when querying but a hex string can be explicitly converted to binary if needed.

Example T-SQL:

CREATE TABLE dbo.YourTable (
      YourTableID int NOT NULL CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
    , BinaryValue binary(16) NOT NULL INDEX idx_BinaryValue NONCLUSTERED
    , OtherData varchar(50) NOT NULL
);
INSERT INTO dbo.YourTable VALUES(1,0X000102030405060708090A0B0C0D0E0F,'example 1');
INSERT INTO dbo.YourTable VALUES(2,0X000102030405060708090A0B0C0D0E00,'example 2');
INSERT INTO dbo.YourTable VALUES(3,0X000102030405060708090A0B0C0D0E0F,'example 3 duplicate value');

--example query values
DECLARE @BinaryValue binary(16) = 0X000102030405060708090A0B0C0D0E0F;
DECLARE @CharValue char(32) = '000102030405060708090A0B0C0D0E0F';

--matching type query
SELECT YourTableID, BinaryValue, OtherData, CONVERT(char(16), BinaryValue, 2) AS DisplayValue
FROM dbo.YourTable
WHERE BinaryValue = @BinaryValue;

--query with hex string
SELECT YourTableID, BinaryValue, OtherData, CONVERT(char(16), BinaryValue, 2) AS DisplayValue
FROM dbo.YourTable
WHERE BinaryValue = CONVERT(binary(16), @CharValue, 2);
GO