SQL Server interview questions - Index

SQL Server interview questions - Index 


1. What is an index in SQL Server?

  • Answer: An index in SQL Server is a database object that improves the speed of data retrieval operations on a table. It functions similarly to an index in a book, allowing SQL Server to quickly locate rows in a table without scanning the entire table. Indexes can be clustered or non-clustered and are used to speed up queries that involve sorting, filtering, or joining tables.

2. What are the types of indexes in SQL Server?

  • Answer:
    • Clustered Index: This index defines the physical order of data in the table. There can only be one clustered index per table. The primary key by default creates a clustered index.
    • Non-clustered Index: A separate structure from the data table, it contains pointers to the actual data rows. A table can have multiple non-clustered indexes.
    • Unique Index: Ensures that the indexed column(s) have unique values.
    • Full-text Index: Used to perform efficient text searches.
    • Spatial Index: Optimizes queries on spatial data types (geometry or geography).
    • XML Index: Used for indexing XML data types in SQL Server.
    • Filtered Index: A non-clustered index with a WHERE clause to index a subset of data.

3. What is the difference between clustered and non-clustered indexes?

  • Answer:
    • Clustered Index: The data rows are sorted and stored in the order of the clustered index. There can only be one clustered index per table. The primary key constraint creates a clustered index by default.
    • Non-clustered Index: A non-clustered index is a separate structure from the data table that contains pointers to the data rows. A table can have multiple non-clustered indexes.

4. How do you create an index in SQL Server?

  • Answer: To create an index, you can use the CREATE INDEX statement:
  • CREATE NONCLUSTERED INDEX IX_IndexName
  • ON TableName (ColumnName);

Example:

CREATE NONCLUSTERED INDEX IX_Employee_Department

ON Employees(Department);

5. What is a composite index in SQL Server?

  • Answer: A composite index is an index that is created on multiple columns. It can help improve the performance of queries that filter or sort based on multiple columns.
  • CREATE NONCLUSTERED INDEX IX_Employee_Department_Salary
  • ON Employees(Department, Salary);

6. What is the difference between a unique index and a primary key index?

  • Answer:
    • A Primary Key Index is a special type of unique index created automatically when a primary key constraint is defined. It ensures that the values in the indexed column(s) are unique and not null.
    • A Unique Index also enforces uniqueness, but it is not necessarily tied to a primary key constraint. You can create a unique index on any column or set of columns, even if they are not the primary key.

7. Can we create an index on a NULL value in SQL Server?

  • Answer: Yes, SQL Server allows you to create an index on columns that contain NULL values. However, be aware that indexes involving NULL values can behave differently, especially with certain operations like IS NULL or IS NOT NULL.

8. What are the advantages and disadvantages of using indexes?

  • Answer: Advantages:
    • Faster Data Retrieval: Indexes speed up query performance by allowing SQL Server to quickly find data without scanning the entire table.
    • Efficient Sorting: Indexes improve the performance of queries that require sorting or grouping.
    • Improved Joins: Indexes on join columns can improve the performance of JOIN operations.

Disadvantages:

    • Additional Storage: Indexes consume additional disk space.
    • Slower DML Operations: Insert, update, and delete operations can be slower because the indexes need to be updated.
    • Complexity: Too many indexes can complicate the database schema and cause maintenance challenges.

9. What is index fragmentation? How can you resolve it?

  • Answer:
    • Fragmentation occurs when the index pages are not contiguous, which can lead to inefficient query performance.
    • You can resolve fragmentation by rebuilding or reorganizing the index:
    • -- Rebuild an index
    • ALTER INDEX IX_Employee_Department ON Employees REBUILD;
    •  
    • -- Reorganize an index
    • ALTER INDEX IX_Employee_Department ON Employees REORGANIZE;
    • Rebuild is a more intensive operation and should be used for high fragmentation, whereas reorganize is a lighter operation suitable for low to moderate fragmentation.

