Hello everyone diculpen by this question may be something unusual or absurd, but I am looking for the autoincrementable value of a table.
In MySQL it's this
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'nombre_db' AND TABLE_NAME = 'nombre_tabla'
I made this way select MAX (ID_Cat) + 1 from category but when the table does not truncate funciana the query.
Is there any way to get SQL Server?
With IDENT_CURRENT you can get the last identity generated for a table, NULL if an identiy was never generated or if the table was truncated.
You also have the functions IDENT_SEED e IDENT_INCREMENT that return the initial value and the increment respectively.
SELECT IDENT_CURRENT('esquema.tabla') as IdActual,
IDENT_SEED('esquema.tabla') as IdInicial,
IDENT_INCR('esquema.tabla') as Incremento;
Similarly, if what you need is to obtain the ID value of a newly inserted record, it is convenient to use SCOPE_IDENTITY to obtain said value.
DECLARE @UltimoID INT
INSERT INTO esquema.tabla(col1, col2) VALUES (10, 'algo');
SELECT @UltimoID = SCOPE_IDENTITY();
In @UltimoID the ID of the newly inserted record remains. Note that SCOPE_IDENTITY returns the last identity generated within the scope where it was called, therefore the table is not specified.
Try using the function SCOPE_IDENTITY() that within SQL Server is used to obtain the last value inserted in the same context that is being used
Then your query should look like this:
If you have 10 records in your table, the following query will show you the number 10
SELECT SCOPE_IDENTITY()
If, on the contrary, in your tables you have 10 records and you put at the end +1 then you will get the next id available
SELECT SCOPE_IDENTITY()+1