An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word
information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information withdbmanagement word. By using second method we can save lot of time and we can get information in efficient way.
information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information withdbmanagement word. By using second method we can save lot of time and we can get information in efficient way.
This same principle applies for retrieving data from a database table. Without an SQL Index, the database system reads through the entire table to locate the desired information. If we set the proper index in place, the database system first go through the index to find out where to retrieve the data, and then go to that location directly to get the needed data. This is much faster due to the SQL Index. Creating and removing indexes on table will not show any effect on application because indexes operate behind the scenes.
Syntax to Create SQL Index in table:
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
|
Example to create Index on table
CREATE INDEX SampleIndex ON UserInformation (UserName)
|
The above statement is used to create an index named “SampleIndex” on the “UserName” column in the “UserInformation” table
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
Example of creating SQL Index on multiple columns
CREATE INDEX SampleIndex ON UserInformation (UserName,FirstName)
|
To Drop Index on table use the below statement
DROP INDEX TABLE_NAME.INDEX_NAME
|
In SQL we are having two types of indexes are there
1) Clustered Index
2) Non-Clustered Index
Clustered Index
Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.
Non-Clustered Index
Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values For use on columns with high selectivity
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Option 1: With IGNORE_DUP_KEY set OFF
Once a table is created, create another table and a nonclustered index on it with the option IGNORE_DUP_KEY set OFF. Try to insert data from another table into this table by using the INSERT… SELECT statement.
As there are duplicate values, the insert will fail with error and not insert any single row in the table.
Option 2: With IGNORE_DUP_KEY set ON
Now, once again create new table and create nonclustered index on it with the option GNORE_DUP_KEY set ON. Try to insert data from another table into this table using INSERT… SELECT statement.
Even though there are duplicate values in the table, the rows in the table will be inserted; however, the duplicate values will not be inserted and ignored. A warning message will be generated with the script, but the process will not be stopped.
I hope that this example clarifies the use of this feature. Again, as I said earlier, this is a business logic related issue, and it should be used based on your need.
The complete script from the example is given here.
USE tempdb
GOCREATE TABLE DupData (ID INT,FirstName VARCHAR(100),LastName VARCHAR(100),City VARCHAR(100))GOINSERT INTO DupData (ID,FirstName,LastName,City)VALUES(1,'John','Doe','LA');INSERT INTO DupData (ID,FirstName,LastName,City)VALUES(2,'Joe','Doe','LA');INSERT INTO DupData (ID,FirstName,LastName,City)VALUES(3,'John','Doe','LA');INSERT INTO DupData (ID,FirstName,LastName,City)VALUES(4,'Joseph','Doe','LA');GO-- Check data from Test Table.SELECT *FROM DupData
GO-- Option 1 - With IGNORE_DUP_KEY set OFFCREATE TABLE DupIndex (ID INT,FirstName VARCHAR(100),LastName VARCHAR(100),City VARCHAR(100))GO--Create Unique Index on TableCREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]ON [dbo].[DupIndex]([FirstName] ASC) ON [PRIMARY]
GO-- Insert will throw an error
-- Error will rollback transactionINSERT INTO DupIndex (ID,FirstName,LastName,City)SELECT ID,FirstName,LastName,CityFROM DupDataORDER BY ID
GO-- Table with contain NO rowSELECT *FROM DupIndex
GODROP TABLE DupIndex
GO-- Option 2 - With IGNORE_DUP_KEY set ONUSE tempdb
GOCREATE TABLE DupIndex (ID INT,FirstName VARCHAR(100),LastName VARCHAR(100),City VARCHAR(100))GO--Create Unique Index on Table with IGNORE_DUP_KEY set ONCREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]ON [dbo].[DupIndex]([FirstName] ASC) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
GO-- Insert will throw warning
-- Warning will not insert duplicate rowINSERT INTO DupIndex (ID,FirstName,LastName,City)SELECT ID,FirstName,LastName,CityFROM DupDataORDER BY ID
GO-- Table with contain three rowsSELECT *FROM DupIndex
GO-- Clean upDROP TABLE DupIndexDROP TABLE DupData
GO