Stored procedures encapsulate multiple SQL queries and control flows, improving performance through compiled execution and enabling reuse and modularization of code. Functions return a result from given inputs, suitable for computations and reducing client-to-server round trips. Both enhance DBMS efficiency, enforce business logic, and promote consistent execution in complex operations, but they can increase server-side load if not carefully optimized .

RANK() assigns a unique rank number, with gaps after duplicates, based on ORDER BY choice; DENSE_RANK() eliminates gaps in rankings by incrementing consecutively; ROW_NUMBER() provides a sequential integer for each row, useful for pagination. Use RANK() when differentiation with gaps is acceptable, DENSE_RANK() for uninterrupted rank values, and ROW_NUMBER() for ordering rows uniquely .

Window functions enhance SQL by allowing calculations across rows related to the current row without collapsing data sets like aggregate functions. PARTITION BY divides the result set into partitions, applying functions like RANK() over each, while ORDER BY determines row order within partitions. For example, using RANK() OVER (PARTITION BY department ORDER BY salary DESC) ranks employees by salary within each department .

ACID properties ensure reliable transactions through four key principles: Atomicity ensures operations within a transaction are completed entirely or not at all, Consistency guarantees the database remains in a valid state before and after transactions, Isolation manages concurrent transactions leaving the database state as if they were executed sequentially, and Durability ensures completed transactions persist despite system failures. They are crucial for maintaining data integrity and preventing data corruption .

Indexes improve query performance by reducing data access time. Clustered indexes sort the data storage itself, affecting whole rows, ideal for range queries. Non-clustered indexes hold pointers to data rather than the data itself, suitable for queries involving specific columns. Trade-offs include additional storage usage, increased complexity during data modification operations, and the potential to slow down insertions, updates, and deletions due to index maintenance .

DELETE removes rows based on a condition and can be rolled back, TRUNCATE removes all rows without logging individual row deletions and is faster but less flexible, and DROP deletes the table structure itself, removing all data and cannot be rolled back. DELETE impacts the data, TRUNCATE impacts data with less granularity, and DROP affects both data and table structure .

Sharding involves dividing a database into smaller, horizontally partitioned databases across different servers, enhancing scalability by distributing load and storage. Partitioning involves dividing a database table into parts within a single database instance, improving performance through indexing and quicker access paths. Sharding is exceptional for handling large distributed systems, while partitioning is ideal for performance within a single system .

UNION combines results from multiple SELECT statements into a single result set, removing duplicates, making it suitable for scenarios requiring unique results. UNION ALL also combines results but retains duplicate entries, optimizing performance by avoiding duplicate elimination and is faster when duplicates are necessary or acceptable .

Strategies include using indexes efficiently, optimizing join operations, minimizing the use of subqueries through CTEs, avoiding SELECT *, leveraging query caching, breaking complex queries into simpler parts, analyzing query plans using EXPLAIN, and regular database maintenance like updating statistics. In data engineering, focusing on ETL optimizations and managing data volumes effectively is critical .

Normalization involves organizing database tables to reduce redundancy and improve data integrity by dividing tables and establishing relationships. Denormalization combines tables to reduce joins and enhance read performance at the expense of redundancy. Normalization is chosen for data integrity and reducing update anomalies, while denormalization is used for faster query performance in read-heavy applications .