SQL Server 2019 is the latest version of Microsoft's flagship relational database management system. It comes with several new features and enhancements compared to its predecessor, SQL Server 2017. In this article, we will explore some of the key interview questions and answers related to SQL Server 2019, focusing on the new features that were not present in SQL Server 2017.

SQL Server 2019 - New Features

• Big Data Clusters

  SQL Server 2019 introduces Big Data Clusters, which allow seamless integration with Apache Spark and Hadoop Distributed File System (HDFS). This enables processing and analyzing large-scale data sets and provides data virtualization, data lakes, and big data analytics within SQL Server.

• Data Virtualization

  With Data Virtualization, SQL Server 2019 enables integration with external data sources like Oracle, MongoDB, and CSV files without physically moving the data. External tables can be created to query and join data from these sources within SQL Server, simplifying data integration and analysis.

• Intelligent Query Processing (IQP)

  SQL Server 2019 incorporates Intelligent Query Processing features such as Batch Mode on Rowstore, Memory Grant Feedback, and Approximate Query Processing. These optimize query performance by providing efficient execution plans and memory allocation, resulting in faster query processing.

• Accelerated Database Recovery (ADR)

  ADR is a significant feature in SQL Server 2019 that significantly reduces the time required to recover a database after a failure or unexpected shutdown. It maintains a version store in TempDB, allowing quick rollback to a prior state without requiring a full recovery process.

• Memory-Optimized TempDB Metadata

  SQL Server 2019 improves performance by storing TempDB metadata in memory-optimized tables, reducing contention and enhancing overall performance, especially in scenarios where TempDB usage is high.

• Always On Availability Groups Enhancements

  SQL Server 2019 introduces Read-Scale Availability Groups, enabling offloading of read-only workloads to secondary replicas, resulting in improved performance for read-heavy scenarios. Additionally, SQL Server 2019 supports Availability Groups on Kubernetes, providing high availability and disaster recovery capabilities for SQL Server instances running on Kubernetes clusters.

• Always Encrypted with Secure Enclaves

  SQL Server 2019 provides enhanced security with Always Encrypted and secure enclaves. It allows certain operations on encrypted columns without exposing sensitive data to the database engine. Secure enclaves use Intel Software Guard Extensions (SGX) to perform these operations within a secured, isolated environment.

SQL Server 2019 Interview Questions and Answers

Question 1: What are the new features introduced in SQL Server 2019 that were not available in SQL Server 2017?

Answer: SQL Server 2019 introduced several new features that were not present in SQL Server 2017, including Big Data Clusters, Accelerated Database Recovery, Intelligent Query Processing, Enhanced PolyBase, Java Language Extensions, Support for UTF-8 characters, and Scalar UDF Inlining.

Question 2: Explain the concept of Big Data Clusters in SQL Server 2019.

Answer: Big Data Clusters is a new feature in SQL Server 2019 that allows you to deploy scalable clusters of SQL Server, Apache Spark, and Hadoop Distributed File System (HDFS) containers running on Kubernetes. It enables you to combine large volumes of relational data with unstructured and big data, making it easier to analyze and derive valuable insights from diverse data sources. This feature was not available in SQL Server 2017.

Question 3: What is Accelerated Database Recovery (ADR)? How does it improve database availability and reliability?

Answer: Accelerated Database Recovery is a new feature in SQL Server 2019 that significantly improves database availability and reliability by reducing the time taken to recover a database after a failure. In SQL Server 2017, when a database recovery process occurred after a crash, it could take a considerable amount of time, especially for large databases.

ADR introduces a new logging and recovery process that shortens the recovery time by maintaining a version store for each database transaction. This allows the system to quickly revert to the most recent consistent state, reducing downtime and improving overall database availability.

Question 4: How does Intelligent Query Processing enhance query performance in SQL Server 2019?

Answer: Intelligent Query Processing is a set of performance-enhancing features introduced in SQL Server 2019. Some of these features include Batch Mode Adaptive Joins, Batch Mode Memory Grant Feedback, and Scalar UDF Inlining (Introduced in SQL Server 2019 CU5).

These features optimize query execution plans and adapt to changing runtime conditions, resulting in improved query performance. SQL Server 2017 does not have these query processing enhancements, making SQL Server 2019 more efficient in handling complex queries.

