This potentially might be too large of a question for a complete solution, and I've got a bit of a strange set up. I'm using HP OO to create a text-based RPG just to practice getting used to database design on this platform.
So it's basically a flow script that runs once. When the script starts, a player (user) is created, and then a character is created. The player inputs a name for its character, and this is stored in the
SELECT name FROM character WHERE character.character_id=x
There's nothing guaranteeing that the highest value in an identity column is the most recently created record. You should add a
date_created column to your table and give it a default value of the current date and time (
current_timestamp for a
datetime2 field). That actually does what you want.
OK, your question changed a bit and, Tab's comment here is also correct. If you want to insert and get the identity inserted back, you should follow the advice here that he linked.
However, if you want to be able to determine the order of creation -- which is what you originally asked -- then you should use a
date_created field. It's possible to get around
IDENTITY and insert any value you want, and things like UPDATEs and DELETEs can change things as well. Essentially, it's a bad idea to assign meaning to a record's value of an IDENTITY column relative to other records in the table (i.e., this was created before or after these other records) because you can actually get around that.
Personally, I would either use the
OUTPUT clause to have my INSERTs send the ID back:
INSERT INTO Character (...) OUTPUT INSERTED.Id VALUES (....);
Or I'd reuse the same connection and return the
INSERT INTO Character (...) VALUES (....); SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];