SQL Server Interview Questions for All Levels

Okay, here’s a comprehensive article on SQL Server Interview Questions, covering a wide range of topics and levels of difficulty, aiming for approximately 5000 words:

SQL Server Interview Questions for All Levels: A Comprehensive Guide

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It’s a core component of many enterprise applications, making SQL Server skills highly sought after in the IT industry. This guide provides a comprehensive overview of SQL Server interview questions, categorized by difficulty level (Beginner, Intermediate, Advanced) and topic. It’s designed to help both interviewers create effective assessments and candidates prepare thoroughly.

I. Beginner Level Questions (0-2 Years of Experience)

These questions focus on fundamental SQL concepts, basic syntax, and common operations. Candidates should be able to write simple queries and understand the core principles of relational databases.

A. Basic SQL Syntax and Concepts:

What is SQL? What is a database? What is a relational database?
- SQL (Structured Query Language): A standard language for managing and manipulating data in relational database management systems.
- Database: An organized collection of structured information, or data, typically stored electronically in a computer system.
- Relational Database: A type of database that stores and provides access to data points that are related to one another. Data is organized into tables with rows (records) and columns (fields/attributes). Relationships between tables are defined using keys.
What are the different data types in SQL Server? Give examples.
- INT (INTEGER): Whole numbers (e.g., 10, -5, 0).
- BIGINT: Larger whole numbers.
- SMALLINT: Smaller whole numbers.
- TINYINT: Very small whole numbers (0-255).
- DECIMAL(p, s) / NUMERIC(p, s): Fixed precision and scale numbers (e.g., 123.45, where p is the total number of digits and s is the number of digits after the decimal point).
- FLOAT(n): Approximate-number data types for use with floating point numeric data. n indicates the number of bits used to store the mantissa.
- REAL: A synonym for FLOAT(24).
- VARCHAR(n): Variable-length character strings (e.g., “Hello”, “SQL Server”). n is the maximum length.
- NVARCHAR(n): Variable-length Unicode character strings (supports international characters).
- CHAR(n): Fixed-length character strings (padded with spaces if the string is shorter than n).
- NCHAR(n): Fixed-length Unicode character strings.
- TEXT: Large variable-length character strings (deprecated in favor of VARCHAR(MAX)).
- NTEXT: Large variable-length Unicode strings (deprecated in favor of NVARCHAR(MAX)).
- DATE: Stores a date (year, month, day).
- TIME: Stores a time (hours, minutes, seconds, fractions of a second).
- DATETIME: Stores both date and time.
- DATETIME2: A more precise version of DATETIME.
- SMALLDATETIME: A less precise version of DATETIME.
- DATETIMEOFFSET: Stores date and time with time zone awareness.
- BIT: Stores a single bit of data (0, 1, or NULL).
- BINARY(n): Fixed-length binary data.
- VARBINARY(n): Variable-length binary data.
- IMAGE: Large binary data (deprecated in favor of VARBINARY(MAX)).
- UNIQUEIDENTIFIER: Globally Unique Identifier (GUID).
What are the different SQL commands (DDL, DML, DCL, TCL)? Explain with examples.
- DDL (Data Definition Language): Defines the database schema.
  - CREATE: Creates database objects (e.g., tables, views, indexes).
    sql CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) );
  - ALTER: Modifies existing database objects.
    sql ALTER TABLE Employees ADD Email VARCHAR(100);
  - DROP: Deletes database objects.
    sql DROP TABLE Employees;
  - TRUNCATE: Removes all rows from a table (faster than DELETE but cannot be rolled back and resets identity).
    sql TRUNCATE TABLE Employees;
  - RENAME: Renames a database object.
    sql EXEC sp_rename 'Employees', 'Staff'; -- Renames the table Employees to Staff
- DML (Data Manipulation Language): Manipulates data within the database.
  - SELECT: Retrieves data from one or more tables.
    sql SELECT FirstName, LastName FROM Employees;
  - INSERT: Adds new rows to a table.
    sql INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES (1, 'John', 'Doe');
  - UPDATE: Modifies existing data in a table.
    sql UPDATE Employees SET LastName = 'Smith' WHERE EmployeeID = 1;
  - DELETE: Removes rows from a table.
    sql DELETE FROM Employees WHERE EmployeeID = 1;
