This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What happened when we create a Primary Key constraint?
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.
What happened when we create a UNIQUE constraint?
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.
Can you create UNIQUE INDEX on a single column , which has NULL value in more than one row?
You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.
What are the benefits of a Unique Index?
Multicolumn unique indexes guarantee that each combination of values in the index key is unique. For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.
Provided that the data in each column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.
Unique indexes ensure the data integrity of the defined columns.
Unique indexes provide additional information helpful to the query optimizer that can produce more efficient execution plans.
When we should consider a Filtered Index in Designing?
When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.
When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.
What are the benefits of the Filtered Index?
- Improved query performance and plan quality.
- Reduced index maintenance costs.
- Reduced index storage costs.
- Online index rebuilds.
What is the advantage of Covered Index?
There can be data types which not allowed as Index key columns called as a nonkey columns.
Using include keyword you can add nonkey columns in the primary index.
An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
During an index reorganization operation, if the index spans multiple files, will pages be allowed to migrate between files.
No: pages will not migrate between files during an index reorganization.
How to make forcefully use an index in a query? Or What table hint needs to be specified to forcefully use an index in a query?
We can specify “Index” table hint in a query to forcefully use an index.
|
1 |
SELECT Emp_ID, Name FROM tbl_Employee WITH(INDEX(idx_tbl_Employee_Name)); |
What kind of data structure is an index?
B- trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is due to the fact that they are time efficient because look-ups, deletions, and insertions can all be done in logarithmic time. And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can be sorted.
Can you create Indexed Views?
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.
What is an Index Scan and Index Seek operation?
What is a Database Engine Tuning Advisor in the SQL Server?
Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.
Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.
How Index partitioning help in improving overall performance?
Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table.
When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes.
What is a Bookmark Lookup operator in SQL Server?
The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index.
Which Index operations do not required additional Disk Space?
- ALTER INDEX REORGANIZE; however, log space is required.
- DROP INDEX when you are dropping a nonclustered index.
- DROP INDEX when you are dropping a clustered index offline without specifying the MOVE TO clause and nonclustered indexes do not exist.
- CREATE TABLE (PRIMARY KEY or UNIQUE constraints)
