Interview Q&A

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))

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"]);

• Vendor Lock-In: Stored procedures use database-specific syntax, making it harder

to migrate to different database platforms.

• 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.

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:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Indexes improve query performance in several ways:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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:

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).

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.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Consider creating an index when:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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.

SQL Server:

LTER INDEX idx_name ON table_name REBUILD;

PostgreSQL:

REINDEX INDEX idx_name;

MySQL:

LTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name

(column_name);

Reorganizing an Index: A lighter operation that compacts the index and defragments it. 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);

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

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

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.
• 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.

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.

Transactions impact concurrent operations by introducing locking mechanisms to ensure

that multiple transactions don't interfere with each other and cause inconsistent data. 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.

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.

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Deadlocks occur when two or more transactions are waiting for each other to release locks,

causing a cycle of dependencies. 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.

In stored procedures, you control transaction behavior using the following:

• BEGIN TRANSACTION: Explicitly starts a transaction in the stored procedure.
• COMMIT: Ends the transaction and commits all changes made during the

transaction.

• ROLLBACK: Undoes all changes made during the transaction if something goes

wrong.

• SAVEPOINT: Sets a point in the transaction to which you can roll back.

Example:

BEGIN TRANSACTION;

• - SQL operations here

IF (some_condition)

BEGIN

COMMIT; -- Commit if condition is true

END

ELSE

BEGIN

ROLLBACK; -- Rollback if condition is false

END

Normalization & Database Design

ddresses a different kind of redundancy or dependency.

• 1NF (First Normal Form):
• A table is in 1NF if it contains only atomic (indivisible) values and each record

has a unique identifier (Primary Key).

• No repeating groups or arrays are allowed.
• 2NF (Second Normal Form):
• A table is in 2NF if it is in 1NF and all non-key attributes are fully

functionally dependent on the primary key.

• Eliminates partial dependency (when a non-key attribute depends on only

part of a composite primary key).

• 3NF (Third Normal Form):
• A table is in 3NF if it is in 2NF and there are no transitive dependencies

(non-key attributes depending on other non-key attributes).

• This removes dependencies between non-key attributes.
• BCNF (Boyce-Codd Normal Form):
• A table is in BCNF if it is in 3NF and every determinant is a candidate key.
• This is a stricter version of 3NF.
• 4NF (Fourth Normal Form):
• A table is in 4NF if it is in BCNF and multi-valued dependencies are

removed.

• This ensures that a record doesn’t have two or more independent

multi-valued attributes.

• 5NF (Fifth Normal Form):
• A table is in 5NF if it is in 4NF and cannot be decomposed further without

losing data.

Normal forms are guidelines used to organize a relational database schema. Each form

addresses a different kind of redundancy or dependency.

• 1NF (First Normal Form):
• A table is in 1NF if it contains only atomic (indivisible) values and each record

has a unique identifier (Primary Key).

• No repeating groups or arrays are allowed.
• 2NF (Second Normal Form):
• A table is in 2NF if it is in 1NF and all non-key attributes are fully

functionally dependent on the primary key.

• Eliminates partial dependency (when a non-key attribute depends on only

part of a composite primary key).

• 3NF (Third Normal Form):
• A table is in 3NF if it is in 2NF and there are no transitive dependencies

(non-key attributes depending on other non-key attributes).

• This removes dependencies between non-key attributes.
• BCNF (Boyce-Codd Normal Form):
• A table is in BCNF if it is in 3NF and every determinant is a candidate key.
• This is a stricter version of 3NF.
• 4NF (Fourth Normal Form):
• A table is in 4NF if it is in BCNF and multi-valued dependencies are

removed.

• This ensures that a record doesn’t have two or more independent

multi-valued attributes.

• 5NF (Fifth Normal Form):
• A table is in 5NF if it is in 4NF and cannot be decomposed further without

losing data.

To normalize a database to 3NF:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Data redundancy occurs when the same piece of data is stored in multiple places, which can lead to inconsistencies and wasted storage. To avoid redundancy:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: pplication? Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.

Answer: Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

What interviewers expect

• A clear definition tied to SQL in SQL & Databases projects
• Trade-offs (performance, maintainability, security, cost)
• When you would and would not use it in production

Real-world example

In a production SQL & Databases application, teams apply this when handling user-facing features or integration boundaries. For example, you might use it during a sprint where reliability and observability matter—logging metrics, validating edge cases, and documenting the decision in an ADR so future developers understand why the approach was chosen.

How to explain in the interview

1. Define the concept in one or two sentences.
2. Context — where it fits in SQL & Databases architecture.
3. Example — a specific project, bug, or performance win.
4. Trade-off — what you gain vs what you sacrifice.

Tip: Practice aloud on Toolliyo mock interview or the Interview Q&A section before your real interview.