Interview Q&A

Short answer: Performance: Stored procedures are precompiled, so execution is faster compared to running individual SQL queries each time.

Explain a bit more

Code Reusability: Once defined, stored procedures can be reused in multiple places, reducing redundancy. Security: You can grant users permission to execute a stored procedure without giving them direct access to the underlying tables. Maintainability: Centralizing business logic in stored procedures makes maintenance easier, especially when making changes to the logic. Error Handling: Stored procedures allow you to include error-handling mechanisms like TRY...CATCH in SQL Server or EXCEPTION in PostgreSQL.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: BEGIN SELECT salary FROM employees WHERE id = @emp_id; END; Calling the procedure: EXEC GetEmployeeSalary @emp_id = 101; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END // DELIMITER ; Calling the procedure: CALL GetEmployeeSalary(101); BEGIN SELECT… salary FROM employees WHERE…… id = @emp_id; END; Calling the procedure: EXEC…

Explain a bit more

GetEmployeeSalary @emp_id = 101; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END // DELIMITER ; Calling the procedure: CALL GetEmployeeSalary(101); BEGIN SELECT salary FROM employees WHERE id = @emp_id; END; Calling the procedure: EXEC GetEmployeeSalary @emp_id = 101; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END // DELIMITER ; Calling the procedure: CALL GetEmployeeSalary(101); BEGIN SELECT… salary FROM employees WHERE id = @emp_id; END; Calling the procedure: EXEC GetEmployeeSalary…

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Stored procedures allow you to pass parameters to them, either input or output parameters. Input Parameters: These allow you to send data to the procedure. Output Parameters: These allow the procedure to send data back to the caller.

Example code

SQL Server: CREATE PROCEDURE GetEmployeeSalary (@emp_id INT) AS BEGIN SELECT salary FROM employees WHERE id = @emp_id; END; Calling the procedure: EXEC GetEmployeeSalary @emp_id = 101; MySQL: DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT) BEGIN SELECT salary FROM employees WHERE id = emp_id; END // DELIMITER ; Calling the procedure: CALL GetEmployeeSalary(101);

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Error handling is an essential part of stored procedures.

Explain a bit more

Here’s how you handle errors in different databases: SQL Server: Use TRY...CATCH blocks to handle errors. BEGIN TRY - Some SQL operation INSERT INTO employees (name) VALUES ('John Doe'); END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; PostgreSQL: Use EXCEPTION blocks in PL/pgSQL. BEGIN - Some SQL operation INSERT INTO employees (name) VALUES ('John Doe'); EXCEPTION WHEN others THEN RAISE NOTICE 'Error occurred: %', SQLERRM; END; MySQL: MySQL doesn't have a built-in TRY...CATCH, but you can use DECLARE...HANDLER. DELIMITER // CREATE PROCEDURE ExampleProcedure() BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'An error occurred'; - Some SQL operation INSERT INTO employees (name) VALUES ('John Doe'); END // DELIMITER ;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: You can call stored procedures from programming languages using appropriate database connectors and drivers. Example (C# with SQL Server): using (SqlConnection conn = new SqlConnection(connectionString))

Example code

