SQL Server – Optimizing a UNIQUE CONSTRAINT to Make SELECT Faster

clustered-primary-keydatabase-designoptimizationsql serverunique-constraint

I have a database on SQL Server 2012 SP3 with this table:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

Using Sql Server Management Studio Activity Monitor I have seen that the following select has a 32ms average duration.

set @maxCode = (select max(Serial) from Code where AggregationLevelId = @codeLevel);

Is there any way to improve it? I asked this because I don't know if I can add a new index to a column that has a Unique Constraint.

UPDATE:

Code table has also this index:

CREATE NONCLUSTERED INDEX [ix255] ON [dbo].[Code]
(
    [AggregationLevelId] ASC
)
INCLUDE (   [Serial],
    [CodeId]) 
WHERE ([CommissioningFlag]=(255))

Best Answer

For the best performance of your query the table should have an index on (AggregationLevelId, Serial). One index on two columns. The order of columns in the index is important. Since Serial is unique, this two-column index can and should be declared unique as well.

CREATE UNIQUE NONCLUSTERED INDEX [IX_AggregationLevelId_Serial] ON [dbo].[Code]
(
    [AggregationLevelId] ASC,
    [Serial] ASC
)

You can create such index in addition to existing indexes and constraints. There is no limitation. It will affect the performance of inserts and updates as any other extra index.

Most likely the optimiser would use a single seek in such index for your query, but occasionally I came across a situation when the query with MAX didn't perform well even with the proper supporting index. (I vaguely remember that it happened when the table had no rows for the given AggregationLevelId).

So, I personally use the equivalent variant of the query:

SET @maxCode = 
(
    SELECT TOP(1) Serial
    FROM Code
    WHERE AggregationLevelId = @codeLevel
    ORDER BY Serial DESC
);

Optimiser should be smart enough to use the index with Serial ASC, even though the query orders by Serial DESC.

Related Question