First of all, I would recommend using a surrogate key, see
Surrogate key - Wikipedia[
^]. Typically the purpose of the key is to uniquely identify the record, nothing more. Unless there's a good reason to use a key that is derived from the data avoiding it would make life much easier.
Having that said if really you need to use a key like you described, why not split the field into two, store the year separately from the running id. In other words
CREATE TABLE [dbo].[Students] (
[StudentIDYear] INT NOT NULL,
[StudentIDNo] INT NOT NULL,
[StudentNumber] NVARCHAR (50) NOT NULL
);
This way it would be easy to query the highest
StudentIDNo
for current year and decide what values you want to use for the new record.
But as said, looking at your table I would prefer the identity field. Also if the
StudentNumber
is really a number, you should store it in an integer field, not in character field.