表中有一列的数据类型为VARBINARY，需要查询具有特定字节模式的行，以下方法严重降低了性能：
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
有没有别的方法可以替代这种方式？
这里有一个方法，使用计算列来存储前19个字节，并使用PERSISTED使其可以被索引。
在SQLFiddle上可以找到演示。
当我执行链接测试时，持久化和索引的方法约快5倍。如果平均[Payload]非常大，则可能会显著增加。
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
我不完全清楚你想达到什么目的，但或许可以使用HASHBYTES来实现。
<>，你只选择了一个值，而很可能存在许多记录；我不确定是否会导致与 OP 所想的相同的情况。 - deroby@DevNull = field 上执行 SELECT 只是为了从测试环境中删除 SSMS 并从中接收和绘制结果网格。该变量将捕获找到的每个值，并最终得到与查询匹配的最后一行。因此，有许多记录与之匹配（50k 测试的 49950 行），但这应该是无关紧要的。这只是一种技术，以确保 SSMS 请求更多的内存用于结果网格不会偏向任何一种测试情况。 - Solomon RutzkyCOUNT()或者更好的CHECKSUM_AGG(BINARYCHECKSUM())会“强制”查询优化器实际读取所有值并返回一致的数字；从而使测试脚本具有显示每种方法返回相同结果的附加好处。 - deroby[Payload]字段中数据的行数和/或大小增加而增加。我并不是在说你的建议不是一种改进，只是它很可能是无关紧要的，因为即使没有索引和计算列，查询也非常快速，我们只能得出真正的问题还没有被识别，VARBINARY的东西只是一个转移注意力的东西/误导。 - Solomon Rutzky是否可以创建一个包含这前19个字节的额外二进制列？
如果可以，您可以将其创建为持久计算列，或者作为“真实”列，并创建一个 INSERT 触发器，在添加记录时填充它（如果记录被更新，则还需要一个 UPDATE 触发器）。
然后，您可以对新列进行索引，并重新编写查询以与该列进行比较，从而避免昂贵的子字符串操作。
VARBINARY。假设原始列中的所有值都为19个字节或更长，则BINARY(19)可能更好。 - Ed BVARBINARY(100)的表并在其上创建索引没有任何问题。我唯一能预见的问题是如果VARBINARY列的长度超过了允许的索引长度（900字节，如果我没记错的话）。 - Ed BPayload字段为VARBINARY(100)和VARBINARY(MAX)。对于VARBINARY(MAX)字段测试，首先测试仅数据类型的差异，因为初始数据集与VARBINARY(100)表中的数据相同（在这种情况下，数据仍在数据页上），然后将MAX表更新为将数据大小推送到每行14k字节（在这种情况下，数据现在移动到lob页面）。VARBINARY上进行过滤是任何查询缓慢的真正原因。即使最慢的方法也会在5000行上返回得太快，甚至无法注意到。
- 虽然已经提出了一些改善性能的好建议，但如果没有适当的测试，一些看起来不错的建议可能根本没有显示出任何改进。不应该对SQL Server的反应做出假设，也不需要做出假设，因为可以测试任何情况（尽管构造测试可能有点棘手）。我尝试了以下方法，在我的测试中，对于50万行数据，它非常快速！
重要的是：您需要拥有一个真正好的聚簇索引！聚簇索引应始终为：NUSE
我像这样创建了表：
CREATE TABLE [dbo].[YourTable]
(
[Id] INT IDENTITY(1,1),
[Payload] VARBINARY(MAX),
Prefix AS CAST(LEFT([Payload],19) AS BINARY(19)) PERSISTED
)
CREATE UNIQUE CLUSTERED INDEX idx1 ON dbo.YourTable(Id)
GO
CREATE NONCLUSTERED INDEX idex ON dbo.YourTable (Prefix)
GO
因此，前缀计算列是BINARY（19），始终为前19个字节。SQL Server需要执行的唯一操作是聚集索引扫描。
这不是一个严格的替代方案...更像是一种增强...
在负载字段上创建列存储索引
CREATE COLUMNSTORE INDEX payloadcsindex
ON yourdb.yourschema.yourtable ( payload )
听起来您想在列上执行LIKE。但是您不能这样做，因为LIKE只能用于varchar或nvarchar列。
让我们假设它是一个nvarchar(4000)列，并且您想查找以这个19字节模式开头的任何内容。以%结尾的like查询会通过计划优化成between查询。
例如，考虑以下查询：
select data FROM #a
where data like N'006%'
Start: [tempdb].[dbo].[#a].data >= Scalar Operator(N'006'), End: [tempdb].[dbo].[#a].data < Scalar Operator(N'007')
。
select data
from my_table
where data >= @searchPattern and data < (convert(varbinary,1)+@searchPattern)
我得出这个结论的方法是将约15000个文件加载到测试数据库中，并执行不同类型的查询（多次），得到以下结果：
这是一个创建测试表的SQL脚本:
CREATE TABLE [dbo].[Files]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Path] [varchar](260) NOT NULL,
[Prefix] [binary](19) NOT NULL,
[AllBytes] [varbinary](MAX) NOT NULL,
CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED ([Id] ASC)
)
现在是查询时间（请在不同的选项卡中运行以下内容）：
1.
--1. Equality check using a prefix column -> 191 ms
--WARNING!! Do not run this on a production server
--Clear SQL optimizations
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DECLARE @pattern BINARY(19) --NOTE use BINARY not VARBINARY to match the column type exactly
SET @pattern = 0x4D5A90000300000004000000FFFF0000B80000 --Start of EXE / DLL files
SELECT [Path]
FROM [dbo].[Files]
WHERE Prefix <> @pattern
--2. SUBSTRING -> 20448 ms = 107x slower
--WARNING!! Do not run this on a production server
--Clear Cache
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DECLARE @pattern BINARY(19)
SET @pattern = 0x4D5A90000300000004000000FFFF0000B80000 --Start of EXE / DLL files
SELECT [Path]
FROM [dbo].[Files]
WHERE SUBSTRING(AllBytes, 0, 19) <> @pattern
3.
-- 3. LEFT -> 34091 ms = 178x slower
--WARNING!! Do not run this on a production server
--Clear Cache
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DECLARE @pattern VARBINARY(19)
SET @pattern = 0x4D5A90000300000004000000FFFF0000B80000 --Start of EXE / DLL files
SELECT [Path]
FROM [dbo].[Files]
WHERE LEFT(AllBytes, 19) <> @pattern
现在，下面是一段创建测试数据的代码：
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SO
{
class Program
{
static void Main(string[] args)
{
//!! To keep this EXAMPLE as simple as possible I'm leaving off all error handling and taking other shortcuts I wouldn't normal advise.
const string sqlConnectionString = @"Data Source=yourServer;Initial Catalog=databaseName;Integrated Security=True";
const string folderToProcess = @"C:\Program Files"; //choose folder with your test files
const int prefixLength = 19;
using (var connection = new SqlConnection(sqlConnectionString))
{
connection.Open();
foreach (var filePath in Directory.GetFiles(folderToProcess, "*.*", SearchOption.AllDirectories))
{
Console.WriteLine(filePath);
//I'm only using ReadAllBytes here to keep this example simple.
//This isn't appropriate for large files because it'll load the entire file into memory
byte[] allBytes = File.ReadAllBytes(filePath);
//To keep things simple I'm assuming we're only working with binary values that are >= prefix length
if (allBytes.Length < prefixLength) { continue; }
//This can also be accomplished using SQL
byte[] prefix = new byte[prefixLength];
Array.Copy(allBytes, prefix, prefixLength);
//Rather use a stored procedure here
using (var command = new SqlCommand("INSERT INTO dbo.Files (Path, Prefix, AllBytes) Values(@path, @prefix, @allBytes)", connection))
{
command.Parameters.Add("@path", SqlDbType.NVarChar, 260).Value = filePath;
command.Parameters.Add("@prefix", SqlDbType.Binary, prefixLength).Value = prefix; //DataType is Binary!!
command.Parameters.Add("@allBytes", SqlDbType.VarBinary, allBytes.Length).Value = allBytes; //DataType is VarBinary
command.ExecuteNonQuery();
}
}
connection.Close();
}
Console.WriteLine("All Done !");
Console.ReadLine();
}
}
}
如果计算列不可用，这也是一种选择。
DECLARE @t TABLE (
val VARBINARY(MAX)
)
INSERT INTO @t SELECT 0x00000100000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00001000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00010000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00100000000000000000000000000000000000000000000000000000
INSERT INTO @t SELECT 0x00000f00000000000000000000000000000000000000000000000000
declare @pattern varbinary(19)
declare @pattern2 varbinary(19)
set @pattern = 0x0001
set @pattern2 = @pattern+0xFF
select @pattern,@pattern2
SELECT
*
FROM @t
WHERE val<@pattern
OR val>@pattern2
请提供更多的输入，否则这就是一道教科书式的问题。
请提供一些样本数据并告诉我们您的应用程序。有时候，应用程序的行为具有更广泛的解决方案。
我注意到您将其归类为SQL Server 2008，因此您应该：
打开页面压缩，禁用自动统计生成，查看sys.dm_os_wait_stats，并检查高度碎片化。
您正在读取哪种类型的记录需要进行<>比较？
是否有任何东西被分配到第二个内存流以进行处理？
您能否进行任何更改以防止数据写入磁盘？
处理此表需要多长时间？
WHERE CAST(r.payload as varbinary(19)) <> @pattern- Magnus@pattern声明为仅为VARBINARY本身，还是VARBINARY（19）？如果您没有指定最大长度，则可能会出现问题，即使不是问题的根本原因，因为在声明VARCHAR/NVARCHAR/VARBINARY变量时的默认长度只有1，例如，如果实际执行DECLARE @Pattern VARBINARY;，则会得到一个VARBINARY（1）变量。此外，虽然可能不是问题，但SUBSTRING的最小起始值为1，而不是0。 - Solomon RutzkySUBSTRING是正确的，而CAST是错误的。但实际上恰恰相反。通过使用0作为SUBSTRING的起始索引，您实际上是截掉了最后一个字符，得到了18个字节而不是19个字节。CAST（或CONVERT）正确地返回了19个字节。 - Solomon Rutzky