Optimize a UNIQUE CONSTRAINT to make SELECT faster
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.