I have a table with a varchar
field than I need to be unique, but it is too long for the nonclustered index that the uniqueness constraint requires, so I have added a computed column (either using LEFT
or HASHBYTES
) and based the uniqueness constraint on that.
CREATE TABLE [dbo].[TableWithLongField] (
[Customer] [char](10) NOT NULL,
[PrimaryKey] [varchar](88) NOT NULL,
[LongField] [varchar](4096) NOT NULL,
[LongFieldLeft] AS CONVERT(varchar(900), LEFT([LongField], 900)),
CONSTRAINT [UQ_LongFieldLeft] UNIQUE ([LongFieldLeft]),
CONSTRAINT [PK_PrimaryKey] PRIMARY KEY NONCLUSTERED ([PrimaryKey]),
INDEX [IX_Customer] CLUSTERED ([Customer])
);
When I perform a query like
DELETE
FROM dbo.TableWithLongField
WHERE
PrimaryKey <> @PrimaryKey AND
LongField = @LongField
I had hoped that SQL Server would be intelligent enough to realise it has an index based on the field available and automatically use it. However, that doesn't seem to be the case, when I look at the execution plan or at the user_seeks
on the index. I wondered if it was just calculating that the function would cost more than the whole-table scan it is currently performing, but I've added a few tens of thousands of rows and it hasn't made any difference.
It does use the index if I manually perform the same calculation in the query
DELETE
FROM dbo.TableWithLongField
WHERE
PrimaryKey <> @PrimaryKey AND
LongFieldLeft = LEFT(@LongField, 900)
LongField = @LongField
but having to define the function in two places already seems bad, and will only get worse as I write more queries.
Am I expecting too much from SQL Server and my only option is to do this manually? Or is there something I can do to hint that the index is available for querying on the long field, without adding it explicitly to every query?