2

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))
3

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.

2
  • Is there any reason why you did not create the index with Serial DESC? – Lennart Oct 4 '16 at 8:55
  • @Lennart, not really. There should not be any difference in this case, so I prefer to use default ASC option. An index may be used by many different queries and usually the sort order of the index doesn't matter. I find it easier to maintain the database as it evolves when most indices follow the same style. If I use a non-default DESC variant there must be a compelling reason, it should be documented, etc. – Vladimir Baranov Oct 4 '16 at 9:27

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.