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 ands
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 thanDELETE
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 withNULL
using standard operators (=, <, >, etc.) typically result inNULL
. You useIS NULL
orIS NOT NULL
to check forNULL
values.
- A
-
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 byGROUP BY
based on a condition. Similar toWHERE
, 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.
- Aggregate functions perform calculations on a set of values and return a single value.
-
What is the difference between
WHERE
andHAVING
clauses?WHERE
filters rows before grouping (ifGROUP BY
is used).HAVING
filters groups after grouping (used withGROUP BY
).HAVING
can use aggregate functions, whileWHERE
cannot.
-
What is the difference between
UNION
andUNION ALL
?UNION
: Combines the result sets of two or moreSELECT
statements, removing duplicate rows.UNION ALL
: Combines the result sets of two or moreSELECT
statements, including duplicate rows.UNION ALL
is generally faster thanUNION
because it doesn’t have to perform the duplicate removal. The columns in theSELECT
statements must be compatible (same number and data types).
-
What is the difference between
CHAR
andVARCHAR
?CHAR(n)
: Fixed-length character string. If the stored string is shorter thann
, it’s padded with spaces.VARCHAR(n)
: Variable-length character string. Stores only the actual characters, up to a maximum ofn
.VARCHAR
is generally more efficient for storing strings of varying lengths.
-
What is the difference between
DELETE
andTRUNCATE
?DELETE
: A DML command that removes rows based on aWHERE
clause (or all rows if noWHERE
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 thanDELETE
because it deallocates the data pages used by the table instead of logging individual row deletions. Cannot be used with aWHERE
clause. Resets identity columns. Requires higher permissions thanDELETE
.
-
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.
- A nonclustered index that includes a
- 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
, orDENSE_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.)
- 1NF (First Normal Form):
-
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()
, andDENSE_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 sameROW_NUMBER
within a partition.RANK()
: Assigns a rank to each row within a partition based on theORDER BY
clause. If two or more rows have the same value for theORDER BY
columns, they will receive the same rank, and the next rank will be skipped.DENSE_RANK()
: Similar toRANK()
, 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, whileDENSE_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. Thee1.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, namedEmployeeSalaries
, 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
orIS NOT NULL
to check forNULL
values. Direct comparisons withNULL
using=
,<
,>
, etc., usually result inNULL
. - Calculations:
NULL
values in arithmetic operations generally propagateNULL
as the result. Use functions likeISNULL()
,COALESCE()
, orNULLIF()
to handleNULL
s in calculations.ISNULL(expression, replacement_value)
: Returnsreplacement_value
ifexpression
isNULL
; otherwise, returnsexpression
.COALESCE(expression1, expression2, ...)
: Returns the first non-NULL expression in the list.NULLIF(expression1, expression2)
: ReturnsNULL
ifexpression1
equalsexpression2
; otherwise, returnsexpression1
.
- Comparisons: Use
-
Explain the difference between
UNION
andJOIN
.UNION
: Combines the result sets of two or moreSELECT
statements vertically (stacking the results). TheSELECT
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
andOUTER JOIN
? What are the different types ofOUTER JOIN
s?- 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 withProduct
,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;
``
PIVOT
Theoperator transforms rows into columns. The
SUM(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);
``
UNPIVOT` operator transforms columns back into rows.
The
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
, andROLLBACK 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
orEXECUTE
.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.
- Inline Table-Valued Functions (iTVFs): Contain a single
- 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
, orDELETE
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).
- DML Triggers: Fired in response to
-
Explain the
inserted
anddeleted
tables in DML triggers.inserted
: A virtual table that contains the new rows (or updated values) that are being inserted or updated. Available inINSERT
andUPDATE
triggers.deleted
: A virtual table that contains the old rows (or old values) that are being deleted or updated. Available inDELETE
andUPDATE
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 TRIGGERCREATE 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