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
Here you go, use a calculated column to store the first 19 bytes,
PERSISTED so you can index it.
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]);
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.