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