- DCL (Data Control Language): Controls access and permissions to the database.
  - GRANT: Gives privileges to users.
    sql GRANT SELECT ON Employees TO User1;
  - REVOKE: Removes privileges from users.
    sql REVOKE SELECT ON Employees FROM User1;
  - DENY: Prevents a user from gaining a specific permission, even if granted through group membership.
- TCL (Transaction Control Language): Manages transactions within the database.
  - BEGIN TRANSACTION: Starts a transaction.
  - COMMIT: Saves changes made within a transaction.
  - ROLLBACK: Reverts changes made within a transaction.
  - SAVE TRANSACTION: Creates a savepoint within a transaction.
What is a primary key? What is a foreign key?
- Primary Key: A column (or a set of columns) that uniquely identifies each row in a table. It cannot contain NULL values, and each table can have only one primary key.
- Foreign Key: A column (or a set of columns) in one table that refers to the primary key of another table. It establishes a relationship between the two tables. Foreign keys can contain NULL values (unless explicitly constrained otherwise).
What is a NULL value?
- A NULL value represents missing or unknown data. It is not the same as zero or an empty string. Comparisons with NULL using standard operators (=, <, >, etc.) typically result in NULL. You use IS NULL or IS NOT NULL to check for NULL values.
Explain the SELECT statement and its clauses (FROM, WHERE, GROUP BY, HAVING, ORDER BY).
- SELECT: The core statement for retrieving data.
  - SELECT column1, column2, ...: Specifies the columns to retrieve. SELECT * retrieves all columns.
  - FROM table_name: Specifies the table(s) to retrieve data from.
  - WHERE condition: Filters the rows based on a specified condition.
  - GROUP BY column1, column2, ...: Groups rows with the same values in the specified columns. Used with aggregate functions (e.g., COUNT, SUM, AVG).
  - HAVING condition: Filters the groups created by GROUP BY based on a condition. Similar to WHERE, but applied after grouping.
  - ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...: Sorts the result set based on the specified columns. ASC (ascending) is the default; DESC is descending.
Example:
sql SELECT Department, COUNT(*) AS EmployeeCount FROM Employees WHERE Salary > 50000 GROUP BY Department HAVING COUNT(*) > 5 ORDER BY Department;
This query retrieves the number of employees in each department where the salary is greater than 50000, only includes departments with more than 5 employees, and sorts the results by department name.
What are the different types of JOINs in SQL Server? Explain with examples.
- INNER JOIN: Returns only the rows where there is a match in both tables based on the join condition.
  sql SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
- LEFT (OUTER) JOIN: Returns all rows from the left table (the table specified before LEFT JOIN) and the matching rows from the right table. If there is no match in the right table, NULL values are returned for the right table’s columns.
  sql SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
- RIGHT (OUTER) JOIN: Returns all rows from the right table and the matching rows from the left table. If there is no match in the left table, NULL values are returned for the left table’s columns.
  sql SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
- FULL (OUTER) JOIN: Returns all rows from both tables. If there is no match in either table, NULL values are returned for the non-matching table’s columns.
  sql SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;
- CROSS JOIN: Returns the Cartesian product of the two tables (all possible combinations of rows). No ON clause is used.
  sql SELECT e.FirstName, d.DepartmentName FROM Employees e CROSS JOIN Departments d;
- SELF JOIN: A join where a table is joined to itself. This is useful for querying hierarchical data.
  “`sql
  SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
  FROM Employees e1
  LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
  
  “`
What are aggregate functions? Give examples.
- Aggregate functions perform calculations on a set of values and return a single value.
  - COUNT(*): Counts the number of rows.
  - COUNT(column_name): Counts the number of non-NULL values in a column.
  - SUM(column_name): Calculates the sum of values in a column.
  - AVG(column_name): Calculates the average of values in a column.
  - MIN(column_name): Returns the minimum value in a column.
  - MAX(column_name): Returns the maximum value in a column.
