Types of Indexes in SQL Server: All 12 Types Explained with Syntax (2026)

SQL Server supports twelve distinct index types: clustered, non-clustered, unique, filtered, clustered columnstore, non-clustered column store, hash, memory-optimized non-clustered, XML, spatial, full-text and vector. Each one solves a different retrieval problem. Choosing correctly is the difference between a query that returns in eight milliseconds and the same query returning in eight seconds.

Most articles on the types of indexes in SQL Server stop at six. They list clustered, non-clustered, unique, columnstore, filtered and full-text, then close with a paragraph about how indexes are like the index in a book. That was a complete answer in 2019. It is not a complete answer now. SQL Server 2025, released to general availability on 18 November 2025, added a native vector index built on Microsoft’s DiskANN algorithm — the index type that makes semantic search and retrieval-augmented generation possible inside the database engine itself. Any guide that omits it is describing a product that no longer exists.

This guide covers all twelve, with working T-SQL syntax, the workload each one is designed for, the trade-off each one imposes, and a decision matrix you can apply to a real schema. It is written for BCA, MCA and Diploma in Data Science students who need this for a semester examination, and for working professionals who need it for a technical interview on Monday morning.

All 12 types of indexes in SQL Server at a glance

Before the detail, here is the complete taxonomy in one view. Bookmark this table — it is the answer to the exam question “list the types of indexes in SQL Server.”

Know More: Best BCA Online Colleges in India (2026): The 7-Point Checklist to Choose the Right One

What is an index in SQL Server, and why does it change everything?

An index is a separate, ordered data structure that lets the SQL Server query optimizer locate rows without reading every page in the table.

Without an index, SQL Server performs a table scan: it reads all 8 KB data pages belonging to the table and discards the rows that do not match. On a 50-million-row orders table, that is millions of page reads for a query that returns four rows.

With a suitable index, SQL Server performs an index seek: it navigates a B-tree from the root page down through intermediate levels to the exact leaf page holding the matching rows. Three or four page reads instead of millions.

A table with no clustered index is called a heap. Rows sit in no guaranteed order, and the only way to find anything is to scan. A table with a clustered index is called a clustered table, and its rows are stored in the logical order of the index key.

Two terms you must be able to distinguish in any interview:

• Index seek — the optimizer navigates directly to the qualifying rows. This is what you want.
• Index scan — the optimizer reads every leaf page of the index. Better than a table scan, but it usually signals a missing or badly ordered index.

A third term decides whether your index actually helps: key lookup. When a non-clustered index contains the search column but not the columns in your SELECT list, SQL Server must jump back to the clustered index for every matching row. On a query returning 40,000 rows, that is 40,000 extra lookups, and the optimizer will frequently abandon your index altogether and scan the table instead. Solving key lookups is what included columns exist for, and we return to them below.

The four rowstore SQL Server index types you will be tested on

Rowstore indexes store data row by row in a B-tree structure. These are the four that appear in almost every syllabus and almost every interview.

1. Clustered index

A clustered index determines the physical storage order of the rows in a table. The leaf level of the index is the table data — there is no separate copy. Because rows can only be sorted one way, a table can have exactly one clustered index.

CREATE CLUSTERED INDEX IX_Orders_OrderDate

    ON dbo.Orders (OrderDate);

When you declare a PRIMARY KEY, SQL Server creates a clustered index on that column by default unless you specify NONCLUSTERED. That default is a convenience, not a recommendation — the best clustered key is narrow, unique, static and ever-increasing, which is why an identity column or a date column often outperforms a natural key.

Best for: range queries (BETWEEN, >=, <=), ORDER BY on the key column, and as the anchor for every non-clustered index on the table.

Cost: every insert into the middle of the key range can cause page splits, which fragment the index and slow writes.

2. Non-clustered index

A non-clustered index is a separate structure that holds the key columns in sorted order plus a row locator pointing back to the actual data — the clustered index key if the table is clustered, or a physical row identifier (RID) if it is a heap. A single table supports up to 999 non-clustered indexes, though a schema needing more than a handful is usually a schema with a design problem.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID

    ON dbo.Orders (CustomerID)

    INCLUDE (OrderDate, TotalAmount);

The INCLUDE clause is the single most underused feature in SQL Server indexing. Included columns are stored only at the leaf level, are not part of the sort key, and do not count against the 1,700-byte key size limit. Adding the columns your query returns turns the index into a covering index: SQL Server answers the entire query from the index and never touches the base table. No key lookups, no table access, dramatically fewer reads.

