{"id":158,"date":"2017-09-18T21:40:33","date_gmt":"2017-09-18T21:40:33","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=158"},"modified":"2020-05-20T03:31:50","modified_gmt":"2020-05-20T03:31:50","slug":"indices-teoria-y-ejercicios","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=158","title":{"rendered":"\u00cdndices SQL Server &#8211; Teor\u00eda y ejercicios"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_IndicesD1.png\" alt=\"\" class=\"wp-image-1253\" width=\"303\" height=\"265\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>\u00cdndices en SQL Server<\/strong><\/h1>\n\n\n\n<p>Un \u00edndice se puede definir como un objeto que ordena los registros de una tabla o vista por uno o m\u00e1s campos de manera ascendente o descendente.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Importante<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>Es recomendable crear \u00edndices para optimizar las consultas.<\/li><li>Se pueden crear un \u00edndice agrupado, que se crea de manera autom\u00e1tica en la tabla con clave primaria (primary key) y 999 \u00edndices no agrupados en la misma tabla o vista.<\/li><li>En la lista de campos por el cual va a ordenar los registros de la tabla o vista pueden haber hasta 32 campos.<\/li><li>El orden puede ser ascendente (Asc) o descendente (Desc). El valor por defecto es Asc.<\/li><li>Las columnas con tipos de datos <strong>ntext<\/strong>,<strong>text<\/strong>,\u00a0<strong>varchar (max)<\/strong>,\u00a0 <strong>nvarchar(max)<\/strong>,\u00a0 <strong>varbinary (max)<\/strong>,\u00a0 <strong>xml<\/strong>, o\u00a0<strong>imagen<\/strong>\u00a0no se pueden crear \u00edndices.<\/li><li>Se puede crear \u00edndices para las tablas particionadas, estos \u00edndices se les llama \u00edndices particionados. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=557\">Ver \u00edndices particionados<\/a>)<\/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\n<h3 class=\"wp-block-heading\">Sintaxis<\/h3>\n\n\n\n<p>Para la creaci\u00f3n de \u00edndices vamos a dividir la sintaxis de acuerdo al tipo de \u00edndice.<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice no agrupado<\/strong><\/p>\n\n\n\n<p>Create nonclustered index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice agrupado<\/strong><\/p>\n\n\n\n<p>Create clustered index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice \u00fanico<\/strong><\/p>\n\n\n\n<p>Create unique index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice filtrado<\/strong><\/p>\n\n\n\n<p>Create index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>where Expresi\u00f3nL\u00f3gica<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice con factor de relleno<\/strong><\/p>\n\n\n\n<p>Create index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>with fillfactor = Valor<br>go<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Crear un \u00edndice y sobre escribir el existente<\/strong><\/p>\n\n\n\n<p>Create index NombreDelIndice on Tabla\/Vista (Camp1 orden[, Campo2 orden])<br>with drop_existing = on<br>go<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p><strong>Usando la base de datos Norwhwind<\/strong><br>use northwind<br>go<\/p>\n\n\n\n<p><strong>1. Crear un \u00edndice para la tabla Categories, campo CategoryName<\/strong><\/p>\n\n\n\n<p>Create index CategoriaNombreIDX on Categories (CategoryName asc)<br>go<\/p>\n\n\n\n<p><strong>2. Ver los \u00edndices de la tabla Categorias, suponer que los nombres de los \u00edndices comienzan con la palabra Categoria.<\/strong><\/p>\n\n\n\n<p>select * from sys.indexes where name like &#8216;Categoria%&#8217;<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_Indices_01-1024x117.jpg\" alt=\"\" class=\"wp-image-1251\" width=\"799\" height=\"91\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_01-1024x117.jpg 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_01-300x34.jpg 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_01-768x87.jpg 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_01.jpg 1212w\" sizes=\"auto, (max-width: 799px) 100vw, 799px\" \/><\/figure><\/div>\n\n\n\n<p><strong>3.&nbsp; Ver la estructura de la tabla, tambi\u00e9n se presentan la lista de indexados creados en la tabla.<\/strong><\/p>\n\n\n\n<p>sp_help Categories<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_Indices_02-1024x473.png\" alt=\"\" class=\"wp-image-1252\" width=\"829\" height=\"382\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_02-1024x473.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_02-300x139.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_02-768x355.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Indices_02.png 1290w\" sizes=\"auto, (max-width: 829px) 100vw, 829px\" \/><\/figure><\/div>\n\n\n\n<p><strong>4. Crear un \u00edndice para los productos que tengan un precio mayor a 30<\/strong><\/p>\n\n\n\n<p>Create index ProductosPrecioMas30IDX on Products(UnitPrice)<br>where UnitPrice &gt; 30<br>go<\/p>\n\n\n\n<p><strong>5. Crear un \u00edndice \u00fanico para los productos por el campo ProductName<\/strong><\/p>\n\n\n\n<p>Create unique index ProductosNombreIDX on Products(ProductName)<br>go<\/p>\n\n\n\n<p><strong>6. Crear un \u00edndice para empleados por los campos LastName y FirstName<\/strong><\/p>\n\n\n\n<p>Create index EmpleadosNombreCompletoIDX on Employees(LastName, FirstName)<br>go<\/p>\n\n\n\n<p><strong>7. Crear un \u00edndice para el campo UnitsInStock en la tabla Products con factor de relleno de 70<\/strong><\/p>\n\n\n\n<p>Create index ProductosStockIDX on Products(UnitsInStock)<br>with fillfactor = 70<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>8. Crear un \u00edndice para el nombre del cliente (campo CompanyName) en Customers.&nbsp;<\/strong><strong>Asignar un factor de relleno de 70 y sobre escribir el existente.&nbsp;<\/strong><strong>Usar la estructura If para comprobar que el \u00edndice existe<\/strong><\/p>\n\n\n\n<p>if not exists (select * from sys.indexes where name = &#8216;ClientesNombreIDX&#8217;)<br>Begin<br>Create index ClientesNombreIDX on Customers(CompanyName)<br>with fillfactor = 70<br>End<br>else<br>Begin<br>Create index ClientesNombreIDX on Customers(CompanyName)<br>with (fillfactor = 70, drop_existing = on)<br>End<br>go<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u00cdndices en SQL Server Un \u00edndice se puede definir como un objeto que ordena los registros de una tabla o vista por uno o m\u00e1s campos de manera ascendente o descendente.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=158\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1253,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[105,104],"class_list":["post-158","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indices","tag-create-index","tag-indices-sql-server","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/158","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=158"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":1255,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions\/1255"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1253"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}