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.
Code table has also this index:
CREATE NONCLUSTERED INDEX [ix255] ON [dbo].[Code] ( [AggregationLevelId] ASC ) INCLUDE ( [Serial], [CodeId]) WHERE ([CommissioningFlag]=(255))