Understanding the difference between a clustered and non-clustered index is the most frequently asked SQL Server interview question at every experience level, so be precise: the clustered index is the data, sorted; the non-clustered index is a pointer structure to the data.

3. Unique index

A unique index enforces that no two rows share the same value in the indexed column or column combination. It serves two purposes at once — data integrity and query performance — because the optimizer knows it will find at most one matching row.

CREATE UNIQUE NONCLUSTERED INDEX UX_Students_Email

    ON dbo.Students (Email);

A UNIQUE constraint and a unique index are implemented identically by the engine; the constraint is the declarative form, the index is the physical form. A unique index can be clustered or non-clustered, and it can span multiple columns, in which case the combination must be unique rather than each column individually.

4. Filtered index

A filtered index in SQL Server is a non-clustered index defined with a WHERE clause, so it covers only a subset of rows. Because the structure is smaller, it is cheaper to store, faster to scan and cheaper to maintain.

CREATE NONCLUSTERED INDEX IX_Orders_Pending

    ON dbo.Orders (CustomerID, OrderDate)

    WHERE OrderStatus = ‘Pending’;

The classic use case for a filtered index in SQL Server is a status column with skewed distribution — 2% of orders pending, 98% completed. A full index on OrderStatus is nearly useless because the column has terrible selectivity. A filtered index covering only pending orders is small, highly selective and used constantly.

Two caveats that examiners like: a filtered index in SQL Server is non-clustered only, and the query’s WHERE clause must be logically compatible with the index filter for the optimizer to use it. SET options such as ANSI_NULLS and QUOTED_IDENTIFIER must also be correctly configured on the connection.

Clustered vs non-clustered vs unique vs filtered

Among all the types of indexes in SQL Server, these four are the ones an examiner will almost certainly ask you to compare. This table answers that question directly.

Columnstore indexes: the analytics engine

Everything above stores data row by row. A columnstore index in SQL Server inverts that: it stores each column separately, compresses it, and processes queries in batch mode rather than row by row. Microsoft’s published guidance puts the gain at up to 10x data compression and up to 100x query performance for analytical workloads compared with equivalent rowstore storage.

The reason is straightforward. An analytical query such as SELECT Region, SUM(Revenue) FROM Sales GROUP BY Region touches two columns out of forty. Rowstore reads all forty because rows are stored intact. Columnstore reads two. Compression is also far higher because values within a single column are similar to one another — a Region column with eight distinct values across ten million rows compresses enormously.

There are two variants:

Clustered columnstore index — this is the table’s storage. The entire table is held in column format. Use it for fact tables and data warehouse tables that are read far more often than they are updated.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales

    ON dbo.FactSales;

Non-clustered columnstore index — a secondary columnstore index in SQL Server that sits on top of a normal rowstore table. This is the key to hybrid transactional/analytical processing: the rowstore serves your OLTP writes, the columnstore serves your reports, and both stay current.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders

    ON dbo.Orders (OrderDate, ProductID, Quantity, TotalAmount);

Rows are organised into rowgroups of roughly one million rows, and each column segment within a rowgroup is compressed independently. Rowgroups that are too small — the result of trickle inserts — degrade performance badly, which is why bulk loading into a columnstore index in SQL Server is preferred over row-by-row insertion.

Memory-optimized indexes: hash and Bw-tree

These two exist only on memory-optimized tables (In-Memory OLTP). They live in memory, contain no data pages, and are rebuilt from the durable data at database startup.

5. Hash index

A hash index applies a hash function to the key and maps it to a bucket. Point lookups become close to O(1) — the fastest single-row retrieval SQL Server offers.

