How to generate an auto-incrementing ID number that resets yearly

Question

I have an application that generates student ID based on year, like the first ID: 202100001, then 202100002 and so on. Basically takes the year and start incrementing the counter.

That's good and easy, but my problem because it taking the last ID from database to generate the student ID. When there is a new year, the year part changes but it doesn't reset to zero. I would like in 2022 to start at: 202200001, then 202200002 and so on. Is there an easier way to reset the counter, should I add a new column for this?

This is my current code:

//Generate Student Number
string StudentNumber;
private void GenerateStudentNumber()
{
    DateTime moment = DateTime.Now;
    string year = moment.Year.ToString();
    try
    {
        StudentNumber = GenerateID();               
        txtStudentNumber.Text = year + StudentNumber;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

private string GenerateID()
{    
    string value = "00";
    int IDindex = 0;
    try
    {        
        using (con = new SqlConnection(databaseConnectionString))
        {
            // Fetch the latest ID from the database
            con.Open();
            cmd = new SqlCommand("SELECT TOP 1 StudentID FROM Students order BY StudentID DESC", con);
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (rdr.HasRows)
            {
                rdr.Read();
                IDindex = Convert.ToInt16(rdr["StudentID"]);
            }
            rdr.Close();
        }
            
        IDindex++;
        // Because incrementing a string with an integer removes 0's
        // we need to replace them. If necessary.
        if (IDindex <= 9)
        {
            value = "00" + value + IDindex.ToString();
        }
        else if (IDindex <= 99)
        {
            value = "0" + value + IDindex.ToString();
        }
        else if (IDindex <= 999)
        {
            //value = "00" + value + IDindex.ToString();
            value = value + IDindex.ToString();
        }
        else if (IDindex <= 9999)
        {
            value = "0" + IDindex.ToString();
        }        
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    return value;
}

Here is how my SQL table:

CREATE TABLE [dbo].[Students] (
    [StudentID]          INT             IDENTITY (1, 1) NOT NULL,
    [StudentNumber]      NVARCHAR (50)   NOT NULL,
);

Thank you very much.

Answer 1

My recommendation is to set up a sequence:

 CREATE SEQUENCE StudentSequenceWithinYear;

Once a year, you can run a job to reset the value:

ALTER SEQUENCE StudentSequenceWithinYear RESTART WITH 1 ;  

You can set this up automatically to run around midnight on Dec 31st/Jan 1st.

Then in the CREATE TABLE statement, you can use:

CREATE TABLE Students (
    StudentID  INT IDENTITY (1, 1) NOT NULL,
    StudentNumber NVARCHAR (50) NOT NULL,
    Year INT DEFAULT YEAR(GETDATE()),
    WithinYearSequence INT DEFAULT NEXT VALUE FOR StudentSequenceWithinYear,
    MyStudentId AS (CONCAT(Year, FORMAT(WithinYearSequence, '00000'))) 
);

All that said, I think you should just use an identity column for identifying students. Trying to store information within an id usually causes problems with edge cases (a student who drops out one year but returns another, for instance). You can always look up the year in your database, so you don't need to store that as part of the id.