Q1: What is a SQL Server Index?
A SQL Server index is considered as one of the most important factors in the performance tuning process. Indexes are created to speed up the data retrieval and the query processing operations from a database table or view, by providing swift access to the database table rows, without the need to scan all the table’s data, in order to retrieve the requested data.
Q2: What are the Types of SQL Server indexes?
There are two types of SQL Server Index:
Clustered Index
Non-Clustered Index
In the case of a clustered index, the arrangement of the data in the index table will be the same as the arrangement of the data of the actual table. Example: The index we find the start of a book. When a table has a clustered index then the table is called a clustered table. If a table has no clustered index its data rows are stored in an unordered structure.’ A table can have only one clustered index in it which will be created when the primary key constraint used in a table. A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.
In the case of a non-clustered index, the arrangement of data in the index table will be different from the arrangement of the actual table. A non-clustered index is analogous to an index in a textbook. The data is stored in one place, the index is another place. The index will have pointers to the storage location of the data. Since, the non-clustered index is stored separately from the actual data a table can have more than one non clustered index, just like how a book can have an index by chapters at the beginning and another index by common terms at the end. In the index itself, the data is stored in an ascending or descending order of the index key which does not in any way influence the storage of data in the table. In a table, we can create a maximum of 249 non clustered indexes.
Q3: How many Clustered indexes can be created on a table and why?
SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using only one order criteria
Q4: What are some different ways to create an index?
CREATE INDEX T-SQL statement.
Using SQL Server Management Studio, by browsing the table on which you need to create an index, right click on the Indexes node and choose New Index option.
Indirectly by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statements.
Q5: When should indexes be created:
A column contains a wide range of values.
A column does not contain a large number of null values.
One or more columns are frequently used together in a where clause or a join condition.
Q6: When should indexes be avoided:
The table is small
The columns are not often used as a condition in the query
The column is updated frequently
Q7: 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
Q8: Why is an index described as a double-edged sword?
A well-designed index will enhance the performance of your system and speed up the data retrieval process. On the other hand, a badly-designed index will cause performance degradation on your system and will cost you extra disk space and delay in the data insertion and modification operations. It is better always to test the performance of the system before and after adding the index to the development environment, before adding it to the production environment.
Q9: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?
A Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index.
A clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key.
The heap table can be identified by querying the sys.partitions system object that has one row per each partition with index_id value equal to 0. You can also query the sys.indexes system object also to show the heap table index details, that shows, the id of that index is 0 and the type of it is HEAP.
Q10: What is the difference between OLTP and OLAP workloads and how do they affect index creation decisions?
OLTP | OLAP |
On Online Transaction Processing (OLTP) databases, workloads are used for transactional systems, in which most of the submitted queries are data modification queries. | In contrast, Online Analytical Processing (OLAP) database workloads are used for data warehousing systems, in which most of the submitted queries are data retrieval queries that filter, group, aggregate and join large data sets quickly. |
Uses of OLTP are as follows:
| Uses of OLAP are as follows:
|
The data is used to perform day to day fundamental operations. | The data is used in planning, problem solving and decision making. |
The size of the data is relatively small as the historical data is archived. For ex MB, GB | Large amount of data is stored typically in TB, PB |
Backup and recovery process is maintained religiously | It only need backup from time to time as compared to OLTP. |
Q11: What is the difference between PAD_INDEX and FILLFACTOR?
FILLFACTOR is used to set the percentage of free space that the SQL Server Engine will leave in the leaf level of each index page during index creation. The FillFactor should be an integer value from 0 to 100, with 0 or 100 is the default value, in which the pages will be filled completely during the index creation.
PAD_INDEX is used to apply the free space percentage specified by FillFactor to the index intermediate level pages during index creation.
Q12: What is the main difference between a Clustered and Non-Clustered index structure?
CLUSTERED INDEX | NON-CLUSTERED INDEX |
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore a table can have only one clustered index. | 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 clustered index determines the storage order of rows in the table and hence does not require additional disk space | A non-clustered index is stored separately from the table, additional storage space is required. |
Clustered Index is faster than Non-Clustered Index | Non-Clustered Index is slower than Clustered Index because when Non-Clustered Index is used there us an extra look up from the Non-Clustered Index to the table to fetch the actual rows. |
Q13: What is the difference between index Rebuild and Index Reorganize operations?
Index fragmentation can be resolved by rebuilding and reorganizing SQL Server indexes regularly. The Index Rebuild operation removes fragmentation by dropping the index and creating it again, defragmenting all index levels, compacting the index pages using the Fill Factor values specified in rebuild command, or using the existing value if not specified and updating the index statistics using FULLSCAN of all the data.
The Index Reorganize operation physically reorders leaf level pages of the index to match the logical order of the leaf nodes. The index reorganizes operation will be always performed online. Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.
Q14: Which type of indexes are used to maintain the data integrity of the columns on which it is created?
Unique Indexes, by ensuring that there are no duplicate values in the index key, and the table rows, on which that index is created.
Q15: How could we benefits from a Filtered index in improving the performance of queries?
It uses a filter predicate to improve the performance of queries that retrieve a well-defined subset of rows from the table, by indexing the only portion of the table rows. The smaller size of the Filtered index, that consumes a small amount of the disk space compared with the full-table index size, and the more accurate filtered statistics, that cover the filtered index rows with only minimal maintenance cost, help in improving the performance of the queries by generating a more optimal execution plan.
Q16: What are the different ways that can be used to retrieve the properties of the columns participating in a SQL Server index?
Using SSMS, by expanding the Indexes node under a database tabl, then right-clicking on each index, and choose the Properties option. The problem with gathering the indexes information using the UI method is that you need to browse it one index at a time per each table. You can imagine the effort required to see the article: all indexes in a specific database.
The sp_helpindex system stored procedure, by providing the name of the table that you need to list its indexes. In order to gather information about all indexes in a specific database, you need to execute the sp_helpindex number of time equal to the number of tables in your database.
The sys.indexes system dynamic management view. The sys.indexes contains one row per each index in the table or view. It is recommended to join sys.indexes DMV with other systems DMVs, such as the sys.index_columns, sys.columns and sys.tables in order to return meaningful information about these indexes.
Q17: How could we benefits from a Filtered index in improving the performance of queries?
It uses a filter predicate to improve the performance of queries that retrieve a well-defined subset of rows from the table, by indexing the only portion of the table rows. The smaller size of the Filtered index, that consumes a small amount of the disk space compared with the full-table index size, and the more accurate filtered statistics, that cover the filtered index rows with only minimal maintenance cost, help in improving the performance of the queries by generating a more optimal execution plan.
Q18: How can you find the missing indexes that are needed to potentially improve the performance of your queries?
The Missing Index Details option in the query execution plan, if available.
The sys.dm_db_missing_index_details dynamic management view, that returns detailed information about missing indexes, excluding spatial indexes,
A combination of the SQL Server Profiler and the Database Engine Tuning Advisor tools.
Q19: How is data stored in a database in SQL Server?
SQL Server stores data in it under data pages where a data page is a memory location for storing the information.
A data page will be having a size of 8KB and every 8 data pages we stored under a logical container known as “extend”.
Q20: What is a Composite Index in SQL Server? or What is the advantage of using a Composite Index in SQL Server? or What is Covering Query?
A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes.
If all of the information for a query can be retrieved from an Index then it is called as Covering query. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.
Q21: What is the purpose of the query optimizer in SQL Server?
An important feature of SQL Server is the query optimizer (component). The query optimizer’s job is to find the fastest and least resource-intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task.
Q22: How to create a multi-column index in SQL?
We can create index on combination of columns. The general syntax for creating multi-column index is :
CREATE INDEX index_name
ON TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2,.. COLUMN_NAMEN);
CREATE INDEX idx_emp_name
ON EMPLOYEES (FIRST_NAME, LAST_NAME);Q23: How to create Index for multiple columns?
Syntax:
CREATE INDEX index
ON TABLE (column1, column2,.....);Q24: How to create Unique Indexes?
Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for the fast performance, it does not allow multiple values to enter into the table. Syntax:
CREATE UNIQUE INDEX index
ON TABLE column;Q25: How to remove an Index?
To remove an index from the data dictionary by using the DROP INDEX command.
Syntax:
DROP INDEX index;To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.
Q26: How to alter an Index?
To modify an existing table’s index by rebuilding, or reorganizing the index.
ALTER INDEX IndexName
ON TableName REBUILD;Q27: How to confirm the Indexes?
You can check the different indexes present in a particular table given by the user or the server itself and their uniqueness.
Syntax:
select * from USER_INDEXES;It will show you all the indexes present in the server, in which you can locate your own tables too.
Q28: How to Rename an index?
You can use the system stored procedure sp_rename to rename any index in the database.
Syntax:
EXEC sp_rename
index_name,
new_index_name,
N'INDEX'; The Tech Platform