Question 5: Compare the enhancements in PolyBase between SQL Server 2017 and SQL Server 2019.

Answer: In SQL Server 2017, PolyBase had certain limitations, such as supporting only delimited text files. However, in SQL Server 2019, PolyBase has undergone significant improvements. It now supports various file formats, including Apache Parquet, ORC, JSON, and more. Additionally, PolyBase in SQL Server 2019 allows you to create external tables without requiring an external data source. These enhancements make data integration and querying capabilities more robust and flexible compared to SQL Server 2017.

Question 6: What are the benefits of the Java Language Extensions feature in SQL Server 2019?

Answer: The Java Language Extensions is a new feature in SQL Server 2019 that enables you to execute Java code within the database. This feature enhances the extensibility of SQL Server, allowing developers to implement custom functionality using Java, alongside T-SQL.

With Java Language Extensions, you can call Java code from T-SQL routines, such as stored procedures, functions, and triggers. This capability was not available in SQL Server 2017, making SQL Server 2019 more versatile and accommodating for developers with mixed language requirements.

Question 7: Explain the support for UTF-8 characters in SQL Server 2019.

Answer: SQL Server 2019 introduced support for the UTF-8 character encoding, which is particularly beneficial when dealing with international data. In previous versions like SQL Server 2017, the database used a fixed-width character encoding (UTF-16), which could result in increased storage requirements for certain character sets.

By supporting UTF-8, SQL Server 2019 enables more efficient storage of characters from various languages, as many characters can be represented using a single byte in UTF-8. This feature helps reduce storage space and enhances data handling capabilities in multilingual environments.

Question 8: How does SQL Server 2019's Intelligent Query Processing feature impact performance for queries involving table variables?

Answer: In SQL Server 2019, the Intelligent Query Processing feature extends its optimizations to queries that involve table variables. Prior to this version, queries using table variables were treated as static and had suboptimal execution plans.

With the new version, table variables can benefit from better cardinality estimates and join optimizations, leading to improved query performance.

Question 9: What is the significance of the "Approximate Count Distinct" feature introduced in SQL Server 2019?

Answer: The "Approximate Count Distinct" feature is an enhancement to the traditional "Count Distinct" operation in SQL Server 2019. Unlike the exact count, which can be resource-intensive for large datasets, the approximate count distinct uses an algorithm called HyperLogLog to estimate the number of distinct values in a column.

This can lead to much faster query execution times for distinct count operations on large datasets, making it an attractive option for performance-critical scenarios.

Question 10: How does the "Persistent Memory" feature in SQL Server 2019 contribute to better performance and data durability?

Answer: SQL Server 2019 introduces support for Persistent Memory (PMEM) technology, which provides a non-volatile memory medium with low-latency access. By leveraging PMEM, the database engine can optimize I/O operations and reduce the overhead of writing data to disk.

This results in improved overall database performance and enhanced data durability, as data can be quickly persisted to a more reliable medium, even in the event of a system failure.

Question 11: In SQL Server 2019, what are the benefits of the "Always Encrypted with Secure Enclaves" feature?

Answer: "Always Encrypted with Secure Enclaves" is a new security feature in SQL Server 2019 that allows sensitive data to be encrypted and processed within a secure enclave. This secure enclave is an isolated environment within the database server where encryption keys and decryption operations are performed.

As a result, even database administrators with high-level access cannot access the plaintext data, providing an added layer of data security and confidentiality.

Question 12: Compare the improvements made to the Online Index Rebuild feature in SQL Server 2019 with SQL Server 2017.

Answer: In SQL Server 2019, the Online Index Rebuild operation has been enhanced to include support for indexes on Memory-Optimized Tables. This means that memory-optimized indexes can now be rebuilt online, allowing for continuous availability of critical workloads during index maintenance.

In SQL Server 2017, the online index rebuild was limited to disk-based indexes only.

Question 13: How does SQL Server 2019 leverage Intelligent Query Processing for batch mode operations?

Answer: SQL Server 2019 introduces batch mode improvements through the Intelligent Query Processing feature. With the introduction of "Batch Mode Adaptive Joins," the query optimizer can dynamically switch between different join algorithms based on runtime data statistics.

This adaptability ensures that the most efficient join algorithm is used, leading to faster query execution and better overall performance for batch mode operations.

Question 14: Explain the advantages of using SQL Server 2019's "Data Discovery & Classification" feature for compliance and data protection.

Answer: The "Data Discovery & Classification" feature in SQL Server 2019 helps organizations in their compliance efforts by automatically classifying sensitive data in the database. By using machine learning, it identifies and tags columns containing sensitive information, such as credit card numbers or personal identification data.

This feature aids in data protection by providing administrators with an overview of their data's sensitivity and enabling them to take appropriate measures to secure the data, ensuring compliance with data protection regulations like GDPR.

Question 15: How does the Accelerated Database Recovery feature improve the rollback process during long-running transactions in SQL Server 2019?

Answer: In SQL Server 2019, Accelerated Database Recovery drastically reduces the time taken for the rollback process during long-running transactions. In previous versions, rolling back large transactions could be a time-consuming operation, resulting in potential performance bottlenecks.

With ADR, the database keeps track of the changes made during the transaction using a persistent version store. During rollback, only the metadata of the changes needs to be reverted, rather than the entire transaction log, leading to much faster rollback times and better overall database performance.

Question 16: How does the "Memory-Optimized TempDB Metadata" feature in SQL Server 2019 enhance TempDB performance?

Answer: SQL Server 2019 introduced the "Memory-Optimized TempDB Metadata" feature to reduce contention on TempDB system metadata structures. With this enhancement, the metadata structures are stored in memory-optimized tables, eliminating latch contention and improving overall TempDB performance, especially in scenarios with heavy TempDB usage.

Question 17: Explain the benefits of using the "Resumable Index Rebuild" feature in SQL Server 2019.

Answer: The "Resumable Index Rebuild" feature allows you to pause and resume index rebuild operations, providing more flexibility and control during maintenance tasks. If an index rebuild operation is interrupted due to a system failure or resource constraint, it can be resumed from where it left off, rather than starting the entire process again. This capability minimizes downtime during index maintenance and improves the manageability of large index operations in SQL Server 2019.

Question 18: In SQL Server 2019, how does the "Scalar UDF Inlining" feature impact the performance of scalar user-defined functions?

Answer: In SQL Server 2019, the "Scalar UDF Inlining" feature automatically optimizes and transforms scalar user-defined functions (UDFs) into the calling query's execution plan. This transformation eliminates the need for separate function calls, resulting in improved performance by reducing the overhead associated with UDF invocations.

It effectively eliminates the performance penalty previously incurred when using scalar UDFs in SQL Server 2017 and improves query execution times.

Question 19: What are the advantages of using SQL Server 2019's "Intelligent Insights" for monitoring and performance tuning?

Answer: "Intelligent Insights" is a new feature in SQL Server 2019 that provides intelligent performance monitoring and tuning recommendations. It uses built-in intelligence and machine learning capabilities to analyze database performance and identify potential bottlenecks or areas for improvement.

By leveraging these insights, database administrators can proactively address performance issues, optimize query execution plans, and improve overall database performance more effectively than manual tuning methods in SQL Server 2017.

Question 20: How does the "Batch Mode on Rowstore" feature improve analytical query performance in SQL Server 2019?

Answer: In SQL Server 2019, the "Batch Mode on Rowstore" feature extends the benefits of batch processing to rowstore-based queries. Previously, batch processing was limited to columnstore indexes.

With this enhancement, certain types of rowstore-based queries can now take advantage of batch processing, resulting in faster analytical query performance for workloads that predominantly use rowstore tables.

Question 21: Compare the improvements made to the SQL Server Machine Learning Services in SQL Server 2019 with SQL Server 2017.

Answer: SQL Server 2019 enhances the Machine Learning Services by introducing support for the latest Python and R versions, enabling data scientists to use the latest features and libraries available in these languages.

Additionally, SQL Server 2019 introduces the "Java Language Extensions" feature, which allows you to execute Java code within the database engine. In contrast, SQL Server 2017 had limited support for Python and R and did not offer the Java Language Extensions feature.

Question 22: How does the "Data Virtualization" feature in SQL Server 2019 simplify data integration and querying across multiple data sources?

Answer: "Data Virtualization" is a new capability in SQL Server 2019 that allows you to create virtual tables that reference data from external data sources, such as other SQL Server instances, Oracle databases, or Azure SQL Database.

These virtual tables can be queried using standard T-SQL, which simplifies the integration and querying process, eliminating the need to replicate or move data physically. This feature improves data accessibility and streamlines the data integration process compared to SQL Server 2017.

Question 23: Explain how the "Memory-Optimized Temporal Tables" feature in SQL Server 2019 improves the performance of temporal tables.

Answer: In SQL Server 2019, the "Memory-Optimized Temporal Tables" feature allows you to store temporal tables in memory-optimized filegroups.

By doing so, historical and current versions of rows in temporal tables can be efficiently managed using memory-optimized data structures. This improves the overall performance of queries involving temporal tables and significantly reduces the I/O overhead that was present in SQL Server 2017 when working with temporal data.

Question 24: What are the benefits of using the "Intelligent Database Approximation Algorithms" feature in SQL Server 2019?

Answer: The "Intelligent Database Approximation Algorithms" feature is a part of the Intelligent Query Processing enhancements in SQL Server 2019. It improves the cardinality estimation for complex queries, enabling the query optimizer to make more accurate choices when creating query plans.

This leads to better query performance and execution times, especially in scenarios where complex joins and filters are involved, compared to SQL Server 2017's cardinality estimation capabilities.

Question 25: How does the "UTF-8 Support for VARCHAR and CHAR Data Types" feature in SQL Server 2019 enhance storage efficiency and collation flexibility?

Answer: In SQL Server 2019, the "UTF-8 Support for VARCHAR and CHAR Data Types" feature allows you to store VARCHAR and CHAR data in the UTF-8 character encoding format, in addition to the existing UTF-16 format.

This improves storage efficiency, as UTF-8 can represent certain characters using fewer bytes compared to UTF-16. Furthermore, the feature enhances collation flexibility by enabling the use of different collations for VARCHAR and CHAR columns without impacting the database's default collation, making it easier to handle multilingual data efficiently.

In conclusion, SQL Server 2019 introduces a wide range of innovative features and enhancements that significantly improve database performance, security, and data integration capabilities. These new features, not available in SQL Server 2017, pave the way for more efficient and robust database management.

Big Data Clusters bring together the power of SQL Server, Apache Spark, and Hadoop to enable seamless data integration and analysis from various sources, empowering organizations to gain deeper insights from their data.

Accelerated Database Recovery (ADR) revolutionizes database availability and reliability by dramatically reducing recovery times, minimizing downtime, and enhancing overall system resilience.

Intelligent Query Processing empowers SQL Server 2019 with adaptive query optimization, allowing the database engine to make smarter choices and deliver faster query execution.

Enhancements in PolyBase offer greater flexibility by supporting various file formats, making data integration across multiple sources smoother and more efficient.

The introduction of the Java Language Extensions feature opens up new possibilities for developers to implement custom functionality using Java alongside T-SQL, catering to diverse language requirements.

The support for UTF-8 characters not only enhances storage efficiency but also accommodates multilingual data, making SQL Server 2019 a more inclusive platform for global data handling.

Furthermore, the introduction of features like Always Encrypted with Secure Enclaves, Data Discovery & Classification, and Resumable Index Rebuild strengthens data security, compliance, and index maintenance capabilities.

SQL Server 2019's focus on memory-optimized solutions, including Memory-Optimized TempDB Metadata and Memory-Optimized Temporal Tables, enhances performance and scalability for specific workloads.

Intelligent Insights, Batch Mode on Rowstore, and Intelligent Database Approximation Algorithms refine performance monitoring, analytical query processing, and query optimization, raising the bar for database performance and management.

Lastly, the Data Virtualization feature simplifies data integration, reducing data movement and redundancy, thereby streamlining data querying across various sources.

With these advancements and cutting-edge features, SQL Server 2019 establishes itself as a leading database management system, surpassing its predecessors like SQL Server 2017. Embracing SQL Server 2019 empowers organizations to harness the full potential of their data, elevate data security, and elevate performance, thus setting the stage for future growth and success.

As SQL Server continues to evolve, staying up-to-date with the latest features and enhancements becomes crucial for database administrators and developers to harness its full potential, unlock valuable insights, and ensure optimal performance. Embracing SQL Server 2019's new capabilities enables organizations to position themselves at the forefront of data-driven decision-making, fostering a competitive edge in the ever-changing landscape of data management.