What is an Index? Explain its different types in SQL

✅ Index in SQL Server (इंडेक्स इन SQL सर्वर):

Index SQL Server में एक database object (डेटाबेस ऑब्जेक्ट) है जो टेबल के डेटा को जल्दी से प्राप्त करने के लिए उपयोग होता है। यह मुख्य रूप से टेबल के कॉलम्स पर क्रिएट होता है और डेटा को एक क्रमबद्ध तरीके से स्टोर करने में मदद करता है।

Indexes का उपयोग करने से SELECT queries (सेलेक्ट क्वेरीज) की परफॉर्मेंस बढ़ जाती है। लेकिन INSERT, UPDATE, DELETE operations (ऑपरेशन्स) थोड़े धीमे हो सकते हैं क्योंकि इंडेक्स को भी अपडेट करना पड़ता है।

📌 Types of Indexes in SQL Server (SQL Server में इंडेक्स के प्रकार):

1. Clustered Index (क्लस्टर्ड इंडेक्स)
2. Non-Clustered Index (नॉन-क्लस्टर्ड इंडेक्स)
3. Unique Index (यूनिक इंडेक्स)
4. Composite Index (कंपोज़िट इंडेक्स)
5. Full-Text Index (फुल-टेक्स्ट इंडेक्स)
6. XML Index (XML इंडेक्स)
7. Filtered Index (फिल्टर्ड इंडेक्स)
8. Spatial Index (स्पैशल इंडेक्स)

1. Clustered Index (क्लस्टर्ड इंडेक्स)

• Clustered Index में डेटा फिज़िकली sort और store होता है।
• एक टेबल में सिर्फ एक ही क्लस्टर्ड इंडेक्स हो सकता है।

Example (उदाहरण):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Department VARCHAR(50)
);

यहां PRIMARY KEY ऑटोमेटिकली एक Clustered Index बनाता है EmployeeID पर।

2. Non-Clustered Index (नॉन-क्लस्टर्ड इंडेक्स)

• Non-Clustered Index में डेटा फिज़िकली sort नहीं होता, बल्कि एक अलग structure में sorted pointers स्टोर होते हैं।
• एक टेबल में कई नॉन-क्लस्टर्ड इंडेक्स हो सकते हैं।

Example (उदाहरण):

CREATE INDEX idx_EmployeeName
ON Employees (Name);

यह इंडेक्स Name कॉलम पर खोज को तेज़ी से करने के लिए उपयोग होता है।

3. Unique Index (यूनिक इंडेक्स)

• यह डेटा डुप्लिकेशन को रोकता है।
• एक टेबल में कई यूनिक इंडेक्स हो सकते हैं।

Example (उदाहरण):

CREATE UNIQUE INDEX idx_EmployeeEmail
ON Employees (Email);

यहां Email कॉलम में डुप्लिकेट वैल्यूज अलाउ नहीं होंगी।

4. Composite Index (कंपोज़िट इंडेक्स)

• यह एक से ज्यादा कॉलम्स पर इंडेक्स बनाता है।
• Query की परफॉर्मेंस को तब बेहतर करता है जब वही कॉलम्स query में उपयोग किए जाते हैं।

Example (उदाहरण):

CREATE INDEX idx_Name_Department
ON Employees (Name, Department);

यह इंडेक्स तब मदद करेगा जब query में Name और Department दोनों कॉलम्स उपयोग होंगे।

5. Full-Text Index (फुल-टेक्स्ट इंडेक्स)

• यह text-based डेटा पर तेज़ सर्चिंग प्रदान करता है, जैसे CHAR, VARCHAR, TEXT।
• यह मुख्य रूप से वाक्यांश सर्चिंग, शब्द सर्चिंग आदि के लिए उपयोग होता है।

Example (उदाहरण):

CREATE FULLTEXT INDEX ON Employees(Name, Department)
KEY INDEX PK_Employees;

6. XML Index (XML इंडेक्स)

• यह XML डेटा टाइप कॉलम्स पर इंडेक्स बनाने के लिए उपयोग होता है।
• मुख्य रूप से अर्ध-संरचित डेटा को प्रभावी ढंग से खोजने के लिए।

Example (उदाहरण):

CREATE PRIMARY XML INDEX idx_XMLData
ON Employees (XmlColumn);

