MS SQL Advance Interview Question with Answer - Part 1

Here are some advanced Microsoft SQL Server interview questions with answers:

1. What is a clustered index in SQL Server, and how is it different from a non-clustered index?

Answer: A clustered index in SQL Server determines the physical order of data in a table, and the table is stored on disk based on the clustered index key. In other words, when a table has a clustered index, the rows are stored on disk in the order of the clustered index key. On the other hand, a non-clustered index is a separate data structure that stores a copy of a portion of a table's data in a separate location on disk, and it has its own index key. The main difference between a clustered and non-clustered index is that a table can have only one clustered index, but multiple non-clustered indexes.

2. What are the different types of triggers in SQL Server, and when are they used?

Answer: There are three types of triggers in SQL Server:

        ·  DML (Data Modification Language) Trigger: Fires automatically in response to INSERT, UPDATE, or DELETE statements on a table. It can be used for enforcing data integrity, auditing changes, and automating business logic.

      · DDL (Data Definition Language) Trigger: Fires automatically in response to CREATE, ALTER, or DROP statements on a table or database. It can be used for capturing schema changes, auditing DDL events, and controlling database changes.

      · LOGON Trigger: Fires automatically when a user logs on to the SQL Server. It can be used for enforcing security policies, logging user activity, and customizing user sessions.

3. What is a CTE (Common Table Expression) in SQL Server, and why would you use it?

Answer: A CTE (Common Table Expression) in SQL Server is a temporary result set that is defined within a SELECT, INSERT, UPDATE, or DELETE statement and is available for reference within that statement. CTEs provide a way to write complex queries in a more readable and organized manner, and they can be used to simplify complex queries, recursive queries, and self-joins. CTEs are especially useful when you need to reference the same subquery multiple times in a query, as they can improve query performance and code maintainability.

4. What is the purpose of the SQL Server Transaction Log?

Answer: The SQL Server Transaction Log is a file that records all the modifications made to a database, such as INSERTs, UPDATEs, DELETEs, and DDL statements, in a sequential manner. It acts as a safeguard for the database, allowing for recovery in case of system failures or user errors. The Transaction Log also provides the ability to perform transactional operations, such as rolling back or committing changes, and it plays a critical role in ensuring data consistency, durability, and integrity.

5. What is the purpose of the SQL Server Locking Mechanism?

Answer: The SQL Server Locking Mechanism is used to manage concurrent access to shared resources, such as tables, rows, or page-level locks, by multiple transactions. Locks are used to prevent conflicts and maintain data consistency. When a transaction acquires a lock on a resource, other transactions are blocked from modifying or acquiring locks on the same resource until the lock is released. Locking mechanisms are crucial for ensuring data integrity, preventing data corruption, and managing concurrent access in multi-user environments.

6. What are the different types of SQL Server indexes, and when would you use each type?

Answer: There are several types of indexes in SQL Server, including:

     · Clustered Index: Determines the physical order of data in a table and reorganizes the table on disk based on the index key. A table can have only one clustered index, and it is used for optimizing queries that retrieve data in the order of the index key.

    · Non-Clustered Index: A separate data structure that stores a copy of a portion of a table's data in a separate location on disk, with its own index key. A table can have multiple non-clustered indexes, and they are used for optimizing queries that retrieve data based on the index key.

    · Unique Index: Enforces uniqueness of values in one or more columns, and it can be either clustered or non-clustered. It is used for ensuring data integrity and preventing duplicate values in the indexed columns.

   · Filtered Index: An index that includes only a subset of rows based on a filter condition. It is used for optimizing queries that retrieve data based on a specific condition.

  · Spatial Index: An index used for optimizing queries that involve spatial data, such as geographic information system (GIS) data.

7. What is SQL Server Profiler, and how is it used?

Answer: SQL Server Profiler is a tool provided by Microsoft SQL Server that allows you to capture, analyze, and monitor SQL Server events and activities in real-time or from saved trace files. It provides detailed information about queries, stored procedures, and other activities running on the SQL Server, such as duration, CPU usage, reads, writes, and more. SQL Server Profiler is commonly used for performance tuning, troubleshooting, and identifying slow-performing queries, locking issues, and other performance-related problems.

8. What is a SQL Server Execution Plan, and how can it be used for performance tuning?

Answer: A SQL Server Execution Plan is a graphical or text-based representation of how a SQL query is executed by the SQL Server's query optimizer. It shows the sequence of operations and the order in which they are performed, such as table scans, index seeks, joins, sorts, and more. The execution plan provides insight into how a query is processed by the SQL Server and can be used to identify performance bottlenecks, such as missing indexes, table scans, or inefficient query operations. By analyzing the execution plan, you can make informed decisions on how to optimize queries, improve performance, and reduce resource utilization.

9. What is a SQL Server Temporal Table, and how is it used?

Answer: A SQL Server Temporal Table is a type of table that allows you to store and manage historical data changes in a table automatically. It includes system-versioned temporal tables and application-time temporal tables. System-versioned temporal tables automatically keep track of changes to data over time, maintaining historical versions of each row with system-generated start and end dates. Application-time temporal tables allow you to define custom time periods for which data changes are tracked. Temporal tables are useful for auditing, compliance, and tracking changes to data over time without the need for manual data management or historical data replication.

10. What is the purpose of the SQL Server Agent, and how is it used?

Answer: The SQL Server Agent is a built-in SQL Server component that is used for automating administrative tasks, scheduling jobs, and managing alerts and notifications. It provides a way to automate repetitive tasks, such as backup and restore operations, index maintenance, and data synchronization. SQL Server Agent allows you to create and schedule jobs, define steps and dependencies, and monitor the status and results of job executions. It also provides alerting and notification capabilities.

        

MS SQL Advance Interview Question with Answer - Part 2

MS SQL Advance Interview Question with Answer - Part 3

MS SQL Basic Interview Question with Answer