What is index?

• Excerpts:
  • Index => Table index (as like book index)
  • When table index is created on columns, a B-Tree data structure is created using those columns
  • If a table has index, then querying (to that table) looks into index and finds rows (using pointers stored in B-Tree) efectively (instead of looking into entire table - which happens in case of non-indexed table)
• Wikipedia:
  • A database index is a (B Tree/B+ Tree) data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure
  • Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed
  • Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records
• From essentialsql.com
  • A database index allows a query to efficiently retrieve data from a database table
  • Indexes are related to specific tables and consist of one or more keys
  • A table can have more than one index built from it
  • The keys are a fancy term for the values we want to look up in the index. The keys are based on the tables’ columns
• An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don’t have indexes on any column in the WHERE clause, the SQL server has to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables. https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql

Index data structure

• normal => B-Tree / B+ Tree
• spatial data => R-Tree
• memory table => has index

Index types

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes
• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

Creating indexes in SSMS

Step-1

Step-2

Step-3

Step-4

Step-5

More: https://www.mssqltips.com/sqlservertip/5573/creating-indexes-with-sql-server-management-studio/

Creating Indexes using T-SQL

-- Create a nonclustered index on a table or view
CREATE INDEX i1 ON t1 (col1);

-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);

-- Syntax for SQL Server and Azure SQL Database
-- Create a nonclustered index with a unique constraint
-- on 3 columns and specify the sort order for each column
CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

More: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql#syntax