7. Filtered Index (फिल्टर्ड इंडेक्स)

• यह नॉन-क्लस्टर्ड इंडेक्स का एक ऑप्टिमाइज़्ड वर्शन है, जो सिर्फ विशेष rows को शामिल करता है।
• जब टेबल के कुछ विशेष डेटा पर queries ज्यादा चलती हैं, तो यह उपयोगी है।

Example (उदाहरण):

CREATE INDEX idx_AgeAbove30
ON Employees (Age)
WHERE Age > 30;

8. Spatial Index (स्पैशल इंडेक्स)

• यह भौगोलिक डेटा टाइप्स (Geometry, Geography) पर इंडेक्स क्रिएट करने के लिए उपयोग होता है।
• मुख्य रूप से GIS (Geographic Information System) एप्लिकेशन्स में उपयोग होता है।

Example (उदाहरण):

CREATE SPATIAL INDEX idx_GeoLocation
ON Locations (GeoColumn);

📌 Use of Indexes (इंडेक्स के उपयोग):

1. डेटा को तेज़ी से प्राप्त करने के लिए (SELECT Queries)।
2. JOIN operations की परफॉर्मेंस सुधारने के लिए।
3. Sorting और filtering queries को ऑप्टिमाइज़ करने के लिए।
4. Primary Key और Unique Constraints को enforce करने के लिए।

📌 Important Points to Remember (महत्वपूर्ण बातें):

1. Indexes परफॉर्मेंस को सुधारते हैं, लेकिन ज्यादा इंडेक्स बनाने से DML operations (जैसे INSERT, UPDATE, DELETE) धीमे हो सकते हैं।
2. हमेशा Indexes उन कॉलम्स पर बनाएं जो अक्सर WHERE, JOIN, ORDER BY, और GROUP BY clauses में उपयोग होते हैं।
3. Index को बनाना और बनाए रखना भी परफॉर्मेंस को प्रभावित कर सकता है।

SQL Server में indexes by default तब create होते हैं जब हम PRIMARY KEY या UNIQUE constraints define करते हैं।

📌 By Default Indexes:

1. Clustered Index (क्लस्टर्ड इंडेक्स) - PRIMARY KEY Constraint:
   • जब हम PRIMARY KEY constraint define करते हैं किसी column पर, तो SQL Server उस column पर automatically एक Clustered Index create कर देता है।
   • Data physically sorted होता है।

Example (उदाहरण):

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

यहां पर StudentID column पर Clustered Index automatically create हो गया है, क्योंकि PRIMARY KEY define किया गया है।

2. Non-Clustered Index (नॉन-क्लस्टर्ड इंडेक्स) - UNIQUE Constraint:
   • जब हम UNIQUE constraint define करते हैं किसी column पर, तो SQL Server उस column पर automatically एक Non-Clustered Index create कर देता है।
   • Data physically sorted नहीं होता, बल्कि pointers के through search होता है।

Example (उदाहरण):

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(50) UNIQUE,
    Name VARCHAR(50)
);

यहां पर:

• EmployeeID column पर Clustered Index create होता है (PRIMARY KEY की वजह से)।
• Email column पर Non-Clustered Index create होता है (UNIQUE constraint की वजह से)।

📌 What If We Don't Define Any Index? (अगर हम कोई Index Define ना करें?)

• अगर आप किसी table में कोई PRIMARY KEY या UNIQUE constraint define नहीं करते हैं, तो SQL Server उस table में कोई index by default create नहीं करेगा।
• जैसे:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2)
);

• इस table में कोई index by default create नहीं होता है।

📌 Important Points To Remember (याद रखने वाली बातें):

1. By default, PRIMARY KEY हमेशा Clustered Index बनाता है, अगर पहले से कोई Clustered Index ना हो।
2. UNIQUE constraint हमेशा Non-Clustered Index बनाता है।
3. अगर table में PRIMARY KEY या UNIQUE constraint define नहीं होता है, तो कोई Index by default create नहीं होता है।

Here's a comprehensive list of SQL Server Index-related Interview Questions with Answers from basic to advanced.

📌 Basic Level (Beginner):

1. What is an Index in SQL Server?

