{"id":1666,"date":"2020-05-24T15:54:48","date_gmt":"2020-05-24T15:54:48","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1666"},"modified":"2020-05-24T15:54:48","modified_gmt":"2020-05-24T15:54:48","slug":"uso-de-include-en-indices-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1666","title":{"rendered":"Uso de Include en indices SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"200\" height=\"216\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_en_Indices__D.png\" alt=\"\" class=\"wp-image-1667\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Uso de Include en Indices SQL Server<\/h2>\n\n\n\n<p>El uso de include en los \u00edndices permite ampliar la funcionalidad de los \u00edndices no agrupados. Para m\u00e1s informaci\u00f3n de \u00edndices <a href=\"https:\/\/manualsqlserver.com\/?p=158\">Ver Indices en SQL Server.<\/a><\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"has-medium-font-size\"><strong>Beneficios del uso de Include<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Se pueden usar tipos de datos no permitidos en \u00edndices<\/li><li>No son considerados como una columna mas en la cantidad de columnas del \u00edndice o en el tama\u00f1o del \u00edndice. La cantidad de columnas m\u00e1xima en un \u00edndice es 32.<\/li><li>Recuerde que el tama\u00f1o m\u00e1ximo del \u00edndice es de 1,700 bytes.<\/li><li>Aumenta la efectividad de las consultas<\/li><\/ul>\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 class=\"has-medium-font-size\"><strong>Limitaciones<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Se usan en s\u00f3lo \u00edndices no agrupados<\/li><li>Se pueden usar todos los tipos de datos excepto text, ntext, and image<\/li><li>No se pueden eliminar las columnas incluidas con Include salvo que se elimine primero el \u00edndice.<\/li><\/ul>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Crear un \u00edndice para la tabla productos por la descripci\u00f3n, incluir<br>los campos Unidad y Precio<\/strong><br>Create index ProductosDescripcionIDXinUnidadPrecio on<br>Products (ProductName)<br>Include ([QuantityPerUnit],[UnitPrice])<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Ejercicio <\/strong>2<\/p>\n\n\n\n<p><strong>Crear un \u00edndice para Empleados por LastName y FirstName,<br>incluir Title y Address<\/strong><br>Create index EmpleadosCargoDireccionIDXi on<br>Employees (LastName, FirstName)<br>Include (Title , Address)<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__01.png\" alt=\"\" class=\"wp-image-1668\" width=\"701\" height=\"439\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__01.png 858w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__01-300x188.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__01-768x482.png 768w\" sizes=\"auto, (max-width: 701px) 100vw, 701px\" \/><figcaption>Campos LastName y FirstName para el \u00edndice creado<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__02.png\" alt=\"\" class=\"wp-image-1669\" width=\"702\" height=\"440\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__02.png 858w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__02-300x188.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Include_Indices__02-768x482.png 768w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><figcaption>Campos Title y Address incluidos en el \u00edndice con Include<\/figcaption><\/figure>\n\n\n\n<p><strong>Ver los \u00edndices creados<\/strong><br>select * from sys.indexes where name like &#8216;Empleados%&#8217;<br>or name like &#8216;Productos%&#8217;<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Ejercicio <\/strong>3<\/p>\n\n\n\n<p><strong>Crear un \u00edndice para Clientes (Customers) para el campo<br>ContactName, incluir Country, Region y City<br>Asignar factor de relleno de 70 y dar consistencia si existe.<br><\/strong>if not exists<br>(select * from sys.indexes where name = &#8216;ClientesContactoIDX&#8217;)<br>Begin<br>Create index ClientesContactoIDX<br>on Customers([ContactName])<br>include (Country, Region , City)<br>with fillfactor = 70<br>End<br>Else<br>Begin<br>Create index ClientesContactoIDX<br>on Customers([ContactName])<br>include (Country, Region , City)<br>with (fillfactor = 70, drop_existing = on)<br>End<br>go<\/p>\n\n\n\n<p><strong>Cambiar el nombre al \u00edndice<\/strong><br>Para cambiar el nombre de un objeto en la base de datos se utiliza el procedimiento almacenado <strong>sp_rename<\/strong>, espec\u00ecficamente para cambiar el nombre a un \u00edndice la sintaxis es como sigue<br>Execute sp_rename &#8216;Tabla.NombreIndiceOriginal&#8217;, &#8216;NuevoNombre&#8217;, &#8216;INDEX&#8217;<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Ejercicio <\/strong>4<\/p>\n\n\n\n<p><strong>Cambiar el nombre del \u00edndice ProductosDescripcionIDXinUnidadPrecio<br>por ProductosNombreUnidadPrecioIDX<br><\/strong>Execute sp_rename &#8216;Products.ProductosDescripcionIDXinUnidadPrecio&#8217;,<br>&#8216;ProductosNombreUnidadPrecioIDX&#8217;, &#8216;Index&#8217;<br>go<br><strong>Resultado<br>Precauci\u00f3n: al cambiar cualquier parte del nombre de un objeto pueden dejar de ser scripts v\u00e1lidos y procedimientos almacenados.<\/strong><\/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","protected":false},"excerpt":{"rendered":"<p>Uso de Include en Indices SQL Server El uso de include en los \u00edndices permite ampliar la funcionalidad de los \u00edndices no agrupados. Para m\u00e1s informaci\u00f3n de \u00edndices Ver Indices en SQL Server.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1666\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1667,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[105,178,177],"class_list":["post-1666","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indices","tag-create-index","tag-include","tag-index","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1666","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=1666"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1666\/revisions"}],"predecessor-version":[{"id":1670,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1666\/revisions\/1670"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1667"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}