What is the difference between WHERE and HAVING clauses?
- WHERE filters rows before grouping (if GROUP BY is used).
- HAVING filters groups after grouping (used with GROUP BY). HAVING can use aggregate functions, while WHERE cannot.
What is the difference between UNION and UNION ALL?
- UNION: Combines the result sets of two or more SELECT statements, removing duplicate rows.
- UNION ALL: Combines the result sets of two or more SELECT statements, including duplicate rows. UNION ALL is generally faster than UNION because it doesn’t have to perform the duplicate removal. The columns in the SELECT statements must be compatible (same number and data types).
What is the difference between CHAR and VARCHAR?
- CHAR(n): Fixed-length character string. If the stored string is shorter than n, it’s padded with spaces.
- VARCHAR(n): Variable-length character string. Stores only the actual characters, up to a maximum of n. VARCHAR is generally more efficient for storing strings of varying lengths.
What is the difference between DELETE and TRUNCATE?
- DELETE: A DML command that removes rows based on a WHERE clause (or all rows if no WHERE clause is specified). It logs each deleted row, making it slower but allowing for rollback. Does not reset identity columns.
- TRUNCATE: A DDL command that removes all rows from a table. It’s much faster than DELETE because it deallocates the data pages used by the table instead of logging individual row deletions. Cannot be used with a WHERE clause. Resets identity columns. Requires higher permissions than DELETE.
What is an index? What are the types of indexes?
- An index is a special lookup table that the database search engine can use to speed up data retrieval. Simply put, an index in SQL Server is a pointer to data in a table. It’s similar to an index in a book.
- Clustered Index:
  - Determines the physical order of data in a table.
  - A table can have only one clustered index.
  - The leaf nodes of a clustered index contain the actual data rows.
  - Often created automatically on the primary key.
- Nonclustered Index:
  - Does not affect the physical order of data.
  - A table can have multiple nonclustered indexes.
  - The leaf nodes of a nonclustered index contain pointers to the data rows (either the clustered index key or a row identifier (RID) if the table is a heap).
- Unique Index:
  - Ensures that the indexed column(s) contain only unique values.
  - Can be clustered or nonclustered.
- Filtered Index:
  - A nonclustered index that includes a WHERE clause to index only a subset of rows.
- Full-Text Index:
  - Used for full-text searching on text columns.
- Spatial Index:
  - Used for indexing spatial data (e.g., geometry and geography data types).
- XML Index:
  - Used for indexing XML data.
- Columnstore Index:
  - Optimized for analytical workloads (data warehousing). Stores data in a columnar format rather than row-based.
Write a query to find the second-highest salary from an Employees table.

sql SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

This query uses a subquery to find the maximum salary and then finds the maximum salary that is less than that value. Another approach using ROW_NUMBER, RANK, or DENSE_RANK (more efficient) is covered in the Intermediate section.
Write a query to retrieve the names of employees who work in the ‘Sales’ department.
sql SELECT e.FirstName, e.LastName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE d.DepartmentName = 'Sales';

B. Basic Database Design:

What is normalization? What are the benefits of normalization?
- Normalization: The process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing larger tables into smaller, more manageable tables and defining relationships between them.
- Benefits:
  - Reduced Data Redundancy: Minimizes duplication of data, saving storage space and preventing inconsistencies.
  - Improved Data Integrity: Ensures data accuracy and consistency.
  - Easier Data Modification: Updates and deletions are simpler and less prone to errors.
  - Better Data Organization: Makes the database schema more logical and easier to understand.
What are the normal forms (1NF, 2NF, 3NF)? Briefly explain each.
- 1NF (First Normal Form):
  - Eliminate repeating groups of data within a table.
  - Create a separate table for each set of related data.
  - Identify each set of related data with a primary key.
- 2NF (Second Normal Form):
  - Be in 1NF.
  - Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  - Create relationships between these new tables and their predecessors through the use of foreign keys. (No partial dependencies on the primary key)
- 3NF (Third Normal Form):
  - Be in 2NF.
  - Remove columns that are not dependent upon the primary key. (No transitive dependencies).
