Identificadores autoincrementales en SQL SERVER
Patxi Echarte,
21. Abr, 2005,
15 Comentarios
.NET, Windows
A pesar de mis limitados conocimientos de SQL Server, creo que esta explicación sobre diversas características que afectan al tema de cómo disponer de identificadores autoincrementales en las tablas, puede ser de utilidad si te encuentras en algún punto muerto.
Es habitual al crear una tabla de base de datos necesitar que el identificador de la tabla sea un entero autoincremental. Para hacer esto en SQL SERVER se utiliza IDENTITY
. Con esto, definiendo una columna como int IDENTITY, haremos que cada vez que insertemos un registro se genere un nuevo identificador automáticamente.
Lo más habitual es querer que el primer identificador que se genere sea el 1 y que a partir de ahí, se generen identificadores en incrementos de 1, es decir, 2,3,4… Sin embargo existe la posibilidad de indicar ambos valores a la hora de definir la columna, así por ejemplo, int IDENTITY(5,10), generaría un primer identificador con valor de 5, y el resto con incrementos de 10, es decir, 15,25,35…
Es posible además, si en algún momento lo necesitamos, volver a poner el contador interno que lleva la numeración a su estado inicial, por ejemplo si hemos vaciado la tabla y queremos volver a introducir valores, que cojan identificadores inicialmente desde el 1. Una solución algo drástica, es borrar la tabla y volver a generarla, otra más sútil es utilizar la siguiente orden:
dbcc checkident (nombretabla,reseed,0) |
Para terminar con este tema, hay un problema en lo que respecta a la programación cuando utilizamos autoincrementales, ya que tras hacer un INSERT en la base de datos, muchas veces necesitamos obtener el identificador que se ha asignado al nuevo registro. Esto con otras bases de datos como MySQL es realmente sencillo, pero se complica algo en SQL SERVER, ya que el SGBD no nos lo devuelve de forma automática. Para ello hay que realizar una consulta tras la inserción, a continuación se muestra un código de ejemplo en C#.
// Insertamos un registro en una tabla de ejemplo de autores, que consta // de una columna con identificador autoincremental y un nombre. // Tras la inserción añadimos el sql que obtiene de SCOPE_IDENTITY // el último id insertado en la tabla de autores string query = "INSERT INTO autores(name) VALUES ('@autor');" + "SELECT @thisId=SCOPE_IDENTITY() FROM autores"; SqlConnection sqlConnection = new SqlConnection(Configuration.Instance.GetValue("ConnectionString")); SqlCommand sqlCommand = new SqlCommand(query, sqlConnection); // asigno el parámetro de entrada del autor sqlCommand.Parameters.Add("@autor", System.Data.SqlDbType.VarChar).Value = "Patxi"; // indico que la query tiene un parámetro de salida thisId de tipo int sqlCommand.Parameters.Add("@thisId", System.Data.SqlDbType.Int).Direction = ParameterDirection.Output; sqlConnection.Open(); try{ // Ejecutamos la query sqlCommand.ExecuteScalar(); // este es el identificador generado int id = (int)sqlCommand.Parameters["@thisId"].Value; } catch(Exception exc){ throw new Exception("Error al insertar datos", exc); } finally{ sqlConnection.Close(); } |
Espero que sea de utilidad, si eres un experto en SQL Server o crees que hay algo incorrecto, o que se podría hacer mejor de otra forma utiliza los comentarios para compartirlo, todos te lo agradeceremos.
Otra alternativa a usar SCOPE_IDENTITY() (que a veces me ha fallado) sería usar algo como selec max(id_autor) from autores ¿verdad?
Depende de la situación, si puede haber múltiples conexiones a la base de datos, podría suceder que tú insertaras un registro en la tabla y antes de realizar la consulta de obtención del máximo, otro usuario provocase otra inserción, con lo que al hacer la consulta del máximo ambos obtendríais el mismo identificador. El SCOPE_IDENTITY devuelve el último identificador generado para la sesión actual, por lo que en el ejemplo descrito cada usuario obtendría su propio identificador. Si no te funciona en alguna situación puede deberse a que estés usando transacciones o haciendo inserts en varias tablas, hay un par de opciones más que puedes manejar, que son IDENT_CURRENT y @IDENTITY, tienes una explicación en http://www.daveranck.com/SoftwareDev/DevArticles/287.aspx
Les agradezco mucho por estos 2 consejos. Me fueron muy utiles!!
Muy buen trabajo!
Yo necesito extraer cual fue el ultimo identificador generado, de una insercion que yo misma acabo de hacer en la misma sesion y he utilizado:
– Select @@IDENTITY as Ident from treporte
– select SCOPE_IDENTITY() as Ident from treporte
– select IDENT_CURRENT(numreporte) from treporte
Y me devuelven NULL
Claro que si pido: select max(numreporte) as Ident from treporte, si me muestra el valor correcto, pero yo voy a permitir usuarios concurrentes conectados y no debo usar el max(column).
Sabran que me esta pasando??. Si me dan una pista
Hola Nairoby, a mi me pasa lo mismo en una tabla que me devuelve Null al intentar recuperar el valor de la última inserción, pero la diferencia es que yo estoy haciendolo en una tabla que está replicada en otro servidor. Es una replicación de mezcla, y cuando lo hago en el otro servidor si me dá el identity, pero al hacerlo en el remoto, solo me devuelve nulos.
si alguien sabe por qué pasa esto, se lo agredecería.
hola…. tengo un problema con el SQL quiero recuperar el ultimo valor de una query pero no con el MAX, nose si hay algun comando para que me devuelva el ultimo valor de una busqueda.
Saludos
alguno sabe si existe una función como insert_id() para mysql?
Grax!
hola….quiero resivir informacion sobre campos autoincrementales en SQL…
y en el cual tambien ejemplos programados….
gracias…..
Espero que te sirva el ejemplo que hay en esta misma página, Melber. Puedes encontrar multitud de información más buscando en Google, que es lo más fácil y barato, quizás incluso encuentres una página con todo lo que buscas… porque si lo que pretendes es que alguien haga la recopilación por ti, eso se llama consultoría y se cobra 😉
Tengo una tabla cuyo id es identity hubo un error en el log de transacciones ys e realizo un intento de insercion de 100 registros como fue erroneo se quedo intentandolo una y otra vez hasta que el ultimo identity se fue de 22500 a 160900 como puedo hacer para que retorne al valor de 22501 al insertar un nuevo registro
En teoría con el siguiente comando debería funcionarte:
dbcc checkident (nombretabla,reseed,22501)
Muy util la informacion. Gracias a todos.
hola ya utilise el ejemplo para obtener el id que se ha insetado pero deseo tomar ese valor para pasarlo a in parametro el cual sera llamado para ser insertado en otra tabla pero me marca el siguiente error
Mensaje de error del compilador: CS0103: El nombre ‘id’ no existe en el contexto actual
tengo el siguiente codigo
DATOS1.Parameters.Add(new SqlParameter(«@id»,SqlDbType.Int));
Línea 96: DATOS1.Parameters[«@id»].Value =id.Parameters.Int;
la duda es por que si fuerea un texbox por ejemplo seria
DATOS3.Parameters.Add(new SqlParameter(«@ACTIVIDADES», SqlDbType.VarChar, 5000));
DATOS3.Parameters[«@ACTIVIDADES»].Value =ACTIVIDADES.Text;
suponiendo que mi texbox se llame ACTIVIDADES ME URGE LA AYUDA GRACIAS DE ANTEMANO
gracias! me fue de mucha ayuda!
saludos
me gustaria conocer mucho sobre campo incrementable en sql server