Top interview questions and answers SQL - Part 2

What are the performance benefits in using the WHERE filter?

You reduce network traffic by filtering in the database server instead of in the client, and you can potentially use indexes to avoid full scans of the tables involved.

What is the form of a filter predicate that can rely on index ordering called?

 A search argument, or SARG, for short.

How do you guarantee the order of the rows in the result of a query? 

The only way to do so is by adding an ORDER BY clause

What is the difference between the result of a query with and one without an ORDER BY clause?

Without an ORDER BY clause, the result is relational (from an ordering perspective); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.

What is the difference between self-contained and correlated subqueries? 

Self-contained subqueries are independent of the outer query, whereas correlated subqueries have a reference to an element from the table in the outer query.

What is the difference between the APPLY and JOIN operators?

 With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with correlations to elements from the left table.

Which set operators does T-SQL support? 

The UNION, INTERSECT, and EXCEPT set operators, as well as the UNION ALL multiset operator.

Name two requirements for the queries involved in a set operator.

The number of columns in the two queries needs to be the same, and corresponding columns need to have compatible types.

What are the clauses that you can use to define multiple grouping sets in the same query?

 GROUPING SETS, CUBE, and ROLLUP.

What is the difference between PIVOT and UNPIVOT? 

PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows

What type of language constructs are PIVOT and UNPIVOT implemented as?

 PIVOT and UNPIVOT are implemented as table operators.

How does SQL Server enforce uniqueness in both primary key and unique constraints? 

 SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.

Can a primary key on one table have the same name as the primary key in another table in the same database?

 No, all table constraints must have unique names in a database.

What is the purpose of the ON clause in the MERGE statement? 

The ON clause determines whether a source row is matched by a target row, and whether a target row is matched by a source row. Based on the result of the predicate, the MERGE statement knows which WHEN clause to activate and as a result, which action to take against the target.

What are the possible actions in the WHEN MATCHED clause? 

 UPDATE and DELETE

How many WHEN MATCHED clauses can a single MERGE statement have?

Two—one with an UPDATE action and one with a DELETE action