SQL Server Interview Preparation Notes (3-4 Years Experience)
Key Concepts:
1. Indexes:
2. Clustered Index:
◦ Physically sorts table data.
◦ Only one per table.
◦ Example:
CREATE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
3. Non-Clustered Index:
◦ Stores pointers to data.
◦ Multiple indexes allowed.
◦ Example:
CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName);
4. Stored Procedures:
5. Reusable and precompiled SQL commands.
6. Improves performance and security.
7. Example:
CREATE PROCEDURE GetEmployee
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;
1. Triggers:
2. Automatic actions after/instead of database events (INSERT, UPDATE, DELETE).
3. Example (AFTER trigger):
1
CREATE TRIGGER trgUpdateInventory
ON Orders
AFTER INSERT
AS
BEGIN
UPDATE Inventory
SET Quantity = Quantity - inserted.Quantity
FROM Inventory INNER JOIN inserted ON Inventory.ProductID =
inserted.ProductID;
END;
1. Transactions:
2. Ensure data integrity with ACID properties.
3. Example:
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Account SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT TRANSACTION;
1. Performance Tuning:
2. Analyzing execution plans, indexing, and query optimization.
.NET API Interview Preparation Notes (3-4 Years Experience)
Key Concepts:
1. RESTful APIs:
2. Uses standard HTTP methods (GET, POST, PUT, DELETE).
3. Example endpoints:
◦ GET /api/employees
◦ POST /api/employees
4. ASP.NET Core Middleware:
5. Sequentially processes HTTP requests/responses.
6. Example:
2
app.Use(async (context, next) => {
Console.WriteLine("Request incoming");
await next();
Console.WriteLine("Response outgoing");
});
1. Dependency Injection (DI):
2. Inject dependencies into classes to reduce tight coupling.
3. Example:
public class EmployeeController : ControllerBase
{
private readonly IEmployeeService _service;
public EmployeeController(IEmployeeService service)
{
_service = service;
}
}
1. Entity Framework Core:
2. Object-relational mapper (ORM) simplifying database interactions.
3. Example:
public class EmployeeContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
}
1. Authentication & Authorization (JWT):
2. JWT (JSON Web Token) secures APIs by providing encoded tokens.
3. Diagram:
User Login --> Server (Verifies credentials)
<-- Server returns JWT token
User sends JWT token --> Server (validates token)
<-- Server responds with requested resource
3
4. Exception Handling & Logging:
5. Manage errors globally using middleware.
6. Example:
app.UseExceptionHandler(appError =>
{
appError.Run(async context =>
{
context.Response.StatusCode = 500;
await context.Response.WriteAsync("An unexpected error occurred.");
});
});
HTTP Status Codes Explained:
• 200 OK: Request succeeded.
• 400 Bad Request: Client-side error (invalid input).
• 404 Not Found: Requested resource not found.
• 500 Internal Server Error: Server-side error.
Practice:
• Develop sample APIs integrated with SQL databases.
• Regularly optimize and refactor code for performance.
Good luck with your SQL Server and .NET API interview preparation!
