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 Nonclustered 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 nonclustered 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 (depends)
· Covering Indexes
· Full-text
· Filtered Indexes
· Column-based Indexes
· Spatial
· XML
Creating indexes using TSQL and GUI
CREATE INDEX index_name
ON table_name (column_name)
ON table_name (column_name)
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
ON table_name (column_name)
CREATE NONCLUSTERED INDEX [indexname]
ON table_or_view_name ([columnname] ASC|DESC)
DROP INDEX index_name ON table_name
–CREATE CLUSTERED INDEX index_name
–ON table_name (column_name)
EXAMPLE OF INDEXES AND CLUSTERED INDEX
–Insert 1000 records from RedGate app
USE [SQL2]
GO
CREATE TABLE [dbo].[PhoneBook](
[PhoneBookID] [int] NULL,
[lname] [varchar](50) NULL,
[fname] [varchar](50) 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)
USE [SQL2]
GO
CREATE CLUSTERED INDEX [Idx_PhoneBook_Lname] –<< Index name with prefix, table name and column name (convention for clarity)
ON PhoneBook(Lname ASC) –<< Table and column name
GO
–View the 1000 records. This time notice that the last name IS in order by the last!
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
USE [SQL2]
GO
Drop table PHONEBOOK
NON CLUSTERED INDEX SCRIPT
–View data with clustered index (lname column is still sorted)
SELECT*
FROM [SQL2].[dbo].[PhoneBook]
— Create another clustered index causes an issue (on fname) because you can only have one clustered index
USE [SQL2]
GO
CREATE CLUSTERED INDEX [Idx_PhoneBook_phonebookid]
ON PhoneBook(phonebookid ASC)
Drop index [PhoneBook].[Idx_PhoneBook_lname]
–Can create an single clustered index that has multiple columns
CREATE CLUSTERED INDEX [Idx_PhoneBook_Fname_Lname]
ON [dbo].[PhoneBook](lname ASC,fname ASC) –<< multiple columns
SELECT*
FROM [SQL2].[dbo].[PhoneBook]
–Example of non clustered index
USE [SQL2]
GO
CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_ fname] –<< notice that after creation of a non clustered index, the data is not sorted
ON PhoneBook(fname ASC)
SELECT*
FROM [SQL2].[dbo].[PhoneBook]
–Can create multiple non clustered index (On lname)
USE [SQL2]
GO
CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_phone] –<< second creation of an index and phone
ON PhoneBook(phone ASC)
SELECT*
FROM [SQL2].[dbo].[PhoneBook]