10. What is a filtered index in SQL Server?

  • Answer: A filtered index is a non-clustered index that is built on a subset of data, defined by a WHERE clause. It is useful for improving query performance on selective queries that access a small portion of a table. Example:
  • CREATE NONCLUSTERED INDEX IX_ActiveEmployees
  • ON Employees(EmployeeStatus)
  • WHERE EmployeeStatus = 'Active';

11. How do you check the existing indexes on a table in SQL Server?

  • Answer: You can query the sys.indexes system view to view the indexes:
  • SELECT *
  • FROM sys.indexes
  • WHERE object_id = OBJECT_ID('TableName');

12. What is the impact of creating too many indexes on a table?

  • Answer: Creating too many indexes on a table can lead to:
    • Slower Write Operations: Insert, update, and delete operations become slower because indexes need to be updated with each modification.
    • Increased Storage: More indexes require more disk space.
    • Maintenance Complexity: Managing multiple indexes can become complex and may result in unnecessary overhead during database maintenance.

13. What are some best practices for indexing in SQL Server?

  • Answer:
    • Index the columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.
    • Avoid indexing columns that change frequently, as they can cause performance overhead during write operations.
    • Use composite indexes for queries that filter on multiple columns.
    • Regularly check for index fragmentation and rebuild or reorganize indexes as necessary.
    • Drop unused or redundant indexes to save storage and improve performance.
    • Consider using filtered indexes for queries that work on subsets of data.

14. What is the difference between REBUILD and REORGANIZE in SQL Server?

  • Answer:
    • REBUILD: This operation completely rebuilds the index, dropping and recreating it. It is more resource-intensive and can be done when fragmentation is high.
    • REORGANIZE: This operation defragments the index by reorganizing the pages without completely rebuilding it. It is lighter and can be used for low to moderate fragmentation.

15. What is an included column in an index?

  • Answer: An included column is a column that is added to the index as a non-key column. It improves performance by covering queries that access those columns. These columns are stored in the leaf level of the index but are not part of the key. Example:
  • CREATE NONCLUSTERED INDEX IX_Employee_Name
  • ON Employees(EmployeeID)
  • INCLUDE (EmployeeName);


Comments

Post a Comment

Popular posts from this blog

Multiline to singleline IN C# - CODING

Multiline to singleline IN C# - CODING using System; using System.Linq; class Program {     static void Main(string[] args)     {         string input = @"This is                  multiline";                  // Split the input into lines, trim each line, and filter out empty lines         string result = string.Join(" ", input.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)                                                .Select(line => line.Trim()));         Console.WriteLine(result);     } } Output: This is  multiline
Read more

EF Core interview questions for beginners

1. What is Entity Framework Core (EF Core)? EF Core is an open-source Object-Relational Mapper (ORM) that enables .NET developers to work with databases using .NET objects. It allows you to interact with a database using strongly typed classes and LINQ queries, rather than writing raw SQL queries. EF Core supports various database providers, such as SQL Server, SQLite, PostgreSQL, MySQL, and more. 2. What are the advantages of using EF Core over traditional ADO.NET? Productivity:  EF Core allows you to work with databases using high-level objects, reducing the amount of boilerplate code you need to write. Cross-platform:  EF Core supports multiple databases and works on Windows, Linux, and macOS. LINQ Support:  You can query the database using LINQ, which is more type-safe and easier to maintain than raw SQL queries. Code-First & Database-First:  EF Core supports both approaches to model databases. Change Tra...
Read more

EF Core interview questions for experienced

EF Core interview questions for experienced 1. What are the main differences between Entity Framework (EF) and Entity Framework Core (EF Core)? EF Core is a complete rewrite of EF. Unlike EF, EF Core supports cross-platform development, improved performance, LINQ query translation, and has a more modular architecture. EF Core does not support all features that EF 6 does (like lazy loading and some database providers). 2. How does EF Core handle migrations, and what are some common pitfalls? EF Core uses migrations to update the database schema based on model changes. Common pitfalls include not applying migrations correctly, misalignment between model and database state, or missing/overwriting migrations during collaboration in a team environment. 3. Explain the difference between Add and Attach methods in EF Core. Add method is used to add a new entity to the context and mark it as Added , while Attach attaches an existing entity to the context with no changes. The Attach meth...
Read more