Menu
Is it possible in SQL (SQL Server) to retrieve the next ID (integer) from an identity column in a table before, and without actually, inserting a row? This is not necessarily the highest ID plus 1 if the most recent row was deleted.
I ask this because we occassionally have to update a live DB with new rows. The ID of the row is used in our code (e.g. Switch (ID){ Case ID: } and must be the same. If our development DB and live DB get out of sync, it would be nice to predict a row ID in advance before deployment.
I could of course SET IDENTITY OFF SET INSERT_IDENTITY ON or run a transaction (does this roll back the ID?) etc but wondered if there was a function that returned the next ID (without incrementing it).
816
The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
You cannot reliably find out the next identity value - until you've actually inserted a new row into the table. Stop trying - you won't succeed - just accept the fact you cannot know the identity value before the row is actually inserted into the table and SQL Server has assigned the value.
We use the IDENT_CURRENT function to return the last IDENTITY value generated for a specified table under any connection. It does not consider the scope of the SQL query that generates identity value. We need to specify the table for which we want to check the identity value.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use. IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope.
try IDENT_CURRENT:
Select IDENT_CURRENT('yourtablename')
This works even if you haven't inserted any rows in the current session:
Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
