fbpx

Top 100 SQL DBA Interview Questions and Answers

Top 100 SQL DBA Interview Questions and Answers
Contents show

1. What is the role of an SQL DBA?

Answer:
An SQL DBA is responsible for managing and maintaining databases. This includes tasks like installation, configuration, monitoring, performance tuning, backup and recovery, security management, and ensuring data integrity.


2. How do you optimize a slow-running query?

Answer:
One approach is to create appropriate indexes on columns used in the WHERE and JOIN clauses. Additionally, optimizing the query’s structure and avoiding excessive use of functions can improve performance.

-- Example: Creating an index
CREATE INDEX idx_name ON table_name(column1, column2);

3. Explain the purpose of a clustered index.

Answer:
A clustered index determines the physical order of data in a table. It reorders the table based on the indexed columns. Each table can have only one clustered index because the data rows themselves are stored in the order defined by the clustered index.


4. How do you perform a database backup in SQL Server?

Answer:
Use the BACKUP DATABASE command. Here’s an example for a full database backup:

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\YourBackupPath\YourBackupName.bak';

5. Explain the difference between a primary key and a unique key.

Answer:
A primary key is used to uniquely identify each record in a table. It doesn’t allow NULL values and there can only be one primary key per table. A unique key, on the other hand, enforces uniqueness but allows for one NULL value.


6. What is a deadlock in SQL Server?

Answer:
A deadlock occurs when two or more processes are waiting for each other to release a resource, resulting in a standstill. SQL Server detects deadlocks and automatically chooses one process as the victim to release resources.


7. How do you monitor SQL Server performance?

Answer:
Use tools like SQL Server Management Studio (SSMS) reports, Performance Monitor, and Dynamic Management Views (DMVs) to track metrics like CPU usage, memory utilization, and query performance.

