Issues With Increment Ms-sql, C#

I am having an issue with the increment for the ID. The ID would increase by one every time I click insert, but the problem occurs when the ID 2, it would insert the values twice,

Solution 1:

Problem is with your query since you are getting COUNT(S_ID) which is going to get you count of records doesn't necessarily will give exact ID number. You should rather try MAX(S_ID) or ORDER BY clause saying

SelectMAX(S_ID) from Student_Name 

(OR)

Select TOP 1 S_ID from Student_Name ORDERBY S_ID DESC; 

But recommended, You should actually go with SQL Server @@IDENTITY or SCOPE_IDENTITY() to get the last inserted record ID (assuming that S_ID is an IDENTITY column)

Solution 2:

It's highly recommended to not use max or top in order to determine the "next" identifier to use, simply because of the cost associated with it.

However, there are some other pitfalls to using max and top especially if there is a chance that nolock is used (which is a whole other conversation). I've seen a lot of web applications use max and has proven to be a performance killer.

Rahul is right, @@identity or scope_identity are good alternatives. However, I think this calls for using a native SQL Server sequence, which was introduced in SQL Server 2012. It was something that application developers have been waiting for and Microsoft finally delivered.

The issue with using @@identity or scope_identity is that you actually have to write rows to some table before you can even contemplate doing something.

This makes it a bit more costly and messier than what it may need to be. In the case of using a sequence, you can issue a new sequence number and then decide what to do and once you decide what to do you're still guaranteed that you're the only one with that sequence number.

You would create a sequence like this. You should check out the documentation as well.

create sequence dbo.StudentIdSeq
    asint -- this can be any integer type
    start with1 -- you can start withany valid number in the int, even negative
    increment by 1;
go

Then you issue new sequence numbers by doing this ...

selectnext value for StudentIdSeq;

It may still be good to create a stored procedure with an output parameter that you can call from C# (which is what I would do). In fact you may want to take it a step further, in the case that you have a bunch of sequences, and create a slick stored procedure that will get a new sequence based on the type that is being requested from the caller.