(There are higher normal forms like BCNF, 4NF, 5NF, but 1NF, 2NF, and 3NF are the most commonly discussed in interviews.)
What is a database schema?
- A database schema is the structure or blueprint of a database, described in a formal language supported by the database management system (DBMS). It defines how data is organized and how the relations among them are associated. It includes all the tables, columns, data types, views, stored procedures, relationships, primary keys, foreign keys, and other database objects.

II. Intermediate Level Questions (2-5 Years of Experience)

These questions require a deeper understanding of SQL Server, including more complex query writing, stored procedures, functions, triggers, and performance optimization techniques.

A. Advanced Querying:

Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() window functions. Provide examples.

ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition (or the entire result set if no partition is specified). No two rows will have the same ROW_NUMBER within a partition.
RANK(): Assigns a rank to each row within a partition based on the ORDER BY clause. If two or more rows have the same value for the ORDER BY columns, they will receive the same rank, and the next rank will be skipped.
DENSE_RANK(): Similar to RANK(), but ranks are consecutive. If two or more rows have the same value, they receive the same rank, but the next rank is not skipped.

sql -- Sample Data (Employees table) -- EmployeeID | Name | Salary | Department -- ---------- | ----- | ------ | ------------ -- 1 | Alice | 60000 | Sales -- 2 | Bob | 70000 | IT -- 3 | Carol | 60000 | Sales -- 4 | David | 80000 | IT -- 5 | Emily | 70000 | Sales WITH RankedEmployees AS ( SELECT EmployeeID, Name, Salary, Department, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankValue, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRankValue FROM Employees ) SELECT * from RankedEmployees;

Output (Illustrative):

EmployeeID	Name	Salary	Department	RowNum	RankValue	DenseRankValue
4	David	80000	IT	1	1	1
2	Bob	70000	IT	2	2	2
5	Emily	70000	Sales	1	1	1
1	Alice	60000	Sales	2	2	2
3	Carol	60000	Sales	3	2	2

Notice how RANK() skips rank 3 in the Sales department, while DENSE_RANK() does not.

Write a query to find employees who have the same salary using a self-join and without using window functions.

sql SELECT e1.Name AS Employee1, e2.Name AS Employee2, e1.Salary FROM Employees e1 INNER JOIN Employees e2 ON e1.Salary = e2.Salary AND e1.EmployeeID <> e2.EmployeeID;
This uses a self-join to compare each employee with every other employee. The e1.EmployeeID <> e2.EmployeeID condition prevents an employee from being compared to themselves.
What is a CTE (Common Table Expression)? Provide an example of how to use a CTE.
- A CTE is a temporary named result set that is defined within the execution scope of a single SQL statement. It’s like a temporary view that exists only for the duration of the query. CTEs can be used to simplify complex queries, improve readability, and enable recursive queries.
sql WITH EmployeeSalaries AS ( SELECT EmployeeID, FirstName, LastName, Salary FROM Employees WHERE DepartmentID = 1 ) SELECT * FROM EmployeeSalaries WHERE Salary > 60000;
This CTE, named EmployeeSalaries, selects employees from department 1. The main query then uses this CTE to filter employees with salaries greater than 60000.
How do you handle NULL values in comparisons and calculations?
- Comparisons: Use IS NULL or IS NOT NULL to check for NULL values. Direct comparisons with NULL using =, <, >, etc., usually result in NULL.
- Calculations: NULL values in arithmetic operations generally propagate NULL as the result. Use functions like ISNULL(), COALESCE(), or NULLIF() to handle NULLs in calculations.
  - ISNULL(expression, replacement_value): Returns replacement_value if expression is NULL; otherwise, returns expression.
  - COALESCE(expression1, expression2, ...): Returns the first non-NULL expression in the list.
  - NULLIF(expression1, expression2): Returns NULL if expression1 equals expression2; otherwise, returns expression1.
