Indexes and Constraints

Question 1

What are the 6 types of indexes?

Answer 1

1- Clustered Index
2- Non-Clustered Index
3- Unique Index
4- Composite Index
5- Covered Index
6- Full-Text Index

Question 2

Definition: “Clustered Index”

Answer 2

This type of index determines the physical order of data in a table. Each table can have only one clustered index. When a clustered index is created, the data in the table is physically reordered to match the order of the index. It is commonly used on columns that are frequently used for sorting or range queries.

Question 3

Definition: “Non-Clustered Index”

Answer 3

Unlike clustered indexes, non-clustered indexes do not determine the physical order of data in a table. Instead, they create a separate structure that contains the indexed column(s) along with a reference to the corresponding row in the table. Non-clustered indexes are useful for improving the performance of search queries.

Question 4

Definition: “Unique Index”

Answer 4

A unique index ensures that the values in the indexed column(s) are unique, meaning no duplicate values are allowed. It is similar to a non-clustered index, but with an additional constraint of uniqueness.

Question 5

Definition: “Composite Index”

Answer 5

A composite index is created on multiple columns of a table. It allows for efficient searching and sorting based on combinations of the indexed columns. Composite indexes can be useful when queries involve multiple conditions that span multiple columns.

Question 6

Definition: “Covered Index”

Answer 6

A covered index includes all the columns required to satisfy a query, thereby eliminating the need to access the actual table. It improves query performance by reducing disk I/O.

Question 7

Definition: “Full-Text Index”

Answer 7

Full-text indexing is used for efficient text-based searching. It enables searching for words or phrases within large amounts of text stored in a column.

Question 8

How many clustered indexes we can have on a table?

Answer 8

It’s important to note that a table can have only one clustered index because the clustered index determines the physical order of the data.

Question 9

How can we set a clustered index?

Answer 9

CREATE CLUSTERED INDEX IDX_DimEmployee_EmpCode ON DimEmployee (EmpCode);

Question 10

How can we update/change a clustered index?

Answer 10

Changing a clustered index involves dropping the existing clustered index and creating a new one:

DROP INDEX IDX_DimEmployee_EmpCode ON DimEmployee;

CREATE CLUSTERED INDEX IDX_DimEmployee_EmpCode ON DimEmployee (EmpCode);

Question 11

How can we delete a clustered index?

Answer 11

DROP INDEX IDX_DimEmployee_EmpCode ON DimEmployee;

Question 12

How can we have a list of indexes?

Answer 12

EXEC sp_helpindex DimEmployee

An example which gives indexes on DimEmployee Table

Question 13

What is the relationship between clustered index and the primary key?

Answer 13

A clustered index is commonly used as the primary key constraint, providing uniqueness and efficient retrieval of data by the primary key.

Question 14

What are the pros of the clustered index? (8)

Answer 14

Physical Data Order
Faster Data Retrieval
Eliminates Index Lookup
Covering Queries
Supports Primary Key Constraint
Improved Sorting
Data Integrity
Optimized Joins

Question 15

What are the cons of the clustered index? (7)

Answer 15

Disk Space
Data Modification Performance
Impact on Insert Performance
Fragmentation
Limited Number of Clustered Indexes
Non-Covering Queries
Inflexible Order

Question 16

How many non-clustered indexes we can have on a table?

Answer 16

In Microsoft SQL Server, the limit on the number of non-clustered indexes per table is 999. This limit includes both unique and non-unique non-clustered indexes. It’s important to note that this limit is for non-clustered indexes specifically and does not include the clustered index, which is limited to one per table.

Question 17

How can we set a non-clustered index?

Answer 17

CREATE INDEX IDX_DimEmployee_EmpName
ON DimEmployee (EmpName);

Question 18

How can we change a non-clustered index?

Answer 18

DROP INDEX IDX_DimEmployee_EmpName ON DimEmployee;
CREATE INDEX IDX_DimEmployee_EmpName
ON DimEmployee (EmpName);

Question 19

What are pros of non-clustered index? (10)

Answer 19