CREATE TABLE dbo.SessionCache (

    SessionID  INT       NOT NULL PRIMARY KEY NONCLUSTERED

                         HASH WITH (BUCKET_COUNT = 1000000),

    UserID     INT       NOT NULL,

    LastSeen   DATETIME2 NOT NULL

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

The critical parameter is BUCKET_COUNT. Set it too low and multiple keys collide into one bucket, forming long chains that destroy performance. Microsoft’s guidance is to set it to roughly one to two times the number of distinct key values. The hard limitation: a hash index supports equality predicates only. It cannot serve a range query or an ORDER BY, because hashing destroys ordering.

6. Memory-optimized non-clustered index

Also called a Bw-tree index, this is the range-capable counterpart. It supports inequality predicates, BETWEEN and ordered scans on memory-optimized tables — everything a hash index cannot do.

ALTER TABLE dbo.SessionCache

    ADD INDEX IX_SessionCache_LastSeen NONCLUSTERED (LastSeen);

Rule of thumb: hash for equality-only lookups where you know the cardinality; Bw-tree for everything else, including when you are unsure of the distribution.

Specialised SQL Server index types: XML, spatial and full-text

These three SQL Server index types are domain-specific. Students routinely skip them and then meet them in an interview.

7. XML index

Querying an XML column with XQuery without an index forces SQL Server to shred the entire XML document at runtime, every time. A primary XML index stores a pre-shredded representation of the document. Once it exists, you can add up to three secondary XML indexes — PATH, VALUE and PROPERTY — each tuned to a different query shape.

CREATE PRIMARY XML INDEX PXML_Students_Details

    ON dbo.Students (StudentDetails);

CREATE XML INDEX SXML_Students_Path

    ON dbo.Students (StudentDetails)

    USING XML INDEX PXML_Students_Details FOR PATH;

The table must already have a clustered primary key. A primary XML index typically consumes a substantial multiple of the base XML data size, so treat it as a considered decision rather than a default.

8. Spatial index

A spatial index accelerates queries over geometry and geography data — proximity searches, containment checks, distance calculations. SQL Server decomposes space into a four-level tessellated grid and indexes cell membership in a B-tree.

CREATE SPATIAL INDEX SIX_Campus_Location

    ON dbo.Campus (LocationGeo)

    USING GEOGRAPHY_AUTO_GRID;

Any application answering “which branches are within five kilometres of this pin” depends on this index type.

9. Full-text index

A full-text index enables linguistic search across large character columns: word forms, thesaurus expansion, proximity and inflectional matching, through the CONTAINS and FREETEXT predicates. LIKE with a leading wildcard cannot use a standard B-tree index at all and degrades linearly with table size; full-text search does not.

CREATE FULLTEXT INDEX ON dbo.Articles (Body LANGUAGE 1033)

    KEY INDEX UX_Articles_ID

    ON ftCatalog

    WITH CHANGE_TRACKING AUTO;

A table supports only one full-text index, and it requires an existing unique, single-column, non-nullable index as its key.

10, 11 and 12: vector indexes and the AI-era additions

This is the section the competing articles do not have, and it is the reason this guide exists.

SQL Server 2025 (version 17.x, compatibility level 170) introduced a native VECTOR data type for storing embeddings, and with it a vector index built on DiskANN — a graph-based approximate nearest neighbour algorithm developed by Microsoft Research that leverages SSD storage to index far more vectors than an in-memory structure could hold, while sustaining high queries per second.

CREATE VECTOR INDEX VI_Articles_Embedding

    ON dbo.Articles (BodyEmbedding)

    WITH (METRIC = ‘cosine’, TYPE = ‘diskann’);

Queries then use the VECTOR_SEARCH function to run approximate nearest-neighbour searches. The metric can be cosine, euclidean or dot, depending on how your embedding model was trained.

Three practical notes, because accuracy matters more than hype:

• The VECTOR data type and VECTOR_DISTANCE reached general availability. CREATE VECTOR INDEX and VECTOR_SEARCH shipped as preview features in SQL Server 2025 and require ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON. Microsoft does not recommend preview features for production.
• Vector indexes require a minimum row count before creation — roughly 100 rows in current builds.
• Exact search with VECTOR_DISTANCE remains correct and is often faster below roughly 50,000 candidate vectors. The index earns its place above that threshold.

This is also why vector search now sits alongside model training in applied AI and machine learning curricula rather than in a specialist elective.

Why this belongs in a discussion of the types of indexes in SQL Server rather than in a separate AI article: it means a retrieval-augmented generation pipeline no longer requires a bolted-on vector database. Embeddings, relational data, joins and security all live in one engine, under one backup policy and one permission model. For a student building a final-semester project, that is a materially simpler architecture.

The remaining two entries in the taxonomy of twelve are the clustered and non-clustered columnstore variants counted separately above, since Microsoft treats them as distinct index objects with different storage semantics.

Read More : BCA or BTech: Which Course Should You Choose After Class 12?

Which index should you actually create? A decision matrix

Listing SQL Server index types is the easy half. Choosing between them under a real workload is what separates a graduate who can answer the exam question from one who can do the job.

Work through it in this order:

1. Read the execution plan first. Never create an index from intuition. Look for Table Scan, Clustered Index Scan and Key Lookup operators.
2. Put the most selective column first in a composite key. Column order in a composite index is not cosmetic — an index on (LastName, City) serves a query filtering on LastName alone, but not one filtering on City alone.
3. Cover the query with INCLUDE rather than widening the key.
4. Verify the improvement. Compare logical reads before and after with SET STATISTICS IO ON.

Know More : How to Become a Software Developer After BCA: Skills, Courses, and Career Path

When you should not create an index

Indexes are not free. Every index must be updated on every INSERT, UPDATE and DELETE that touches its columns. An over-indexed OLTP table can write more slowly than an unindexed one.

Avoid indexing when:

• The table is small — a few hundred rows fit in a handful of pages and scan faster than a seek.
• The column has low selectivity. A gender or boolean column with two distinct values across a million rows will be ignored by the optimizer.
• The table is write-heavy and the index is rarely read.
• The index duplicates an existing one. (CustomerID) is redundant if (CustomerID, OrderDate) already exists, because the leading column is already covered.

Find indexes nobody uses with the usage statistics DMV:

SELECT OBJECT_NAME(s.object_id) AS TableName,

       i.name AS IndexName,

       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates

FROM   sys.dm_db_index_usage_stats AS s

JOIN   sys.indexes AS i

       ON i.object_id = s.object_id AND i.index_id = s.index_id

WHERE  s.database_id = DB_ID()

  AND  s.user_seeks + s.user_scans + s.user_lookups = 0

ORDER BY s.user_updates DESC;

Any index with zero reads and high user_updates is pure overhead. Drop it.

Index maintenance in one paragraph

Indexes fragment as pages split. The widely used threshold, drawn from Microsoft’s own guidance, is to REORGANIZE between roughly 5% and 30% average fragmentation and REBUILD above 30%. Check with sys.dm_db_index_physical_stats. FILLFACTOR controls how much free space each leaf page reserves at build time — leave it at the default for read-mostly tables, lower it to 80–90 for tables with heavy mid-range inserts. And keep statistics current, because the optimizer chooses between your indexes using statistics, not the indexes themselves.

Six interview questions on SQL Server indexes, answered

Q. Explain the difference between a clustered and non-clustered index.

The clustered index is the table data held in key order. The non-clustered index is a separate structure of key values and row locators pointing at that data. One clustered index per table; up to 999 non-clustered.

Q. How many clustered indexes can a table have, and why?

One. The clustered index defines the physical order of the rows, and rows can only be physically ordered one way.

Q. What is the difference between an index seek and an index scan?

A seek navigates the B-tree directly to the qualifying rows. A scan reads every leaf page. A scan on a large table usually means the index is missing, badly ordered, or the predicate is not sargable.

Q. What is a covering index?

A non-clustered index containing every column the query needs, either as key columns or via INCLUDE, so the query is satisfied without touching the base table.

Q. When would you use a columnstore index over a rowstore index?

Use a columnstore index in SQL Server for analytical queries that aggregate a few columns across many rows. Rowstore suits OLTP point lookups and updates; columnstore suits scans, aggregations and reporting.

Q. What is new in SQL Server 2025 indexing?

Native vector indexes using the DiskANN algorithm, enabling approximate nearest-neighbour search over embeddings through CREATE VECTOR INDEX and VECTOR_SEARCH, currently as preview features.

Where you learn this properly: database papers at JNU Online

Reading a guide gets you through an interview round. Building and breaking indexes on a real database is what makes the knowledge stick, and that requires a structured programme with laboratory credits rather than a video playlist.

Jaipur National University delivers its online and distance programmes through its Centre for Distance and Online Education. The university holds UGC recognition under Section 2(f), UGC-DEB entitlement for its online programmes, NAAC A+ accreditation and Association of Indian Universities membership — every approval document is published for public inspection on the JNU Online website, which is the verification step every applicant should complete before paying any fee.

Three JNU Online programmes carry database papers with dedicated laboratory credits, which is where indexing is actually practised rather than merely read about.

A few specifics worth knowing:

• The Online MCA teaches Database Management System as a core paper in Semester I with an accompanying laboratory, then returns to the subject in Semester III with Advanced Database Concepts as a discipline-specific elective plus a dedicated laboratory. Semester III also offers Big Data Analytics and Cloud Computing as electives — directly relevant if columnstore and vector indexing interest you. The final semester carries 20 credits of industrial training and 5 credits for a research paper publication.
• The Online BCA covers Database Management System with a laboratory in Semester II, then moves to hands-on query writing in Semester III through MySQL (SQL/PL-SQL) with a project-based laboratory, and reaches Data Warehousing and Data Mining in Semester V.
• The Online Diploma in Data Science is the fastest route for someone who already holds a degree or simply wants the skill. Database Management Systems with a laboratory sits in Semester I, followed by Big Data Analytics, Data Analysis Using Python and a capstone project in Semester II across 34 total credits.

MCA eligibility is a bachelor’s degree of at least three years in any stream with at least 40% marks and Mathematics as a subject; candidates who did not study Mathematics at graduation level complete a bridge programme first. BCA and the Diploma in Data Science require 10+2 with 40% marks. Admission is on merit, with no entrance examination.

If you are still deciding between qualifications, our comparison of MSc Computer Science vs MCA sets the two degrees side by side on eligibility, syllabus and online availability. If you are at the undergraduate stage instead, the seven-point checklist for choosing BCA online colleges covers exactly what to verify before applying, and the guide to UGC-approved online degree courses in India walks through the DEB-ID process step by step.

Conclusion

The complete set of types of indexes in SQL Server is twelve, not six: four rowstore structures, two columnstore variants, two memory-optimized structures, three specialised indexes for XML, spatial and full-text data, and one vector index for the AI workloads that arrived with SQL Server 2025.

Memorising the list gets you a mark in an examination. What gets you hired is the reasoning underneath it — reading an execution plan, recognising a key lookup, knowing that a filtered index beats a full index on a skewed column, and knowing that an index nobody reads is a tax on every write.

Start with the execution plan, index the predicate, cover the SELECT list, and measure. Then verify your qualification the same way you verify an index: check it against the source. For any online degree in India, that means the UGC-DEB entitlement list for your intake year.

Frequently Asked Questions

How many types of indexes are there in SQL Server?

There are twelve types of indexes in SQL Server: clustered, non-clustered, unique, filtered, clustered columnstore, non-clustered columnstore, hash, memory-optimized non-clustered, XML, spatial, full-text and vector. Older guides list six because they predate the columnstore, memory-optimized and vector additions.

What is the main difference between a clustered and non-clustered index?

A clustered and non-clustered index differ in what they store. A clustered index stores the table rows themselves in key order, so a table can have only one. A non-clustered index is a separate structure holding key values plus pointers back to the rows, and a table can have up to 999.

Can a table have more than one clustered index?

No. Rows can be physically ordered only one way. A table without a clustered index is called a heap.

Which index type is fastest in SQL Server?

There is no fastest index type in the abstract. A hash index gives the fastest single-row equality lookup, a clustered index is fastest for range scans, and a columnstore index is fastest for aggregations over millions of rows. The fastest index is the one matching your query pattern.

What is a covering index?

A non-clustered index that contains every column a query requires, using key columns plus the INCLUDE clause, so SQL Server never needs to read the base table.

Do indexes slow down INSERT and UPDATE operations?

Yes. Every index must be maintained on every write that touches its columns. This is why unused indexes should be identified through sys.dm_db_index_usage_stats and dropped.

What is a vector index in SQL Server 2025?

A DiskANN-based graph index over VECTOR columns that enables approximate nearest-neighbour search for semantic search and retrieval-augmented generation. It is created with CREATE VECTOR INDEX and queried with VECTOR_SEARCH, and ships as a preview feature requiring PREVIEW_FEATURES to be enabled.

Is a unique index the same as a primary key?

Not quite. A primary key creates a unique index and additionally enforces NOT NULL, and there can be only one per table. A unique index permits a single NULL value and a table can have many.

Which JNU Online programme teaches SQL and database indexing?

The Online MCA covers Database Management System in Semester I and Advanced Database Concepts in Semester III, both with laboratories. The Online BCA covers Database Management System in Semester II and MySQL (SQL/PL-SQL) in Semester III. The Online Diploma in Data Science covers Database Management Systems with a laboratory in Semester I.

Follow Us: Facebook | Instagram