INDEXES
Indexes are used for faster retrieval of data. Also helps to organize /reorganize data at database level.
There are three types of indexes at database level
1. Primary Index
2. Clustered Index
3. Non Clustered Index
Primary Index
Primary Index is used/helpful in mainting unique records and only works on a single field.
ex: Primary key of a table can be created by using primary index.In this case the records are retrieved in faster way from a table when compared with a table which is not having a primary index.
Clustered Index
Clustered Index is used/helpful in mainting the order of data insertion at the table(database level).
ex: Default clustered index is taken as surrogate key(rec id), in this case the records are stored in recid unique number order. If some string field is selected as a clustered index in a table then the records are stored/Inserted in alphabetic order of that string field.
Non Clustered Index
Any table which is created neither with primary nor clustered Index are called as non clustered index.
FULL TEXT INDEX
Full Text Index is used only on a string data type.It helps to index each and every word into a sentence.
ex: If we take a clustered index for a string column which is having a size of 100. And lets enter a data like "My Country Is Best". Then the data is inserted and saved in a alphabetic order. And if i search for a keyword "my" then i'll get result very fast as it is already indexed with alphabetic order. What if i search for "Country" or "Is" or "Best". I'll get the result but my execution time is at toss. It may take time for normal databases, but in sqlserver 2008 if a column is a full text index then each and every word of string column is considered as a sentence, and every word is indexed as a result if we search for any word which is in middle of the paragraph also will be fetched very fast.
Indexes are used for faster retrieval of data. Also helps to organize /reorganize data at database level.
There are three types of indexes at database level
1. Primary Index
2. Clustered Index
3. Non Clustered Index
Primary Index
Primary Index is used/helpful in mainting unique records and only works on a single field.
ex: Primary key of a table can be created by using primary index.In this case the records are retrieved in faster way from a table when compared with a table which is not having a primary index.
Clustered Index
Clustered Index is used/helpful in mainting the order of data insertion at the table(database level).
ex: Default clustered index is taken as surrogate key(rec id), in this case the records are stored in recid unique number order. If some string field is selected as a clustered index in a table then the records are stored/Inserted in alphabetic order of that string field.
Non Clustered Index
Any table which is created neither with primary nor clustered Index are called as non clustered index.
FULL TEXT INDEX
Full Text Index is used only on a string data type.It helps to index each and every word into a sentence.
ex: If we take a clustered index for a string column which is having a size of 100. And lets enter a data like "My Country Is Best". Then the data is inserted and saved in a alphabetic order. And if i search for a keyword "my" then i'll get result very fast as it is already indexed with alphabetic order. What if i search for "Country" or "Is" or "Best". I'll get the result but my execution time is at toss. It may take time for normal databases, but in sqlserver 2008 if a column is a full text index then each and every word of string column is considered as a sentence, and every word is indexed as a result if we search for any word which is in middle of the paragraph also will be fetched very fast.