Explain the difference between UNION and JOIN.
- UNION: Combines the result sets of two or more SELECT statements vertically (stacking the results). The SELECT statements must have compatible columns (same number and data types).
- JOIN: Combines rows from two or more tables horizontally based on a related column between them.
What is the difference between INNER JOIN and OUTER JOIN? What are the different types of OUTER JOINs?
- This was covered in the Beginner section, but it’s worth reiterating at the Intermediate level. The candidate should be able to explain the differences clearly and provide examples.
Write a query to pivot data from rows to columns. (For example, transform a table with Year, Product, Sales into a table with Product, 2021, 2022, 2023).
“`sql
— Sample Data
CREATE TABLE SalesData (
Year INT,
Product VARCHAR(50),
Sales DECIMAL(10, 2)
);

INSERT INTO SalesData (Year, Product, Sales) VALUES
(2021, ‘ProductA’, 1000),
(2021, ‘ProductB’, 1500),
(2022, ‘ProductA’, 1200),
(2022, ‘ProductB’, 1800),
(2023, ‘ProductA’, 1400),
(2023, ‘ProductB’, 2000);

— Using PIVOT
SELECT Product, [2021], [2022], [2023]
FROM SalesData
PIVOT (
SUM(Sales)
FOR Year IN ([2021], [2022], [2023])
) AS PivotTable;

–Using Conditional Aggregation (More Flexible)

SELECT
Product,
SUM(CASE WHEN Year = 2021 THEN Sales ELSE 0 END) AS [2021],
SUM(CASE WHEN Year = 2022 THEN Sales ELSE 0 END) AS [2022],
SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS [2023]
FROM SalesData
GROUP BY Product;
`` ThePIVOToperator transforms rows into columns. TheSUM(Sales)is the aggregate function,Yearis the pivoting column, and the values in square brackets ([2021],[2022],[2023]`) become the new column names. The conditional aggregation method is more flexible, allowing you to pivot on values that aren’t known in advance.
Write a query to unpivot data. (The reverse of the previous question).
“`sql
— Assume the pivoted data from the previous example is in a table called PivotedSales
CREATE TABLE PivotedSales (
Product VARCHAR(50),
[2021] DECIMAL(10, 2),
[2022] DECIMAL(10, 2),
[2023] DECIMAL(10, 2)
);
INSERT INTO PivotedSales VALUES (‘ProductA’, 1000, 1200, 1400);
INSERT INTO PivotedSales VALUES (‘ProductB’, 1500, 1800, 2000);

SELECT Product, Year, Sales
FROM PivotedSales
UNPIVOT (
Sales
FOR Year IN ([2021], [2022], [2023])
) AS UnpivotTable;

— Using CROSS APPLY (More Flexible)
SELECT p.Product, v.Year, v.Sales
FROM PivotedSales p
CROSS APPLY (VALUES
(2021, p.[2021]),
(2022, p.[2022]),
(2023, p.[2023])
) v (Year, Sales);
`` TheUNPIVOT` operator transforms columns back into rows.

B. Stored Procedures, Functions, and Triggers:

What is a stored procedure? What are the advantages of using stored procedures?
- Stored Procedure: A precompiled collection of one or more SQL statements that are stored under a name and can be executed as a unit.
- Advantages:
  - Improved Performance: Stored procedures are compiled and optimized when they are created, so they generally execute faster than ad-hoc SQL queries.
  - Reduced Network Traffic: Only the stored procedure name and parameters need to be sent to the server, rather than the entire SQL code.
  - Code Reusability: Stored procedures can be called from multiple applications or parts of an application.
  - Security: Stored procedures can be used to control access to data by granting permissions to execute the procedure rather than granting direct access to the underlying tables.
  - Maintainability: Changes to the SQL logic can be made in one place (the stored procedure) without having to modify multiple applications.
  - Transaction Management: Stored Procedures can easily manage transactions with BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN.
How do you create a stored procedure? Provide an example.

“`sql
CREATE PROCEDURE GetEmployeesByDepartment (@DepartmentID INT)
AS
BEGIN
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE DepartmentID = @DepartmentID;
END;

— Execute the stored procedure
EXEC GetEmployeesByDepartment @DepartmentID = 1;
“`

What is the difference between a stored procedure and a function?