-- Example: Query to get top resource-consuming queries
SELECT TOP 10 total_worker_time/execution_count AS Avg_CPU_Time,
    execution_count,
    total_elapsed_time/execution_count as AVG_Run_Time,
    (SELECT [text] FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC;

8. What is the purpose of a transaction log in SQL Server?

Answer:
The transaction log records all transactions and modifications to the database. It ensures data integrity and allows for point-in-time recovery. It’s crucial for activities like backups and replication.


9. How do you rebuild an index in SQL Server?

Answer:
Use the ALTER INDEX statement with the REBUILD option. This rebuilds an existing index.

ALTER INDEX IX_IndexName ON TableName REBUILD;

10. Explain the purpose of the NOLOCK hint in SQL Server.

Answer:
The NOLOCK hint allows a query to read a table even if it’s currently locked by another process. While it can improve query performance, it may lead to reading uncommitted data.

SELECT * FROM TableName WITH (NOLOCK);

11. What is SQL Server Agent and what is its purpose?

Answer:
SQL Server Agent is a component of SQL Server that allows for the automation of tasks, such as database backups, maintenance plans, and executing SQL Server jobs at specific intervals.


12. How do you monitor the growth of a database?

Answer:
You can use the sys.master_files system view to monitor the growth of database files. The size and growth columns provide information about file size and auto-growth settings.

-- Example: Query to get file size and growth settings
SELECT name AS FileName, size/128 AS FileSize_MB, growth/128 AS AutoGrowth_MB
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabaseName');

13. Explain the purpose of SQL Profiler.

Answer:
SQL Profiler is a tool used to monitor and analyze SQL Server events. It captures events such as queries, stored procedures, and errors, providing insights into database activity.


14. How do you handle database corruption in SQL Server?

Answer:
In the event of database corruption, restore the database from a known good backup. If no valid backup is available, consider using third-party tools for recovery.


15. What is the purpose of the CHECKDB command?

Answer:
The CHECKDB command is used to check the logical and physical integrity of all objects in a database. It identifies and repairs any integrity issues it encounters.

-- Example: Running CHECKDB command
DBCC CHECKDB('YourDatabaseName');

16. Explain the difference between a database role and a fixed database role.

Answer:
A database role is a group of database users to which specific permissions can be assigned. A fixed database role is a pre-defined role with specific permissions (e.g., db_datareader, db_datawriter) and cannot be modified.


17. How do you implement database mirroring in SQL Server?

Answer:
Database mirroring involves creating and managing two copies of a single database to increase database availability. It requires setting up a principal server, a mirror server, and a witness server.


18. What is the purpose of a tempdb database in SQL Server?

Answer:
tempdb is a system database in SQL Server used to store temporary objects like temporary tables and indexes. It’s a shared resource used for various operations across multiple users and sessions.


19. How do you restore a database in SQL Server?

Answer:
Use the RESTORE DATABASE command. Here’s an example of restoring a database from a backup file:

RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\YourBackupPath\YourBackupName.bak';

20. What is the purpose of SQL Server Logins?

Answer:
SQL Server Logins are used to authenticate and authorize users to access SQL Server. They can be either Windows logins (authenticated by the Windows OS) or SQL Server logins (authenticated by SQL Server).


21. How do you perform a backup and restore operation using T-SQL?

Answer:
To perform a backup, you can use the BACKUP DATABASE command. Here’s an example:

-- Backup database
BACKUP DATABASE YourDatabaseName TO DISK = 'C:\YourBackupPath\YourBackupName.bak';

For restoration:

-- Restore database
RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\YourBackupPath\YourBackupName.bak';

22. Explain the purpose of the SQL Server Agent service account.

Answer:
The SQL Server Agent service account is used to run SQL Server Agent jobs. It requires appropriate permissions to execute tasks such as executing T-SQL code, accessing files, and interacting with the OS.


23. What is a clustered index in SQL Server?

Answer:
A clustered index determines the physical order of data in a table. Each table can have only one clustered index, which is used for storage and retrieval of data.


24. How do you monitor the performance of a SQL Server instance?

Answer:
You can use SQL Server Management Studio (SSMS) reports, dynamic management views (DMVs), and third-party monitoring tools to monitor performance. For example:

-- Query to get top CPU consuming queries
SELECT TOP 10 qs.sql_handle, qs.execution_count, qs.total_worker_time,
    qs.total_worker_time/qs.execution_count AS [Avg CPU Time],
    t.text AS [SQL Text]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

25. Explain the purpose of SQL Server Profiler templates.

Answer:
Profiler templates are pre-defined sets of events and data columns that can be saved and reused for specific profiling tasks. They allow for the customization of the information gathered during a trace.


26. What is a deadlock in SQL Server?

Answer:
A deadlock occurs when two or more processes are unable to proceed because each holds a lock that the other process needs to complete its operation.


27. How do you optimize a query in SQL Server?

Answer:
Optimizing a query involves various steps, such as creating appropriate indexes, avoiding unnecessary columns, and rewriting complex queries for better performance.

-- Example: Creating an index
CREATE INDEX IX_Employee_LastName ON Employee(LastName);

28. Explain the purpose of the SQL Server Recovery Model.

Answer:
The Recovery Model determines how SQL Server manages transaction logs. There are three types: Simple, Full, and Bulk-Logged. It impacts backup and restore operations.


29. How do you handle a full transaction log in SQL Server?

Answer:
You can perform a transaction log backup to truncate the log file and free up space. Here’s an example:

-- Transaction log backup
BACKUP LOG YourDatabaseName TO DISK = 'C:\YourBackupPath\YourLogBackupName.trn';

30. What is SQL Server Integration Services (SSIS)?

Answer:
SSIS is a platform for building high-performance data integration and workflow solutions. It includes packages to solve complex business problems by copying or downloading files, extracting and transforming data from different data sources, and loading data.


31. How do you troubleshoot a slow-running query in SQL Server?

Answer:

  1. Identify the query: Use tools like SQL Server Profiler to identify the specific query causing the issue.
  2. Check execution plan: Use SET STATISTICS IO ON and SET STATISTICS TIME ON to see how many reads and how much time the query takes.
  3. Indexes: Ensure appropriate indexes exist. Use the Database Engine Tuning Advisor to get recommendations.
  4. Check for blocking: Use sp_who2 or sys.dm_exec_requests to identify if the query is blocked.
  5. Update statistics: Outdated statistics can lead to suboptimal query plans.

32. What is the purpose of a SQL Server Agent job?

Answer:
A SQL Server Agent job is a specified series of operations that can be scheduled, monitored, and automated. It’s used for tasks like database maintenance, backups, and other routine activities.


33. Explain the difference between a clustered and a non-clustered index.

Answer:

  • Clustered Index: Determines the physical order of data in a table. A table can have only one clustered index.
  • Non-Clustered Index: Creates a separate structure for the index, containing a sorted list of references to the table’s rows. A table can have multiple non-clustered indexes.

34. How do you handle database corruption in SQL Server?

Answer:

  1. Identify the corruption: Use DBCC CHECKDB to identify the corrupt objects.
  2. Restore from backup: If possible, restore the affected database from a clean backup.
  3. Repair the database: Use DBCC CHECKDB with appropriate repair options, but be cautious as it can result in data loss.
  4. Contact Microsoft Support: For severe cases, contacting Microsoft Support may be necessary.

35. Explain the purpose of SQL Server Profiler.

Answer:
SQL Server Profiler is a tool that allows DBAs and developers to monitor and analyze events in SQL Server. It helps in understanding the behavior of the SQL Server instance, identifying performance bottlenecks, and troubleshooting issues.


36. How do you handle a transaction that needs to be rolled back?

Answer:
Use a TRY...CATCH block to handle exceptions. If an error occurs, issue a ROLLBACK TRANSACTION to undo the changes made during the transaction.

BEGIN TRY
    BEGIN TRANSACTION;
    -- Your code here
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Handle the error
END CATCH

37. Explain the purpose of the SQL Server Database Engine Tuning Advisor.

Answer:
The Database Engine Tuning Advisor (DTA) is a tool that helps optimize the performance of databases. It analyzes a workload and recommends ways to improve query performance, such as creating indexes or modifying existing ones.


38. How do you implement database mirroring in SQL Server?

Answer:

  1. Set up endpoints: Create endpoints on both principal and mirror servers.
  2. Backup and restore: Take a full backup of the principal database and restore it on the mirror server.
  3. Configure mirroring: Set up mirroring with the ALTER DATABASE command.
  4. Monitor mirroring: Use SQL Server Management Studio (SSMS) to monitor the status.

39. What is a deadlock in SQL Server, and how can it be resolved?

Answer:
A deadlock occurs when two or more processes are unable to proceed because each is holding a lock that the other process needs. This leads to a standstill.

Resolution:

  1. Identify deadlocks: Use tools like SQL Server Profiler or Extended Events to capture deadlock information.
  2. Analyze the deadlock graph: Understand which processes are involved and which resources they’re trying to access.
  3. Modify the code: Restructure queries, add proper indexing, or change isolation levels to minimize the likelihood of deadlocks.

40. Explain the purpose of SQL Server Maintenance Plans.

Answer:
Maintenance Plans are SQL Server objects used to define the database administration tasks to be performed, such as backups, integrity checks, and index maintenance. These plans can be scheduled to run at specific times or intervals.


41. How do you perform a backup and restore using T-SQL commands?

Answer:
To perform a backup:

-- Full backup
BACKUP DATABASE [DatabaseName] TO DISK = 'C:\Backup\BackupFile.bak';

To perform a restore:

-- Full restore
RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Backup\BackupFile.bak' WITH REPLACE;

42. What are the advantages of using stored procedures in SQL Server?

Answer:

  1. Improved performance: Stored procedures are precompiled, which can lead to faster execution times.
  2. Security: They can be encrypted, providing an additional layer of security for sensitive logic.
  3. Code reusability: They can be called from multiple places in an application.

43. What is SQL Server Always On Availability Groups?

Answer:
Always On Availability Groups is a high-availability and disaster recovery solution in SQL Server. It allows you to replicate a set of user databases to secondary replica servers, providing failover support and read-only access to secondary databases.


44. Explain the purpose of the SQL Server Agent Alert system.

Answer:
The SQL Server Agent Alert system notifies operators about events defined in the system. This can include notifications about job completion, errors, or specific custom conditions.


45. What is Transparent Data Encryption (TDE) in SQL Server?

Answer:
TDE is a feature that encrypts the entire database at rest. It encrypts data files, log files, and backup files. This ensures that sensitive data is protected, even if the physical media is stolen.


46. How do you monitor SQL Server performance?

Answer:

  1. Use performance counters: Monitor metrics like CPU usage, memory usage, and disk activity.
  2. Review execution plans: Identify expensive queries and optimize them.
  3. Use dynamic management views (DMVs): Access information about SQL Server’s internal state.
  4. Set up alerts: Configure alerts for specific performance thresholds.

47. What is the purpose of SQL Server Profiler?

Answer:
SQL Server Profiler is a graphical user interface that allows DBAs and developers to monitor and analyze SQL Server activities. It captures events such as query executions, logins, and deadlock events, providing valuable insights for performance optimization and troubleshooting.


48. Explain the concept of Database Mirroring in SQL Server.

Answer:
Database Mirroring is a high-availability and disaster recovery solution in SQL Server. It involves two copies of a single database that reside on different computers. One server acts as the principal database, and the other serves as the mirror. It provides automatic failover in case of a principal database failure.


49. What is SQL Server Integration Services (SSIS)?

Answer:
SSIS is a part of SQL Server’s Business Intelligence suite used for solving complex business problems by copying or downloading files, extracting and transforming data from different data sources, and loading data into one or more destinations.


50. Explain the purpose of Log Shipping in SQL Server.

Answer:
Log Shipping is a high-availability and disaster recovery solution that involves automatically backing up and restoring transaction logs to maintain a secondary copy of a database. This secondary copy can be used for reporting or as a failover option in case of primary database failure.


51. What is the purpose of SQL Server Extended Events?

Answer:
Extended Events is a lightweight performance monitoring system that can be used to monitor and troubleshoot the SQL Server Database Engine. It provides a highly scalable and customizable framework for collecting different types of performance data.


52. What is the purpose of SQL Server Agent Jobs?

Answer:
SQL Server Agent Jobs are a set of tasks or a series of tasks that can be scheduled to run at specific times or at specific intervals. They are used for automating administrative tasks, like backups, index maintenance, or report generation.


53. Explain the concept of Database Snapshot in SQL Server.

Answer:
A Database Snapshot is a read-only, static view of a database as it existed at a particular point in time. It provides a consistent view of the data even if changes are being made to the original database.


54. What is the purpose of SQL Server Resource Governor?

Answer:
Resource Governor is a feature that enables you to manage SQL Server workload and system resource consumption by specifying limits on resource consumption by incoming requests.


55. Explain the concept of Page Splits in SQL Server.

Answer:
A page split occurs when a new row is inserted into a full data page in an index. SQL Server must make room for the new row, which may require splitting the page into two pages.


56. What is the purpose of SQL Server Analysis Services (SSAS)?

Answer:
SQL Server Analysis Services (SSAS) is an analytical data processing and data mining tool used for creating and managing OLAP cubes. It allows users to analyze multidimensional data interactively, perform advanced analytics, and create data mining models.


57. Explain the concept of Transparent Data Encryption (TDE) in SQL Server.

Answer:
Transparent Data Encryption is a feature that helps protect sensitive data by encrypting the database files at rest. It performs real-time I/O encryption and decryption of the data and log files, making it transparent to applications and users.


58. What is the purpose of SQL Server Failover Clustering?

Answer:
SQL Server Failover Clustering is a high-availability solution that provides redundancy in case of hardware or software failure. It involves two or more servers (nodes) that share a common set of disks (shared storage) and are configured to work together.


59. Explain the purpose of Always On Availability Groups in SQL Server.

Answer:
Always On Availability Groups is a high-availability and disaster recovery solution that provides a set of options for achieving redundancy and failover at the database level. It allows you to create a group of user databases that fail over together.


60. What is the purpose of SQL Server Replication?

Answer:
SQL Server Replication is a data distribution and synchronization tool that allows you to copy and distribute data and database objects to other servers and locations. It is used for improving scalability, availability, and performance.


61. Explain the concept of Database Consistency Checking (DBCC) in SQL Server.

Answer:
DBCC is a set of statements and commands used for database maintenance and verification. It includes various operations like checking the logical and physical integrity of a database, repairing corrupt data, and performing other maintenance tasks.


62. What is the purpose of SQL Server PolyBase?

Answer:
PolyBase is a feature in SQL Server that allows you to query relational and non-relational data using Transact-SQL. It enables seamless integration of SQL Server with big data environments like Hadoop.


63. Explain the concept of SQL Server Data Compression.

Answer:
SQL Server Data Compression is a feature that allows you to reduce the size of your database by using different compression algorithms. It helps save storage space and can lead to performance improvements.


64. What is the purpose of SQL Server In-Memory OLTP?

Answer:
In-Memory OLTP is a memory-optimized database engine integrated into SQL Server. It improves the performance of OLTP workloads by moving data from disk-based tables to memory-optimized tables, allowing for faster data access.


65. Explain the purpose of SQL Server Extended Events.

Answer:
Extended Events is an event-handling system used for monitoring and troubleshooting SQL Server. It provides a lightweight and efficient way to collect detailed information about various operations and events happening within the database engine.


66. What is the purpose of SQL Server Resource Governor?

Answer:
Resource Governor is a feature in SQL Server used to manage and allocate system resources (CPU and memory) among different workloads or applications. It allows you to prioritize and control resource usage based on predefined policies.


67. Explain the concept of SQL Server Buffer Pool Extension.

Answer:
Buffer Pool Extension is a feature that allows you to extend the buffer pool cache of SQL Server into a solid-state drive (SSD). This helps improve performance by providing additional space for caching frequently accessed data.


68. What is the purpose of SQL Server Query Store?

Answer:
Query Store is a feature introduced in SQL Server 2016 that helps track and analyze query performance over time. It captures execution plans, runtime statistics, and other relevant information, allowing for better query optimization and troubleshooting.


69. Explain the concept of SQL Server Accelerated Database Recovery (ADR).

Answer:
Accelerated Database Recovery is a feature introduced in SQL Server 2019 that improves database recovery time after a crash or failover. It uses a combination of versioning and logging techniques to speed up the recovery process.


70. What is the purpose of SQL Server Intelligent Query Processing (IQP)?

Answer:
Intelligent Query Processing is a set of features introduced in SQL Server to improve query performance and optimize execution plans. It includes enhancements like batch mode on rowstore, table variable deferred compilation, and more.


71. Explain the concept of SQL Server Temporal Tables.

Answer:
Temporal Tables are a feature introduced in SQL Server 2016 that allow you to keep track of the history of data changes in a table. They provide an easy way to query and analyze data as it existed at different points in time.


72. What is the purpose of SQL Server Automatic Tuning?

Answer:
Automatic Tuning is a set of features in SQL Server that helps optimize database performance by automatically applying performance improvements. It can include tasks like creating and dropping indexes, and fixing query plans.


73. What is Transparent Data Encryption (TDE) in SQL Server?

Answer:
Transparent Data Encryption is a feature in SQL Server that encrypts the entire database at the file level, including data files, log files, and backups. It provides an extra layer of security by ensuring that data is encrypted at rest.


74. Explain Always On Availability Groups in SQL Server.

Answer:
Always On Availability Groups is a high-availability and disaster recovery feature in SQL Server. It allows you to create a group of databases that will fail over together. This provides redundancy and minimizes downtime during planned or unplanned outages.


75. What is the purpose of SQL Server Database Mail?

Answer:
Database Mail is a feature in SQL Server that allows you to send email messages directly from the database engine. It is useful for sending alerts, notifications, reports, and other automated messages.


76. Explain the concept of SQL Server FileStream.

Answer:
FileStream is a feature in SQL Server that allows the storage and management of unstructured data (such as documents, images, etc.) on the file system while maintaining transactional consistency with the database.


77. What is the purpose of the SQL Server Import and Export Wizard?

Answer:
The Import and Export Wizard is a graphical tool provided by SQL Server Management Studio (SSMS) that allows you to easily transfer data between databases or from external sources to SQL Server.


78. Explain the concept of Linked Servers in SQL Server.

Answer:
Linked Servers allow you to establish connections between different instances of SQL Server or other data sources. This enables you to run distributed queries, perform data replication, and access resources from remote servers.


79. What is the purpose of SQL Server Policy-Based Management?

Answer:
Policy-Based Management is a feature in SQL Server that allows you to define and enforce policies for managing and monitoring database objects and settings. It helps maintain compliance with organizational or industry-specific rules.


80. Explain the concept of SQL Server In-Memory OLTP.

Answer:
In-Memory OLTP is a feature introduced in SQL Server 2014 that enables the creation of memory-optimized tables and stored procedures. This improves the performance of high-concurrency and high-throughput workloads.


81. Explain the concept of SQL Server Resource Governor.

Answer:
Resource Governor is a feature in SQL Server that enables you to manage and allocate system resources (CPU, memory, and I/O) among different workloads or groups of users. It helps in controlling and prioritizing resource usage.


82. What is the purpose of SQL Server Extended Events?

Answer:
Extended Events is an event-handling system in SQL Server that provides a lightweight and flexible framework for monitoring and troubleshooting database activity. It allows you to capture and analyze events at a very granular level.


83. Explain the significance of SQL Server Plan Guides.

Answer:
Plan Guides are used to influence the query optimizer’s choice of execution plan for specific queries. They provide a way to override the default behavior of the optimizer, which can be useful in certain scenarios.


84. What is SQL Server FileTable?

Answer:
FileTable is a feature introduced in SQL Server 2012 that builds on top of FileStream. It enables you to store and manage files and documents directly in the database, while still providing Windows file system compatibility.


85. Explain the concept of SQL Server Stretch Database.

Answer:
Stretch Database is a feature introduced in SQL Server 2016 that allows you to extend a database to Azure for archival purposes. It provides a seamless and cost-effective way to store and access historical data.


86. What is the purpose of the SQL Server Query Store?

Answer:
The Query Store is a feature in SQL Server that helps in tracking and analyzing query performance over time. It stores execution plans and query statistics, allowing you to identify and address performance issues.


87. Explain the concept of SQL Server Change Data Capture (CDC).

Answer:
Change Data Capture is a feature in SQL Server that captures and tracks changes made to tables. It allows you to identify and process incremental changes, which is particularly useful for data warehousing and synchronization scenarios.


88. What is the purpose of SQL Server Replication?

Answer:
Replication is a set of technologies in SQL Server that allows you to copy and distribute data and database objects to one or more destinations. It is used for scalability, availability, and data distribution purposes.


89. What is Transparent Data Encryption (TDE) in SQL Server?

Answer:
Transparent Data Encryption is a feature in SQL Server that encrypts the data at rest to secure sensitive information. It encrypts the database files on disk, making it more difficult for unauthorized users to access the data.


90. Explain the concept of SQL Server Database Mail.

Answer:
Database Mail is a feature in SQL Server that allows you to send email messages directly from the database engine. It’s commonly used for sending notifications, alerts, and reports from SQL Server.


91. What is SQL Server Always On Availability Groups?

Answer:
Always On Availability Groups is a high-availability and disaster recovery solution introduced in SQL Server. It allows you to provide redundancy and failover capabilities for a set of user databases, ensuring high availability.


92. Explain the concept of SQL Server Linked Servers.

Answer:
Linked Servers in SQL Server allow you to connect to external data sources, such as other SQL Server instances or different database platforms, from within SQL Server. This enables you to perform distributed queries and transactions.


93. What is the purpose of SQL Server Service Broker?

Answer:
Service Broker is a messaging framework in SQL Server that enables you to build scalable and asynchronous message-based applications. It facilitates communication between different components or services within a database.


94. Explain the concept of SQL Server Database Snapshots.

Answer:
Database Snapshots in SQL Server provide a read-only, point-in-time view of a database. They are useful for reporting purposes and as a backup strategy for certain scenarios.


95. What is the purpose of SQL Server Resource Database?

Answer:
The Resource Database is a read-only system database in SQL Server that contains all the system objects and metadata required by the database engine. It’s a fundamental part of the SQL Server instance.


96. Explain the concept of SQL Server In-Memory OLTP.

Answer:
In-Memory OLTP, also known as Hekaton, is an in-memory processing technology introduced in SQL Server 2014. It allows you to create memory-optimized tables and natively compiled stored procedures for significantly improved performance.


97. What is the purpose of SQL Server Policy-Based Management?

Answer:
Policy-Based Management in SQL Server allows you to define and enforce policies for database objects and configurations. It helps in ensuring compliance with organizational standards and best practices.


98. Explain the concept of SQL Server Full-Text Search.

Answer:
Full-Text Search is a feature in SQL Server that enables you to perform advanced text-based searches on large amounts of unstructured data. It’s useful for applications that require powerful search capabilities.


99. What is the purpose of the SQL Server Agent service?

Answer: The SQL Server Agent service is responsible for automating and scheduling administrative tasks, such as backups, database maintenance, and other jobs in SQL Server. It plays a crucial role in managing and maintaining the SQL Server environment.


100. Explain the concept of SQL Server Extended Events.

Answer: Extended Events is a powerful event-handling system introduced in SQL Server to capture a wide range of events, like queries, errors, and performance metrics. It provides a lightweight and flexible method for monitoring and troubleshooting SQL Server instances.