The N+1 problem is a common performance issue in programming, especially when dealing with databases and object-relational mapping (ORM) frameworks. It occurs when an application makes one query to fetch a list of items (the “1”) and then makes an additional query for each item in the list (the “N”), resulting in many more…
1. Partitioning Definition:Partitioning is the process of dividing a single database table into smaller, more manageable pieces, called partitions, while still keeping them within the same database. Each partition can be stored separately, often based on some key (like date, region, or ID). Purpose: Improves query performance. Makes management easier (backups, archiving). Can reduce…
In SQL (Structured Query Language), Security and Permissions are mechanisms that control who can access the database and what actions they are allowed to perform.This ensures that sensitive data is protected and only authorized users can read, insert, update, or delete data. 1. Authentication Determines who the user is (login system). Example: In SQL…
🔹 What is a Transaction? A transaction is a sequence of one or more SQL statements executed as a single unit of work.It ensures the ACID properties: Atomicity → all or nothing Consistency → maintains data integrity Isolation → transactions don’t interfere with each other Durability → once committed, changes are permanent 🔹 Transaction…
A view in SQL is a virtual table that is based on the result of a query.It does not store data itself, but rather displays data stored in one or more tables. You can think of a view as a saved query that you can treat like a table. 🔹 Why use Views? To…
A Common Table Expression (CTE) in SQL is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.It is defined using the WITH ... AS syntax. Think of a CTE like a temporary view that exists only for the duration of the query. ✅ Basic Syntax WITH…
🔹 Stored Procedure A Stored Procedure is a collection of SQL statements that you save in the database and can execute multiple times.They can include logic (IF, loops, etc.), accept input/output parameters, and perform actions like inserting, updating, or deleting data. ✅ Syntax: CREATE PROCEDURE procedure_name (parameters) AS BEGIN -- SQL statements END; CREATE…
🔹 Triggers in SQL A trigger is a stored procedure that automatically executes (fires) in response to certain events on a table (like INSERT, UPDATE, or DELETE). Example: Trigger for Audit Logging Suppose you have a table employees and you want to log changes whenever an employee’s salary is updated. -- Create employees table…
In SQL, an index is a database object that improves the speed of data retrieval (SELECT queries) from a table. Think of an index like the index of a book: instead of reading the whole book to find a topic, you can go directly to the indexed page. Similarly, indexes help the database quickly…
🔹 What is a CASE Statement? A CASE statement lets you apply conditional logic in SQL queries — kind of like an if-else in programming languages. Basic Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END…