Performance of varbinary comparison in T-SQL

Question

One of a table columns is of VARBINARY type, need to raise a query to evaluate rows for particular bytes pattern, following approach dramatically decreases performance:

declare @pattern varbinary

// 19 bytes constant
set @pattern = 0x00.....

-- r.payload is of VARBINARY type as well
SELECT .... FROM ...
WHERE substring(r.payload, 0, 19) <> @pattern

Is there any alternative to the given approach?

Answer 1

Here you go, use a calculated column to store the first 19 bytes, PERSISTED so you can index it.

A demonstration can be found here on SQLFiddle.

When I perform the linked test, the persisted and indexed approach is about 5 times faster. This might increase significantly if the average [Payload] is very large.

CREATE TABLE [dbo].[YourTable]
(
    [Id] INT CONSTRAINT [PK_YourTable] PRIMARY KEY,
    [Payload] VARBINARY(MAX),
    [Prefix] AS CAST([Payload] AS BINARY(19)) PERSISTED
);

CREATE NONCLUSTERED INDEX [IX_YourTable_Prefix] ON [YourTable]([Prefix]);

allowing,

SELECT
            [Id]
    FROM
            [YourTable]
    WHERE
            [Prefix] <> @pattern 

or some such.

---

It is not entirely clear to me what your are trying to achieve but potentially the use of HASHBYTES might be useful.