Improved Query Performance
Flexible Index Design
Covering Queries
Efficient Data Modification
Index Joining
Index Scans and Seeks
Query Optimization
Conserve Disk Space
Data Integrity
Multiple Indexes

Question 20

What are cons of non-clustered index? (10)

Answer 20

Increased Disk Space
Impact on Data Modification
Index Maintenance Overhead
Query Plan Complexity
Fragmentation
Data Duplication
Non-Covering Queries
Index Selection Overhead
Index Overhead for Low-Selectivity Columns
Index Management Complexity

Question 21

What does “Data Manipulation” mean?

Answer 21

Insert, Delete, Update

Question 22

What does “Low Selectivity” mean?

Answer 22

Columns with low selectivity are those with a relatively small number of distinct values.

Question 23

What does “Covering Query” mean in the context of indexes?

Answer 23

An index can include all the columns required for a query. In this case, the query does not need to refer to the actual table to run.

Question 24

How can we set a unique index?

Answer 24

CREATE UNIQUE INDEX IDX_DimEmployee ON DimDate (Year, Month, Day)

Question 25

How can we change a unique index?

Answer 25

DROP INDEX IDX_DimEmployee ON DimDate
CREATE UNIQUE INDEX IDX_DimEmployee ON DimDate (Year, Month, Day)

Question 26

What are the pros of unique index? (10)

Answer 26

Enforces Uniqueness
Data Validation
Efficient Data Retrieval
Improved Query Performance
Primary Key Constraint
Foreign Key Constraints
Index Optimization
Implicit Index Coverage
Index-Nested Loop Join
Clustered Index on Unique Column

Question 27

What are the cons of unique index? (10)

Answer 27

Increased Disk Space
Impact on Data Modification
Index Maintenance Overhead
Complexity of Key Selection
Query Plan Complexity
Index Overhead for Low-Selectivity Columns
Index Management Complexity
Limited Uniqueness Scope
Concurrency Issues
Index Naming Conflicts

Question 28

What is the primary key?

Answer 28

The primary key (PK) is a concept in relational databases that identifies a unique identifier for each row in a table. It is a column or a combination of columns that uniquely identifies each record in the table. The primary key serves as a means to ensure data integrity, enforce uniqueness, and provide a way to reference individual rows within the table and in related tables.

Question 29

Key characteristics of a primary key

Answer 29

Uniquenes
Non-Nullability
Persistence
Stability
Single Value or Combination

Question 30

How can we set a primary key while table creation?

Answer 30

CREATE TABLE table_name (
column1 data_type PRIMARY KEY,
column2 data_type,
…
);

Question 31

How can we set a primary key after the table is created?

Answer 31

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1);

Question 32

How can we change a primary key?

Answer 32

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1);

Question 33

How can we get the list of constraints of database?

Answer 33

SELECT * FROM sys.key_constraints

Question 34

How can we set a composite index?

Answer 34

CREATE INDEX IDX_DimEmployee_EmpCodeandName
ON DimEmployee (EmpCode, EmpName);

Question 35

How can we change a composite index?

Answer 35

DROP INDEX IDX_DimEmployee_EmpCodeandName ON DimEmployee;

Question 36

How can we set a covered index?

Answer 36

CREATE INDEX index_name
ON table_name (covered_column1, covered_column2, …);

Question 37

How can we change a covered index?

Answer 37

DROP INDEX index_name ON table_name;
CREATE INDEX index_name
ON table_name (covered_column1, covered_column2, …);

Question 38

How can we set a Full-Text index?

Answer 38

Prerequisites:
CREATE FULLTEXT CATALOG catalog_name AS DEFAULT;
ALTER FULLTEXT INDEX ON table_name SET (KEY INDEX index_name, STOPLIST OFF);

Creation:
CREATE FULLTEXT INDEX ON table_name (column_name)
KEY INDEX index_name
ON catalog_name
WITH (CHANGE_TRACKING = AUTO);

Question 39

How can we change a Full-Text index?

Answer 39

DROP FULLTEXT INDEX ON table_name;
CREATE FULLTEXT INDEX ON table_name (column_name)
KEY INDEX index_name
ON catalog_name
WITH (CHANGE_TRACKING = AUTO);