Feature	Stored Procedure	Function
Return Value	Can return multiple values or no value.	Must return a single value (scalar or table).
Side Effects	Can have side effects (e.g., modify data).	Generally should not have side effects.
Calling	Called using `EXEC` or `EXECUTE`.	Called as part of an expression (e.g., in a `SELECT`).
Data Modification	Can use `INSERT`, `UPDATE`, `DELETE` statements.	Cannot use `INSERT`, `UPDATE`, `DELETE` (with exceptions).
Transactions	Can manage transactions.	Generally cannot manage transactions directly.
Error Handling	Can use `TRY...CATCH` blocks.	Can use `TRY...CATCH` blocks (with limitations)

What are the different types of functions in SQL Server?
- Scalar Functions: Return a single value.
- Table-Valued Functions (TVFs): Return a table.
  - Inline Table-Valued Functions (iTVFs): Contain a single SELECT statement. More efficient than multi-statement TVFs.
  - Multi-statement Table-Valued Functions (MSTVFs): Can contain multiple SQL statements, including control-of-flow logic.
- System Functions: Built-in functions provided by SQL Server (e.g., GETDATE(), ISNULL(), CAST()).
- Aggregate Functions: (Covered earlier)
Create a scalar function that calculates the age of an employee given their birthdate.

“`sql
CREATE FUNCTION CalculateAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @Age INT;
SET @Age = DATEDIFF(year, @BirthDate, GETDATE());
— Adjust for birthdays that haven’t occurred yet this year
IF (MONTH(@BirthDate) > MONTH(GETDATE())) OR (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()))
SET @Age = @Age – 1;
RETURN @Age;
END;

–Use the function
SELECT dbo.CalculateAge(‘1990-05-15’);
“`
Create an inline table-valued function to get all products within a certain price range
“`sql
CREATE FUNCTION GetProductsByPriceRange (@MinPrice DECIMAL(10, 2), @MaxPrice DECIMAL(10, 2))
RETURNS TABLE
AS
RETURN (
SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN @MinPrice AND @MaxPrice
);

–Use the function
SELECT * FROM dbo.GetProductsByPriceRange(10, 50);
“`
What is a trigger? What are the different types of triggers?
- Trigger: A special type of stored procedure that automatically executes in response to certain events on a table or view.
- Types of Triggers:
  - DML Triggers: Fired in response to INSERT, UPDATE, or DELETE statements.
    - AFTER Triggers (FOR Triggers): Execute after the triggering statement has completed.
    - INSTEAD OF Triggers: Execute instead of the triggering statement. Can be used on views to make them updatable.
  - DDL Triggers: Fired in response to DDL statements (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE).
  - Logon Triggers: Fired in response to a LOGON event (when a user session is established).
Explain the inserted and deleted tables in DML triggers.
- inserted: A virtual table that contains the new rows (or updated values) that are being inserted or updated. Available in INSERT and UPDATE triggers.
- deleted: A virtual table that contains the old rows (or old values) that are being deleted or updated. Available in DELETE and UPDATE triggers.
These tables allow you to access the data that is being affected by the triggering statement within the trigger’s code.
Create an AFTER INSERT trigger that logs new employee records to an audit table.

“`sql
CREATE TABLE EmployeeAudit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Action VARCHAR(10),
AuditDate DATETIME
);
GO — Important to separate CREATE TABLE and CREATE TRIGGER

CREATE TRIGGER TR_Employees_Insert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Action, AuditDate)
SELECT EmployeeID, FirstName, LastName, ‘INSERT’, GETDATE()
FROM inserted;
END;
GO
“`
What are some potential drawbacks of using triggers?
- Hidden Logic: Triggers can make it harder to understand the overall behavior of the database, as some logic is executed automatically.
- Performance Overhead: Triggers can add overhead to DML operations, especially if they are complex or poorly written.
- Debugging Complexity: Debugging triggers can be more challenging than debugging stored procedures or functions.
- Cascading Triggers: Triggers can fire other triggers, leading to cascading effects that can be difficult to manage.
- Unexpected Behavior: Incorrectly designed triggers can lead to unexpected data modifications or errors.

C. Performance Optimization:

**What is an execution plan? How do you view an

Leave a Comment Cancel Reply