2

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?

  • what happen when you create LongFieldLeft as persisted ? – KumarHarsh Oct 22 at 10:20
  • @KumarHarsh sadly doesn't look like it helps. I think the fact it's used in a index means it gets persisted anyway. – Rawling Oct 22 at 11:16
  • Yes. By indexing it you’re persisting it anyway. You don’t need it persisted in the clustered index. – Rob Farley Oct 22 at 20:25
2

You’re basically adding a helper-predicate, and that’s not something the SQL engine will do out of the box most of the time. It does for LIKE, in that if you have WHERE col LIKE 'A%', then the query plan will show that it can seek on col >= 'A' AND col < 'b', but most others don’t get picked up.

It’s not a ton of extra work, because it worked out the column value when it populated the index, and the parameter only needs to be shortened once. But I get that you might have a lot of queries to change. Sadly, it’s probably just what you have to do.

  • Thanks, Rob. I looked up "helper predicate" and that led me onto sargability and I'm comfortable now that I'm just expecting SQL to be too clever and that I'll need to do it myself. – Rawling Oct 22 at 14:32
  • 1
    You may have come across articles and presentations of mine then. I do keep asking people at Microsoft to improve the engine by looking for useful helper predicates, but it hasn’t happened yet. – Rob Farley Oct 22 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.