Window functions compute results across a specified range of rows relative to the current row without collapsing the results into a single output row, unlike aggregate functions used with GROUP BY which condense multiple input rows into a single output row. Window functions allow calculations such as rank, moving averages, and cumulative sums to be performed while keeping the original row data intact for further analysis .

Stored procedures are precompiled collections of one or more SQL statements that act like executable programs in the database, supporting transactions and allowing multiple operations, while user-defined functions are routines that return a value and cannot modify database states. Stored procedures can execute a set of tasks, accept input/output parameters, and typically perform broader tasks involving multiple steps, whereas functions are designed for simple computations and can be used in SELECT, WHERE, or GROUP BY clauses, restricted to returning a single value .

The WHERE clause is used to filter records before any groupings are made, typically working directly with individual rows, whereas the HAVING clause filters records after the group-by operation is performed, making it suitable for conditions on aggregate functions. Consequently, WHERE is used in pre-aggregation filtration, while HAVING serves post-aggregation filtration purposes .

The SQL UNION operator combines result sets of two or more SELECT queries into a single result set, removing duplicate rows in the process. In contrast, UNION ALL selects all rows from each query, including duplicates, which generally results in faster execution because it does not require an additional step to sort and eliminate duplicates. Therefore, UNION is beneficial when distinct results are necessary, while UNION ALL is suitable for performance optimization when duplicate entry removal is not required .

CTEs provide improved readability and organization for complex SQL queries by allowing separation of logical parts into named expressions, which can be easier to write, read, and debug compared to deeply nested subqueries. They support recursive queries which simplify operations that require iteration, such as hierarchical data queries, whereas subqueries do not natively handle recursion and can become difficult to manage as complexity increases .

Temporary tables are created in the tempdb database and are capable of efficient handling of large datasets, permitting indexing and transaction logging, which makes them more suitable for advanced, resource-intensive operations. Table variables, in contrast, are stored in memory with minimal transaction logs, affecting performance on larger datasets as they do not provide index support or statistics update, making them better for smaller datasets and requiring simpler concurrency considerations .

DELETE removes rows one at a time and logs individual row deletions, allowing the possibility of rollback and triggering of delete triggers, thus preserving data integrity. TRUNCATE, while non-logged and faster for large deletions, removes all rows without triggering delete triggers and cannot be reverted if the operation is not part of a transaction block. DROP completely removes a table structure, including data, triggers, and permissions, making rollback impossible and resulting in permanent data loss .

COALESCE follows the SQL standard, making it portable across different SQL dialects, and it can handle multiple arguments, returning the first non-null value from the list provided, offering flexibility beyond the single input argument limitation of ISNULL. ISNULL is specific to certain SQL dialects like SQL Server, where it is implemented for backward compatibility, and typically used for replacing NULLs with a specified value but lacks the versatility that COALESCE provides in complex expressions .

Primary keys uniquely identify each row within a table and enforce entity integrity by not allowing NULL values in the key column(s). They always create a clustered index on the primary key column by default. Unique keys also ensure uniqueness for each row but, unlike primary keys, they allow one NULL value per column and create a non-clustered index. Both keys maintain data integrity, with primary keys serving as the table's primary identifier and unique keys preventing duplicate values .

INNER JOIN returns rows when there is a match in both participating tables. LEFT JOIN returns all rows from the left table, with matching rows in the right table, or NULL if there is no match. RIGHT JOIN behaves inversely, returning all rows from the right table with matching rows from the left table. FULL JOIN combines the results of both LEFT and RIGHT joins, returning all records when there is a match, and filling in NULLs where data is missing on either side .