Answer:
An Index is a database object that improves the speed of data retrieval operations on a table. It works similarly to an index in a book, allowing quicker access to rows within a table.

2. Why are Indexes used in SQL Server?

Answer:
Indexes are used to:

• Enhance data retrieval speed.
• Improve query performance by reducing search time.
• Enforce uniqueness of data when used with UNIQUE constraints.
• Facilitate sorting and filtering operations.

3. What are the types of Indexes in SQL Server?

Answer:

1. Clustered Index - Physically sorts data in the table, only one per table.
2. Non-Clustered Index - Stores pointers to data, can have multiple per table.
3. Unique Index - Ensures unique values for the indexed column(s).
4. Composite Index - An index created on multiple columns.
5. Full-Text Index - Used for advanced text-based searching.
6. Filtered Index - A non-clustered index that applies to a subset of data.
7. XML Index - Used for XML data type columns.
8. Spatial Index - Optimizes spatial data queries.

4. What is the difference between Clustered and Non-Clustered Indexes?

Answer:

5. How do you create an Index in SQL Server?

Answer:

-- Creating Clustered Index
CREATE CLUSTERED INDEX idx_StudentID ON Students(StudentID);

-- Creating Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Email ON Employees(Email);

📌 Intermediate Level (Intermediate):

6. What is a Composite Index? When should you use it?

Answer:
A Composite Index is an index on two or more columns of a table. It is used when:

• Queries filter or sort data based on multiple columns.
• Columns have a logical relationship (e.g., FirstName and LastName).

Example:

CREATE NONCLUSTERED INDEX idx_Name ON Employees(FirstName, LastName);

7. What is an Included Column Index?

Answer:
An Included Column Index allows non-key columns to be added to a non-clustered index. This helps in covering queries without increasing the index size significantly.

Example:

CREATE NONCLUSTERED INDEX idx_OrderDetails 
ON Orders(OrderID) 
INCLUDE (OrderDate, CustomerID);

8. How do you view all indexes on a table?

Answer:

-- Using system catalog view
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('TableName');

9. What is a Covering Index?

Answer:
A Covering Index is an index that includes all columns needed by a query, so the query can be resolved entirely from the index itself without looking at the base table.

10. What are Index Statistics and why are they important?

Answer:
Index statistics provide distribution details of data in indexed columns, allowing the SQL Server Query Optimizer to create an optimal execution plan.
Use:

UPDATE STATISTICS TableName(IndexName);

📌 Advanced Level (Expert):

11. What is a Filtered Index and when should you use it?

Answer:
A Filtered Index is a non-clustered index applied to a subset of rows in a table using a WHERE clause.
Useful for:

• Improving performance by indexing only relevant data.
• Reducing storage costs.

Example:

CREATE NONCLUSTERED INDEX idx_ActiveEmployees 
ON Employees(EmployeeID)
WHERE Status = 'Active';

12. How do you rebuild and reorganize indexes? What is the difference?

Answer:

• Rebuild: Drops and recreates the index. Used when fragmentation is above 30%.

ALTER INDEX IndexName ON TableName REBUILD;

• Reorganize: Defragments the index without dropping it. Used when fragmentation is 5-30%.

ALTER INDEX IndexName ON TableName REORGANIZE;

13. What is Index Fragmentation? How do you check it?

Answer:
Index Fragmentation occurs when data pages are not stored contiguously.
Check using:

SELECT index_id, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TableName'), NULL, NULL, 'SAMPLED');

14. How do you disable and enable an Index?

Answer:

-- Disable Index
ALTER INDEX IndexName ON TableName DISABLE;

-- Enable Index (Rebuild required)
ALTER INDEX IndexName ON TableName REBUILD;

15. What are the Best Practices for Creating Indexes?

Answer:

• Use Clustered Indexes on Primary Keys.
• Avoid over-indexing; each index adds storage and maintenance overhead.
• Use Filtered Indexes for sparse data.
• Keep indexes small and selective.
• Regularly update statistics.
• Use Included Columns to cover queries efficiently.

16. How does Indexing impact INSERT, UPDATE, and DELETE operations?

Answer:

• INSERT: Can be slower because indexes must be updated.
• UPDATE: Affects performance if columns involved in indexes are modified.
• DELETE: Indexes pointing to deleted rows need to be adjusted.