Topic: Index in SQL
Index in SQL are used to retrieve data from the database more quickly than compared to without it. They are similar to textbook indexes: they enable the database to find data faster without reading the whole table or database.
Indexes are special lookup tables that the database search engine uses to perform tasks quickly. Essentially, they provide a fast way for the system to look up data based on the values within those fields. An index in a database is very similar to an index in the back of a book.
There are several types of indexes in SQL, including Unique Index, Clustered Index, Non-Clustered Index, etc. Each of these serves a particular goal and can be employed under specific conditions to optimize database performance.
Interview Questions:
1. What is an Index in SQL and why is it used?
Answer: An Index in SQL is used to speed up the retrieval of records on a database table. Similar to a textbook index, it allows the system to find the data without reading the entire database table. It creates an entry for each value and hence it will be faster to retrieve data.
2. Can you explain the difference between Clustered and Non-Clustered Index?
Answer: A Clustered Index determines the physical order of data in a table. Each table can only have one clustered index because the data rows themselves can only be sorted in one order. On the other hand, a Non-Clustered Index is a special type of index where the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a non-clustered index do not consist of the data pages. Instead, the leaf nodes consist of index pages.
3. What is a Unique Index in SQL?
Answer: A Unique Index ensures that the index key contains no duplicate values and therefore every entry in the index is unique. The purpose of the unique index is to ensure the data integrity of the columns which it is applied to.
4. How can we create an Index in SQL?
Answer: We can create an index in SQL using the CREATE INDEX statement. Here is an example:
`CREATE INDEX index_name
ON table_name(column_name);`
5. When should indexing be avoided in SQL?
Answer: Indexing should be avoided when the table is small, that is, a table which don’t have many rows. It is also not recommended to use indexing on columns which have a high number of NULL values or columns where there are not many distinct values.
