SQL indexes

What Is an Index?

  • An index can be best described as a pointer to data in a table. An index in a database is very similar to an index in the back of a book or the phone book. Indexes are created on the table or the view.

Purpose of index

  • The purpose of an index is to retrieve data from a table efficiently and fast

Types of indexes

  • Clustered Indexes

Clustered indexes sort the data rows in the table or view based on their key values, as such there can be only one clustered index per table. When a table does not have an index, it is referred to as a heap.  When you create a clustered index, it does not require any additional disk space.

  • Non-clustered Indexes

A Non-clustered index have a structure separate from the data rows, much like the index in back of a book; and as such does require disk space, as it’s a separate object. A non-clustered index contains a pointer to the data row that contains the key value. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

  • Composite Indexes

A composite index is an index on two or more columns of a table. A composite index can be created both on a clustered and non clustered index. Note, when creating a composite index the order of columns in the index is important for performance. Columns that will always mentioned in a query should be placed first.

Other less used indexes are below:-

  • Covering Indexes
  • Full-text
  • Filtered Indexes
  • Column-based Indexes
  • Spatial

Creating indexes using TSQL:-

Syntax:-

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE NONCLUSTERED INDEX [indexname] ON table_or_view_name ([columnname] ASC|DESC)

CREATE CLUSTERED INDEX index_name

ON table_name (column_name)

 

Drop Index Syntax:-

DROP INDEX index_name ON table_name

EXAMPLE OF INDEXES AND CLUSTERED INDEX

CREATE TABLE [dbo].[PhoneBook](

[PhoneBookID] [int] NULL,

[lname] [varchar](40) NULL,

[fname] [varchar](40) NULL

[phone] [varchar](50) NULL

) ON [PRIMARY]

GO

 

–Insert 1000 records from RedGate app View the 1000 records. Notice that the last name is not in any particular order

SELECT * FROM PHONEBOOK

–Insert record into phonebook table and notice the row is inserted after 1000 rows, as there is no index and the Lname is in no particular order

Insert into PHONEBOOK

values (1001,'Abba','Sara','555-1212')

SELECT * FROM PHONEBOOK

--repeat with another record

Insert into PHONEBOOK

values (1002,'Turner','Mike','805-555-1212')

SELECT * FROM PHONEBOOK

–Create a clustered index on table phonebook and column Lname so that the last name is alphabetized (its sorted by the clustered index created)

–Index name with prefix, table name and column name (convention for clarity)

CREATE CLUSTERED INDEX [Idx_PhoneBook_Lname]

ON PhoneBook(Lname ASC)           -- Table and column name

GO

SELECT * FROM PHONEBOOK

 

–Now with the clusttered index in place, if we insert a record, it will automatically be inserted in the sorted order.

Insert into PHONEBOOK

values (1003,'Briggham','Johm','777-555-1212')

SELECT * FROM PHONEBOOK

 

NON CLUSTERED INDEX SCRIPT

Example of non clustered index:-

— notice that after creation of a non clustered index, the data is not sorted

CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_ fname]

ON PhoneBook(fname ASC)

SELECT *

FROM [SQL2].[dbo].[PhoneBook]

--Can create multiple non clustered index

CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_phone]

ON PhoneBook(phone ASC)

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *