SQL Day 5 Flashcards

Question

Explain what a CASE statement is and describe its primary usage in T-SQL queries.

Answer 1

A CASE statement in T-SQL is a way to implement conditional logic directly within SQL queries. It allows you to execute different SQL expressions based on specified conditions. The primary usage of a CASE statement is to conditionally modify the output of a query without using multiple queries or external application logic. It's often used in data transformation, data categorization, or implementing business logic directly within the database.

Answer 2

A CASE statement is particularly beneficial when you need to embed conditional logic within a single SQL statement, such as a SELECT, UPDATE, or DELETE. For example, in a report generation scenario where you need to categorize output based on certain thresholds (e.g., categorizing sales into low, medium, and high), using a CASE statement directly in the SELECT clause can streamline the process and improve performance by reducing the need for multiple conditional IF statements or separate queries for each category.

Answer 3

CASE statements can affect performance if used improperly, such as in scenarios where multiple CASE statements evaluate the same expressions repeatedly or when used in predicates that lead to inefficient query plans. Best practices for optimizing CASE statements include avoiding redundant evaluations by simplifying expressions, using them judiciously within predicates to ensure they do not prevent the use of indexes, and carefully managing their placement in SELECT clauses versus WHERE clauses to optimize how and when conditions are evaluated during query execution.

Answer 4

The WHERE clause in SQL is used to filter records from a table to include only those that meet specific conditions. It helps in narrowing down the result set by specifying criteria that the retrieved data must satisfy, making the query more efficient and the data more relevant. It is also used with the UPDATE and DELETE to specify Which Columns are to be manipulated.

Answer 5

Common mistakes include not accounting for NULL values which can lead to unexpected results, misusing logical operators which can alter the logic of the query, and confusing the WHERE and HAVING clauses. It’s crucial to ensure that the conditions within the WHERE clause are correctly stated and logicaly consistent to avoid retrieving incorrect or unintended data.

Answer 6

A unique key constraint in T-SQL ensures that all values in a column, or a set of columns, are unique across the database table. This means no two rows are allowed to have the same value(s) for the columns that the unique key constraint covers, enforcing uniqueness for the data. It also creates a nonclustered index.

Answer 7

Both unique key and primary key constraints ensure data uniqueness, but a primary key constraint additionally enforces that the column(s) cannot accept NULL values. A table can have multiple unique key constraints but only one primary key. Unique keys can be used to enforce uniqueness on non-essential data columns that can accept NULL values. A PK creates a clustered index while a unique key constraint creates a nonclustered index.

Answer 8

Yes, unique key constraints can be temporarily disabled, primarily for performance reasons during large data imports or when bulk updating data where checking constraints would significantly slow down the process. After the data manipulation, the constraint should be re-enabled to ensure the data's integrity. Disabling is done through altering the table structure, but it should be handled with caution to avoid data corruption.

Answer 9

A TRY...CATCH block in T-SQL is used to handle exceptions that occur during the execution of a set of T-SQL statements. The TRY block contains the SQL code that might generate an error, while the CATCH block contains the code that executes if an error occurs. This structure is used to gracefully handle errors, log them, and perform cleanup or recovery steps, preventing the entire script or transaction from failing abruptly.

Answer 10

The TOP clause in T-SQL is used to limit the number of rows returned by a query. It is often used to retrieve a specific quantity of records from a database table or to sample a subset of data from larger result sets. The TOP clause helps in managing large datasets by allowing the focus on a manageable number of records, which is particularly useful in performance tuning and quick data analysis tasks.

Answer 11

A transaction in T-SQL is a sequence of operations performed as a single logical unit of work. It is important because it ensures data integrity and consistency, even in the face of errors, power failures, or other issues. Transactions adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability), which guarantee that all operations within the transaction either complete fully or have no effect at all.

Answer 12

Transactions can lock resources like data rows or tables, potentially leading to blocking and deadlocks, which can degrade database performance. To manage transactions effectively, keep them as short as possible to minimize locking time, use appropriate isolation levels to balance accuracy and performance, and handle exceptions properly to avoid leaving transactions open. Ensuring efficient transaction management helps maintain smooth database operations and enhances concurrency.

Answer 13

An INNER JOIN in T-SQL is used to combine rows from two or more tables based on a related column between them. It returns only those rows that have matching values in both tables, effectively intersecting the datasets.

Answer 14

A LEFT OUTER JOIN in T-SQL returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Answer 15

A RIGHT OUTER JOIN in T-SQL operates similarly to a LEFT OUTER JOIN, but it returns all rows from the right table and the matched rows from the left table. If there is no match, the result will include NULLs on the side of the left table.

Answer 16

A FULL OUTER JOIN in T-SQL returns all rows when there is a match in either the left or right table records. Where there is no match, the result will have NULLs for all columns of the table that does not have a matching row.

Answer 17

A SELF JOIN in T-SQL is a regular join, but the table is joined with itself rather than another table.

Answer 18

A MERGE JOIN in T-SQL is a type of join that requires both input tables to be sorted on the join columns. It is very efficient if the data is already sorted because it merges the rows by walking through each table only once. This join type is ideal for large volume data operations and where indexes that support sorting are in place. In T-SQL, a MERGE JOIN is not explicitly written in SQL syntax but is instead an internal operation that SQL Server's query optimizer may choose based on the query's execution plan. However, if you want to mimic the behavior of a MERGE JOIN, you can write a query that leverages ordered data from two tables,

Answer 19

A Table Valued Function (TVF) in T-SQL is a function that returns a table data type. It can be invoked in the FROM clause of a SELECT statement, similar to how you would use a table. TVFs are useful for encapsulating complex logic that can transform or generate sets of rows, making them reusable across different SQL queries.

Answer 20

There are two main types of Table Valued Functions in T-SQL: Inline Table-Valued Functions (ITVFs) and Multi-Statement Table-Valued Functions (MSTVFs). ITVFs contain a single SELECT statement and cannot have any other kind of SQL logic or variables, making them generally faster and more efficient. MSTVFs, on the other hand, can contain multiple T-SQL statements, declare variables, and perform complex logic before producing a result set, offering more flexibility at the cost of potential performance overhead.

Answer 21

Table Valued Functions enhance SQL query operations by allowing complex operations to be packaged as functions, promoting code reusability and modular architecture. They simplify the management of complex queries and can be optimized by the database engine when used properly. However, their limitations include potential performance drawbacks, especially with MSTVFs, which might not always be optimized as effectively as inline SQL code or stored procedures due to their inherent complexity and the temporary object overhead they can generate.

SQL Day 5 Flashcards

(46 cards)