SCOPE_IDENTITY() returns thousand number

Question

I have an insert query like the below. However, scopeIdentity does not return 42, it returns 1042.

This is the SQL Server table:

My code:

int masterId = 0;

using (SqlConnection cmd = new SqlConnection(connString))
{
    using (SqlCommand conn = cmd.CreateCommand())
    {
        conn.CommandText = "INSERT INTO[MasterReportData]([ReportName],[CaseList],[EmployeeId],[datetime]) VALUES(@reportName, @caseList, @employeeId, @datetime) SET @ID = SCOPE_IDENTITY()";

        conn.Parameters.Add("@reportName", SqlDbType.VarChar).Value = reportName;
        conn.Parameters.Add("@caseList", SqlDbType.VarChar).Value = caseList;
        conn.Parameters.Add("@employeeId", SqlDbType.Char).Value = employeeId;
        conn.Parameters.Add("@datetime", SqlDbType.DateTime).Value = datetime;
        conn.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;

        cmd.Open();
        conn.ExecuteNonQuery();

        masterId = Convert.ToInt32(conn.Parameters["@ID"].Value);
        cmd.Close();
    }
}

Answer 1

Have a look at https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql

The description says: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

In words: it Returns the last id and not the next to use. Therefore you can't use the INSERT command like that. What you can do is:

Configure your ID as auto-increment id. Then run the INSERT command and run SELECT SCOPE_IDENTITY() afterwards to find out which ID was used.