{"id":329,"date":"2017-05-30T05:20:34","date_gmt":"2017-05-30T05:20:34","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=329"},"modified":"2021-01-02T23:35:03","modified_gmt":"2021-01-02T23:35:03","slug":"uso-de-identity","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=329","title":{"rendered":"Identity en SQL Server &#8211; ejercicio"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_D1.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_D1.png\" alt=\"\" class=\"wp-image-2436\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Uso Identity<\/strong><\/h2>\n\n\n\n<p>Identity es una propiedad que permite que un campo en&nbsp;una tabla incremente su valor de manera autom\u00e1tica al insertar los registros en ella. Para el uso de la propiedad Identity el tipo de dato debe ser entero Int. Es necesario definir un valor inicial y un valor de incremento.<br>Es importante anotar que Identity no asegura la unicidad de valor, esta \u00fanicamente es posible con la restricciones Primary key, Unique o con el \u00edndice Unique.&nbsp;Solamente puede existir una columna por tabla con la propiedad Identidad.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejemplo<\/strong><\/p>\n\n\n\n<p>En el ejemplo, se crea una tabla con la propiedad Identity.<br>Se insertan registros, luego se eliminan, y si fuera<br>posible siempre se debe tener en cuenta el valor de Identity.<\/p>\n\n\n\n<p>Create table Personas<br>(<br>Codigo int Primary key not null identity(1,1) ,<br>NombreCompleto nvarchar(200)<br>)<br>go<\/p>\n\n\n\n<p><strong>Insertar registros<\/strong><br>insert into Personas (NombreCompleto)<br>values (&#8216;Arturo P\u00e9rez&#8217;),(&#8216;Fernando S\u00e1nchez&#8217;),(&#8216;Antonio R\u00edos&#8217;),(&#8216;Jos\u00e9 Campos&#8217;)<br>go<br><strong>Listar los registros.<\/strong><br>select * from Personas<br>go<br>Los registros anteriores toman los valores de Identidad del 1 al 4.<\/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_Identity_01.png\" alt=\"\" class=\"wp-image-1390\" width=\"667\" height=\"330\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_01.png 418w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_01-300x149.png 300w\" sizes=\"auto, (max-width: 667px) 100vw, 667px\" \/><\/figure><\/div>\n\n\n\n<p>Para ver el valor de Indentity. Reporta Null si no se ha ingresado registros con Identidad.<br>select @@IDENTITY<br>go<\/p>\n\n\n\n<p><strong>Si insertamos dos ser\u00edan los n\u00fameros 5 y 6.<\/strong><br>insert into Personas (NombreCompleto)<br>values (&#8216;Rosa Alc\u00e1ntara&#8217;),(&#8216;Yolanda Mej\u00eda&#8217;)<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_01A.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_01A.png\" alt=\"\" class=\"wp-image-2433\" width=\"627\" height=\"486\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_01A.png 370w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2021\/01\/ManualSQL_Identity_01A-300x233.png 300w\" sizes=\"auto, (max-width: 627px) 100vw, 627px\" \/><\/a><\/figure><\/div>\n\n\n\n<p><strong>Si se eliminan f\u00edsicamente los registros 5 y 6\u00a0quedar\u00edan del 1 al 4, al insertar uno nuevo<\/strong><br><strong>\u00bfqu\u00e9 c\u00f3digo deber\u00eda tener?<\/strong><br>delete Personas where Codigo in (5,6)<br>go<\/p>\n\n\n\n<p><strong>Ahora quedar s\u00f3lo del 1 al 4<\/strong><br><strong>Agregar un registro que supuestamente deber\u00eda ser el n\u00famero 5<\/strong><br>insert into Personas (NombreCompleto)<br>values (&#8216;Pedro Picapiedra&#8217;)<br>go<br>Al insertar el registro, se creo con el valor de 7, que era<br>el Identity que deber\u00eda seguir.<\/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_Identity_02.png\" alt=\"\" class=\"wp-image-1391\" width=\"652\" height=\"375\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_02.png 428w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_02-300x172.png 300w\" sizes=\"auto, (max-width: 652px) 100vw, 652px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Corregir, antes de agregar, se debe especificar el valor de Identity en 4<\/strong><br><strong>Borrar el registro primero<\/strong><br>delete Personas where Codigo = 7<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usar DBCC CheckIdent<\/strong><br>DBCC CHECKIDENT (Tabla, RESEED ,Valor)<\/p>\n\n\n\n<p><strong>Para regresar el valor de Identity a 4<\/strong><br>dbcc checkident (Personas, Reseed, 4)<br>go<\/p>\n\n\n\n<p><strong>&nbsp;Ahora insertar el Quinto registro.<\/strong><br>insert into Personas (NombreCompleto)<br>values (&#8216;Pedro Picapiedra&#8217;)<br>go<\/p>\n\n\n\n<p><strong>El listado se muestra<\/strong><br>select * from Personas<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_Identity_03.png\" alt=\"\" class=\"wp-image-1392\" width=\"692\" height=\"402\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_03.png 465w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Identity_03-300x174.png 300w\" sizes=\"auto, (max-width: 692px) 100vw, 692px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Si se borran todos los registros<\/strong><br>delete Personas<br>dbcc checkident (Personas, Reseed, 0)<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Uso Identity Identity es una propiedad que permite que un campo en&nbsp;una tabla incremente su valor de manera autom\u00e1tica al insertar los registros en ella. Para el uso de la propiedad Identity el tipo de dato debe ser entero Int. Es necesario definir un valor inicial y un valor de incremento.Es importante anotar que Identity &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=329\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2436,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[53],"class_list":["post-329","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-registros-vistas","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/329","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=329"}],"version-history":[{"count":9,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/329\/revisions"}],"predecessor-version":[{"id":2445,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/329\/revisions\/2445"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2436"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}