{"id":381,"date":"2017-06-06T00:50:53","date_gmt":"2017-06-06T00:50:53","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=381"},"modified":"2020-05-20T22:35:14","modified_gmt":"2020-05-20T22:35:14","slug":"identity-caso-practico","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=381","title":{"rendered":"Uso de Identity &#8211; Caso pr\u00e1ctico"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"250\" height=\"257\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCasoD.png\" alt=\"\" class=\"wp-image-1375\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Propiedad Identity &#8211; Caso<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>La propiedad Identity puede ser asignada a un solo campo en la tabla, generalmente se usa para saber la cantidad de registros insertados, o para ayudar a construir un c\u00f3digo.<\/li><li>No es recomendable usarla como columna de c\u00f3digo ya que los c\u00f3digos generalmente son datos de tipo caracter.<\/li><li>Se recomienda usar para los c\u00f3digos de las tablas campos de tipo nchar.<\/li><\/ul>\n\n\n\n<!--more-->\n\n\n\n<p>En este ejemplo se muestra como trabajar con la propiedad Identity y al borrar los \u00faltimos registros resetear la propiedad para que los siguientes registros insertados tengan el valor correcto correlativo. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=329\">Ver Uso de Identity<\/a>)<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Crear la base de datos<\/strong><\/p>\n\n\n\n<p>Create database SistemaER<br>go<br>use SistemaER<br>go<\/p>\n\n\n\n<p><strong>&#8212; Tabla con campo Identity<\/strong><br>Create table Datos<br>(<br>DatosCodigo int Identity(1,1) primary key not null,<br>DatosDescripcion nvarchar(50),<br>DatosPrecio Numeric(9,2)<br>)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Insertar registros<\/strong><br>insert into Datos (DatosDescripcion, DatosPrecio)<br>values (&#8216;Casaca&#8217;,180),(&#8216;Pantal\u00f3n&#8217;,800),(&#8216;Sombrero&#8217;,250)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Los c\u00f3digo de los registros insertados son: 1,2 y 3<\/strong><br>select * from Datos<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_01.png\" alt=\"\" class=\"wp-image-1370\" width=\"644\" height=\"269\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_01.png 502w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_01-300x125.png 300w\" sizes=\"auto, (max-width: 644px) 100vw, 644px\" \/><\/figure><\/div>\n\n\n\n<p><strong>&#8212; Al insertar no se especifica el campo Identidad<\/strong><\/p>\n\n\n\n<p>insert into Datos (DatosCodigo, DatosDescripcion, DatosPrecio)<br>values (4,&#8217;Corbata&#8217;,60)<br>go<br><strong>&nbsp;Mens. 544, Nivel 16, Estado 1, L\u00ednea 1<\/strong><br><strong>No se puede insertar un valor expl\u00edcito en la columna de identidad &nbsp;de la tabla &#8216;Datos&#8217; cuando IDENTITY_INSERT es OFF.&nbsp;<\/strong><\/p>\n\n\n\n<p><strong>&#8212; Insertar dos registro mas<\/strong><\/p>\n\n\n\n<p>insert into Datos ( DatosPrecio, DatosDescripcion)<br>values (390, &#8216;Zapatos&#8217;),(120,&#8217;Correo&#8217;)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Los dos nuevos registros toman los c\u00f3digos 4 y 5<\/strong><br>select * from Datos<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_02.png\" alt=\"\" class=\"wp-image-1371\" width=\"658\" height=\"364\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_02.png 510w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_02-300x166.png 300w\" sizes=\"auto, (max-width: 658px) 100vw, 658px\" \/><\/figure><\/div>\n\n\n\n<p><strong>&#8212; Borrar f\u00edsicamente los registros 3, 4 y 5 (<a href=\"http:\/\/www.manualsqlserver.com\/?p=303\">Ver Eliminar registros<\/a>)<\/strong><\/p>\n\n\n\n<p>delete Datos where DatosCodigo in (3,4,5)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Quedaron solamente los registro 1 y 2, si se inserta un nuevo registro&nbsp;<\/strong><strong>deber\u00eda tomar el n\u00famero 3, el valor de la identidad qued\u00f3 en CINCO (Ver la propiedad con @@Identity)<\/strong><\/p>\n\n\n\n<p>select @@IDENTITY<br>go<\/p>\n\n\n\n<p><strong>El valor de identity debe corregirse, insertamos un registro sin la correcci\u00f3n<\/strong><\/p>\n\n\n\n<p>insert into Datos ( DatosPrecio, DatosDescripcion)<br>values (1500, &#8216;Reloj&#8217;)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Listar los registros<\/strong><\/p>\n\n\n\n<p>select * from Datos<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_03.png\" alt=\"\" class=\"wp-image-1372\" width=\"644\" height=\"257\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_03.png 517w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_03-300x120.png 300w\" sizes=\"auto, (max-width: 644px) 100vw, 644px\" \/><\/figure><\/div>\n\n\n\n<p><strong>El c\u00f3digo del registro insertado deber\u00eda ser TRES<\/strong><\/p>\n\n\n\n<p><strong>Primero: borrar el registro con el identidad incorrecto (Registro 6)<\/strong><\/p>\n\n\n\n<p>delete Datos where DatosCodigo = 6<br>go<br><strong>&#8212; El valor de Identity deber\u00eda ser 2, para que al insertar el nuevo registro sea el n\u00famero 3<\/strong><br>DBCC CheckIdent (Datos, Reseed, 2)<br>go<\/p>\n\n\n\n<p><strong>Se inserta ahora el Reloj&#8230; que debe ser el n\u00famero 3<\/strong><\/p>\n\n\n\n<p>insert into Datos ( DatosPrecio, DatosDescripcion)<br>values (1500, &#8216;Reloj&#8217;)<br>go<br><strong>&#8212; Listado<\/strong><br>select * from Datos<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_04.png\" alt=\"\" class=\"wp-image-1373\" width=\"697\" height=\"290\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_04.png 510w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_04-300x125.png 300w\" sizes=\"auto, (max-width: 697px) 100vw, 697px\" \/><\/figure><\/div>\n\n\n\n<p><strong>&#8212; Si se eliminan todos los registros f\u00edsicamente<\/strong><\/p>\n\n\n\n<p>delete Datos<br>go<br>DBCC CheckIdent (Datos, Reseed, 0)<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>&#8212; Uso de Identidad con valores inicial e incremento diferente de 1. <\/strong><br><strong>Tabla Apuestas<\/strong><\/p>\n\n\n\n<p>Create table Apuestas<br>(<br>ApuestasCodigo int Identity(10,5) primary key not null,<br>ApuestasDescripcion nvarchar(50),<br>ApuestasPrecio Numeric(9,2)<br>)<br>go<\/p>\n\n\n\n<p><strong>&#8212; Insertar registros<\/strong><\/p>\n\n\n\n<p>insert into Apuestas ( ApuestasPrecio, ApuestasDescripcion)<br>values (1500, &#8216;Ganador Premio&#8217;),(50, &#8216;Empate&#8217;),(-60, &#8216;Perdedor&#8217;)<br>go<br><strong>&#8212; Los c\u00f3digos son:<\/strong><br>select * from Apuestas<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_05.png\" alt=\"\" class=\"wp-image-1374\" width=\"754\" height=\"329\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_05.png 569w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IdentityCaso_05-300x131.png 300w\" sizes=\"auto, (max-width: 754px) 100vw, 754px\" \/><\/figure><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Propiedad Identity &#8211; Caso La propiedad Identity puede ser asignada a un solo campo en la tabla, generalmente se usa para saber la cantidad de registros insertados, o para ayudar a construir un c\u00f3digo. No es recomendable usarla como columna de c\u00f3digo ya que los c\u00f3digos generalmente son datos de tipo caracter. Se recomienda usar &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=381\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1375,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[20,115,114,53],"class_list":["post-381","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-registros-vistas","tag-create-table","tag-dbcc","tag-identity","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=381"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/381\/revisions"}],"predecessor-version":[{"id":1377,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/381\/revisions\/1377"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1375"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}