{"id":4853,"date":"2022-09-26T11:36:47","date_gmt":"2022-09-26T11:36:47","guid":{"rendered":"https:\/\/www.wikitechy.com\/interview-questions\/?p=4853"},"modified":"2022-09-26T11:36:47","modified_gmt":"2022-09-26T11:36:47","slug":"types-of-indexes-in-sql","status":"publish","type":"post","link":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/","title":{"rendered":"Types of indexes in SQL"},"content":{"rendered":"<p style=\"text-align: justify;\">There are various\u00a0types of indexes in SQL server:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Clustered Index<\/li>\n<li>Non-Clustered Index<\/li>\n<li>Column Store Index<\/li>\n<li>Filtered Index<\/li>\n<li>Hash Index<\/li>\n<li>Unique Index<\/li>\n<\/ul>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone size-full wp-image-4854 aligncenter\" src=\"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg\" alt=\"\" width=\"654\" height=\"427\" srcset=\"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg 654w, https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql-300x196.jpg 300w, https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql-390x255.jpg 390w\" sizes=\"(max-width: 654px) 100vw, 654px\" \/><\/p>\n<h2 id=\"clustered-index\" style=\"text-align: justify;\"><strong>Clustered Index<\/strong><\/h2>\n<ul style=\"text-align: justify;\">\n<li>Rows of data are sorted and stored data in a table or view depending on the central values by clustered index.<\/li>\n<li>Each table can have one clustered index as it enables the user to store the data in a single order.<\/li>\n<li>Data is sorted and stored in gathered index, so if we see a data in a table sorted means it is arranged with clustered index.<\/li>\n<li>If a table contains clustered index, it is called as clustered table.<\/li>\n<li>When a huge data needs to mdified in a database we use clustered index.<\/li>\n<\/ul>\n<h2 id=\"non-clustered-index\" style=\"text-align: justify;\"><strong>Non Clustered index<\/strong><\/h2>\n<ul style=\"text-align: justify;\">\n<li>Non clustered index are structures that are seperated from the data row.<\/li>\n<li>This type of index contains non clustered key values and those pairs has a pointer to that data row.<\/li>\n<li>In non clustered index, arrow from an index row to data row is called as row locator.<\/li>\n<li>Row locator structure identifies whether the data pages are in the form of clustered table or heap.<\/li>\n<li>Row locator is termed as cluster index key in clustered chart.<\/li>\n<li>User can easily add non key columns to the leaf level in a non clustered index as it byepasses the existing index key limits to perform fully covered indexed queries.<\/li>\n<li>A non clustered index is created to improve the overall performance of frequently asked questions which cannot be created in clustered items.<\/li>\n<\/ul>\n<h3 id=\"unique-index\" style=\"text-align: justify;\"><strong>Unique Index<\/strong><\/h3>\n<ul style=\"text-align: justify;\">\n<li>It enables users to confirm that every row in the table is unique in one way or other by checking if the indexing key contains any duplicate values.<\/li>\n<li>When user wants to use the unique character of data he can use unique index.<\/li>\n<li>Unique index allows indidviduals to ensure that data integrity of each defined column of the table in the database.<\/li>\n<li>It provides additional information about the data table which is helpful for query optimizer.<\/li>\n<\/ul>\n<h3 id=\"filtered-index\" style=\"text-align: justify;\"><strong>Filtered Index<\/strong><\/h3>\n<ul style=\"text-align: justify;\">\n<li>This index is created when column has only a small number of relevent values for queries on the subset of values.<\/li>\n<li>When table contains hetrogeneous data rows, a filtered index is created in sql for one or more data types.<\/li>\n<li>Filtered index is indicate even when query optimizer does not cover any query.<\/li>\n<li>Filtered index indexes the portion of rows in a table, it applies filter on index by improving the overall performance of the query.<\/li>\n<li>Filtered index helps to reduce the maintanence cost and storage cost<\/li>\n<li>Overall impact of data modification is less in filtered index as it is updated only when a new record is inserted or when the data of the index is impacted.<\/li>\n<\/ul>\n<h3 id=\"column-store-index\" style=\"text-align: justify;\"><strong>Column Store Index<\/strong><\/h3>\n<ul style=\"text-align: justify;\">\n<li>Column store index is standard form of index when it comes to storing and querying the large data warehousing tables.<\/li>\n<li>This index was designed for the impovement in the performance of query in case of workloads with vast data.<\/li>\n<li>Data are stored as column based format.<\/li>\n<li>It reduces overall storage cost and provides high level compression of data.<\/li>\n<li>Since data compression happens, user can efficiently perform input and output functions for a high performance result.<\/li>\n<\/ul>\n<h2 id=\"hash-index\" style=\"text-align: justify;\"><strong>Hash Index<\/strong><\/h2>\n<ul style=\"text-align: justify;\">\n<li style=\"text-align: justify;\">Hash index is simply an array in sql that contains pointer slot and a row on each slot.<\/li>\n<li style=\"text-align: justify;\">Hash index use hash function F(K,N) where K is critical and N is number of Buckets.<\/li>\n<li style=\"text-align: justify;\">Function maps out to key corresponding to the bucket of the hash index.<\/li>\n<li style=\"text-align: justify;\">Each hash bucket index contains 8 bytes which is used to store the memory address of the linked list of critical entities.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>There are various\u00a0types of indexes in SQL server: Clustered Index Non-Clustered Index Column Store Index Filtered Index Hash Index Unique Index Clustered Index Rows of data are sorted and stored data in a table or view depending on the central values by clustered index. Each table can have one clustered index as it enables the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6547],"tags":[18598,18599,18597,18590,18591,18595,7294,18589,18593,6619,18594,18592,18600,18596],"class_list":["post-4853","post","type-post","status-publish","format-standard","hentry","category-sql","tag-3-major-types-of-indexes","tag-explain-the-types-of-indexes","tag-indexed-in-sql-server-with-examples","tag-sql-queries-on-clustered-and-non-clustered-indexes","tag-types-of-indexes","tag-types-of-indexes-in-mysql","tag-types-of-indexes-in-sql","tag-types-of-indexes-in-sql-server","tag-types-of-indexes-in-sql-with-example","tag-types-of-indexes-in-sql-with-examples","tag-types-on-index-in-sql","tag-what-are-indexed-and-types-of-indexes-in-sql","tag-what-are-the-types-of-indexes-in-sql-server","tag-what-is-index-in-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Types of indexes in SQL - Different types of Indexes in SQL Server<\/title>\n<meta name=\"description\" content=\"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Types of indexes in SQL - Different types of Indexes in SQL Server\" \/>\n<meta property=\"og:description\" content=\"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Wikitechy\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-26T11:36:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg\" \/>\n<meta name=\"author\" content=\"webmaster\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"webmaster\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/\"},\"author\":{\"name\":\"webmaster\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/#\\\/schema\\\/person\\\/f785ba3ecc599133e65ab6138042a3e4\"},\"headline\":\"Types of indexes in SQL\",\"datePublished\":\"2022-09-26T11:36:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/\"},\"wordCount\":599,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/types-of-index-in-sql.jpg\",\"keywords\":[\"3 major types of indexes\",\"explain the types of indexes\",\"indexed in sql server with examples\",\"sql queries on clustered and non-clustered indexes\",\"types of indexes\",\"types of indexes in mysql\",\"types of indexes in sql\",\"types of indexes in sql server\",\"types of indexes in sql with example\",\"types of indexes in sql with examples\",\"types on index in sql\",\"what are indexed and types of indexes in sql\",\"what are the types of indexes in sql server\",\"what is index in sql\"],\"articleSection\":[\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/\",\"url\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/\",\"name\":\"Types of indexes in SQL - Different types of Indexes in SQL Server\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/types-of-index-in-sql.jpg\",\"datePublished\":\"2022-09-26T11:36:47+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/#\\\/schema\\\/person\\\/f785ba3ecc599133e65ab6138042a3e4\"},\"description\":\"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/sql\\\/types-of-indexes-in-sql\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/types-of-index-in-sql.jpg\",\"contentUrl\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/types-of-index-in-sql.jpg\",\"width\":654,\"height\":427},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/#website\",\"url\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/\",\"name\":\"Wikitechy\",\"description\":\"Interview Questions\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/#\\\/schema\\\/person\\\/f785ba3ecc599133e65ab6138042a3e4\",\"name\":\"webmaster\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g\",\"caption\":\"webmaster\"},\"sameAs\":[\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\"],\"url\":\"https:\\\/\\\/www.wikitechy.com\\\/interview-questions\\\/author\\\/webmaster\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Types of indexes in SQL - Different types of Indexes in SQL Server","description":"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"Types of indexes in SQL - Different types of Indexes in SQL Server","og_description":"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index","og_url":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/","og_site_name":"Wikitechy","article_published_time":"2022-09-26T11:36:47+00:00","og_image":[{"url":"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg","type":"","width":"","height":""}],"author":"webmaster","twitter_card":"summary_large_image","twitter_misc":{"Written by":"webmaster","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#article","isPartOf":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/"},"author":{"name":"webmaster","@id":"https:\/\/www.wikitechy.com\/interview-questions\/#\/schema\/person\/f785ba3ecc599133e65ab6138042a3e4"},"headline":"Types of indexes in SQL","datePublished":"2022-09-26T11:36:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/"},"wordCount":599,"commentCount":0,"image":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg","keywords":["3 major types of indexes","explain the types of indexes","indexed in sql server with examples","sql queries on clustered and non-clustered indexes","types of indexes","types of indexes in mysql","types of indexes in sql","types of indexes in sql server","types of indexes in sql with example","types of indexes in sql with examples","types on index in sql","what are indexed and types of indexes in sql","what are the types of indexes in sql server","what is index in sql"],"articleSection":["SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/","url":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/","name":"Types of indexes in SQL - Different types of Indexes in SQL Server","isPartOf":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg","datePublished":"2022-09-26T11:36:47+00:00","author":{"@id":"https:\/\/www.wikitechy.com\/interview-questions\/#\/schema\/person\/f785ba3ecc599133e65ab6138042a3e4"},"description":"Types of indexes in SQL - Clustered Index - Non-Clustered Index - Column Store Index - Filtered Index - Hash Index - Unique Index","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.wikitechy.com\/interview-questions\/sql\/types-of-indexes-in-sql\/#primaryimage","url":"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg","contentUrl":"https:\/\/www.wikitechy.com\/interview-questions\/wp-content\/uploads\/2022\/09\/types-of-index-in-sql.jpg","width":654,"height":427},{"@type":"WebSite","@id":"https:\/\/www.wikitechy.com\/interview-questions\/#website","url":"https:\/\/www.wikitechy.com\/interview-questions\/","name":"Wikitechy","description":"Interview Questions","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wikitechy.com\/interview-questions\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.wikitechy.com\/interview-questions\/#\/schema\/person\/f785ba3ecc599133e65ab6138042a3e4","name":"webmaster","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/155b77fd8cdda3d0913fcb7e7ee63543b0c345d2d8f6dcebda5b0583ab61f967?s=96&d=mm&r=g","caption":"webmaster"},"sameAs":["https:\/\/www.wikitechy.com\/interview-questions"],"url":"https:\/\/www.wikitechy.com\/interview-questions\/author\/webmaster\/"}]}},"_links":{"self":[{"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/posts\/4853","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/comments?post=4853"}],"version-history":[{"count":1,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/posts\/4853\/revisions"}],"predecessor-version":[{"id":4855,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/posts\/4853\/revisions\/4855"}],"wp:attachment":[{"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/media?parent=4853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/categories?post=4853"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wikitechy.com\/interview-questions\/wp-json\/wp\/v2\/tags?post=4853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}