INDEX - 1

INDEX - 1

1. What is an Index?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table.

2. What are the various Types of indexes available in SQL Server?

• Clustered
• Non clustered
• Unique
• Column store
• Index with included columns
• Index on computed columns
• Filtered
• Spatial
• XML
• Full-text

3. What is Cluster index?

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

4. What is Non-Cluster index?

A non-clustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the non-clustered index contains the non-clustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

5. What is Unique Index?

A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and non-clustered indexes.

6. What is Column-store index?

 A column-store index stores data in a column-wise (columnar) format, unlike the traditional B-tree structures used for clustered and non-clustered row store indexes, which store data row-wise (in rows). A column-store index organizes the data in individual columns that are joined together to form the index. This structure can offer significant performance gains for queries that summarize large quantities of data, the sort typically used for business intelligence (BI) and data warehousing.

7. What is Index with Included columns?

A non-clustered index that is extended to include non-key columns in addition to the key columns.

8. What is Filtered index?

An optimized non-clustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

9. What is spatial index?

A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

10. What is an XML index?

A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.

11. What is a Full text index?

A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.

12. What is the difference between Cluster and Non Cluster Index?

A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.

Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.

A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.

A table can have multiple non-clustered indexes. But, a table can have only one clustered index.

Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in its leaf nodes.

13. What is a Super Key?

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are subset of Super Keys.

14. What is a Candidate Key?

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

15. What is an Alternate Key?

An Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

16. What is PRIMARY KEY constraint?

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.

17. What is UNIQUE KEY constraint?

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it cannot have duplicate values.

18. What is FOREIGN KEY constraint?

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

Example: We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

19. What is the difference between Primary Key and Unique Key?

Primary Key	Unique Key
Primary Key can’t accept null values.	Unique key can accept only one null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	By default, Unique key is a unique non-clustered index.
We can have only one Primary key in a table.	We can have more than one unique key in a table.
Primary key can be made foreign key into another table.	In SQL Server, Unique key can be made foreign key into another table.

20. By default which index is created when we create a Primary Key column on a table?

By default Primary Key column is created with a Cluster Index.

21. By default which index is created when we create a Unique Key column on a table?

By default Unique Key column is created with a Cluster Index.

22. What are the different index configurations a table can have?

A table can have one of the following index configurations:

• No indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

23. How to create a multi-column index in SQL Server database?

CREATE INDEX name_index

ON Employee (Employee_Name, Department)

24. Explain the structure of Index in SQL server?

An index is structured by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is similar to an upside-down tree, means with the root of the tree at the top, the leaf levels at the bottom, and intermediate levels in between.

Each object in the tree structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.

25. How to Disable Indexes?

--Disable Index

 ALTER INDEX [IndexName] ON TableName DISABLE

 GO

26. How to Enable Indexes?

--Enable Index

 ALTER INDEX [IndexName] ON TableName REBUILD

 GO

27. Can we Insert Data if Clustered Index is disabled?

No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).

28. Why can there be only one Clustered Index and not more than one?

Clustered Index: Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically. Hence there can only be one clustered index per table.

29. What is the Maximum Number of Index per Table?

We can have 1 cluster index and 999 non cluster index on a table.

30. How many maximum columns can be a part of on an index?

Maximum of 16 columns can be a part of an index.

31. What is the Heap?

A heap is a table without a clustered index. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted.