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)