{ conn.Open(); using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails", conn)) {
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = 101;
using (SqlDataReader reader = cmd.ExecuteReader())
{ while (reader.Read()) { Console.WriteLine(reader["name"]); }
}
}
}

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: wait conn.OpenAsync(); using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = 101; using (SqlDataReader reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) {… Console.WriteLine(reader["name"]); } } } }…… wait conn.OpenAsync(); using (SqlCommand cmd = new…

Explain a bit more

SqlCommand("GetEmployeeDetails", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = 101; using (SqlDataReader reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { Console.WriteLine(reader["name"]); } } } } wait conn.OpenAsync(); using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = 101; using (SqlDataReader reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) {……

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Executing stored procedures asynchronously can improve the performance of applications by allowing other tasks to run while waiting for the procedure to finish. Example (C#): using (SqlConnection conn = new SqlConnection(connectionString))

Example code

{
await conn.OpenAsync();
using (SqlCommand cmd = new SqlCommand("GetEmployeeDetails", conn)) {
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = 101;
using (SqlDataReader reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { Console.WriteLine(reader["name"]); }
}
}
}

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Vendor Lock-In: Stored procedures use database-specific syntax, making it harder to migrate to different database platforms.

Explain a bit more

Complexity: As business logic grows within stored procedures, they can become difficult to maintain and debug. Performance: While stored procedures can be optimized, poorly written ones can hurt performance. Limited Flexibility: Stored procedures are less flexible compared to application code, and they cannot easily handle more complex logic that might be easier in a high-level programming language. Testing: Stored procedures are harder to test and debug in isolation compared to application code.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Debugging stored procedures can be done in the following ways: SQL Server: SQL Server Management Studio (SSMS) allows you to set breakpoints, step through the code, and inspect variable values during execution. Steps:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Indexes improve query performance in several ways:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: There are several types of indexes in SQL: Unique Index: Ensures that all values in the indexed column(s) are unique. Automatically created when a PRIMARY KEY or UNIQUE constraint is defined on a column.

Example code

CREATE UNIQUE INDEX idx_employee_id ON employees(id); Full-Text Index: Used for indexing large text fields. It allows for more advanced searches like full-text searches (e.g., MATCH in MySQL). Example (MySQL): CREATE FULLTEXT INDEX idx_fulltext_desc ON products(description); ● Clustered Index: Defines the physical order of the data in the table based on the index. Each table can have only one clustered index. Non-Clustered Index: An index that stores a separate structure containing the indexed columns and pointers to the actual data rows. A table can have multiple non-clustered indexes. Composite Index: An index that includes multiple columns. Useful for queries that filter based on multiple columns. Spatial Index: Used for indexing spatial data types such as points, lines, and polygons (mostly for geographical data).

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Creating an Index: CREATE INDEX idx_index_name ON table_name(column_name); Dropping an Index: DROP INDEX idx_index_name ON table_name; In SQL Server, dropping an index: DROP INDEX idx_index_name; -- No need to specify table name.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Consider creating an index when:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The query optimizer in the database engine decides which index to use based on several factors:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Index fragmentation occurs when data is inserted, updated, or deleted, causing the index structure to become inefficient. This can negatively impact performance in several ways:

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: LTER INDEX idx_name ON table_name REORGANIZE; PostgreSQL: PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to clean up the database and reduce fragmentation: VACUUM INDEX idx_name; MySQL: OPTIMIZE TABLE table_name; Rebuilding an Index: A more intensive operation where the index is completely dropped nd recreated. This can reduce fragmentation to nearly zero.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Reorganizing an Index: A lighter operation that compacts the index and defragments it.

Explain a bit more

It is used when fragmentation is low (less than 30%). SQL Server: ALTER INDEX idx_name ON table_name REORGANIZE; PostgreSQL: PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to clean up the database and reduce fragmentation: VACUUM INDEX idx_name; MySQL: OPTIMIZE TABLE table_name; Rebuilding an Index: A more intensive operation where the index is completely dropped and recreated. This can reduce fragmentation to nearly zero. SQL Server: ALTER INDEX idx_name ON table_name REBUILD; PostgreSQL: REINDEX INDEX idx_name; MySQL: ALTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name (column_name);

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: For large datasets, query optimization can be crucial to ensure performance is not impacted. Here are several tips:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: transaction are invisible to others until the transaction is committed. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system crash.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The ACID properties are critical to ensuring the reliability of transactions in a database: Atomicity: All operations within a transaction are executed completely or not at all.

Explain a bit more

Consistency: A transaction takes the database from one valid state to another, ensuring that all rules (constraints, triggers, etc.) are respected. Isolation: Transactions are isolated from one another, meaning intermediate steps of a transaction are invisible to others until the transaction is committed. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system crash.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ffects concurrency in several ways: Locking: Transactions may lock rows or tables to prevent conflicting changes, leading to possible delays for other transactions. Deadlocks: When two or more transactions are waiting for each other to release locks, causing a cycle of dependency. This can be automatically detected and resolved by the DBMS.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Transactions impact concurrent operations by introducing locking mechanisms to ensure that multiple transactions don't interfere with each other and cause inconsistent data.

Explain a bit more

This affects concurrency in several ways: Locking: Transactions may lock rows or tables to prevent conflicting changes, leading to possible delays for other transactions. Deadlocks: When two or more transactions are waiting for each other to release locks, causing a cycle of dependency. This can be automatically detected and resolved by the DBMS.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: pplication to reattempt the transaction after a short delay. Optimize Transactions: Keep transactions short and ensure that they acquire locks in the same order to reduce the likelihood of deadlocks.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Deadlocks occur when two or more transactions are waiting for each other to release locks, causing a cycle of dependencies.

Explain a bit more

To handle deadlocks: Deadlock Detection: DBMS systems (e.g., SQL Server) can automatically detect deadlocks and terminate one of the transactions to break the deadlock. Retry Logic: If a deadlock is detected, you can implement a retry mechanism in your application to reattempt the transaction after a short delay. Optimize Transactions: Keep transactions short and ensure that they acquire locks in the same order to reduce the likelihood of deadlocks.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: CTEs are named query scopes (not persisted). Temp tables (#t) live in tempdb, support indexes/statistics, good for larger intermediate sets. Table variables (@t) are lighter but historically weaker stats — fine for small sets. Choose based on size and reuse.

Do not claim CTEs are always “faster” — they are about readability/recursion first.