MS SQL Important Questions | Store Procedures, Functions and Transactions

Mastering MS SQL Server is essential for database developers and administrators. This guide covers the most important MS SQL questions related to stored procedures, functions, and transactions to help you ace interviews and optimize database performance.

Q1. Database:

    - An organized collection of data stored and accessed electronically.

---

Q2. Query in SQL:

    - A request for data or information from a database.

SELECT * FROM Employees;

---

Q3. Constraints in SQL:

    - Rules enforced on data columns to ensure the accuracy and reliability of the data.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT CHECK (Age >= 18)
);

---

Q4. Keys in SQL:

    - Used to uniquely identify records in a database table.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

---

Q5. Domain Constraint in SQL:

    - Restricts the values that can be entered into a column.

CREATE DOMAIN PositiveNumber AS INT CHECK (VALUE > 0);

---

Q6. Tuples in a Database:

    - Rows in a database table, representing a single record of data.

INSERT INTO Employees (ID, Name) VALUES (1, 'Alice');

---

Q7. Difference between 'NVARCHAR' and 'VARCHAR' in SQL:

    - 'NVARCHAR': Stores Unicode data.

    - 'VARCHAR': Stores non-Unicode data.

CREATE TABLE Employees (
    Name NVARCHAR(100),
    Description VARCHAR(255)
);

---

Q8. Function in SQL:

    - A saved database procedure that returns a value.

CREATE FUNCTION GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

---

Q9. Procedure in SQL:

    - A saved database procedure that performs a specific task or set of tasks.

CREATE PROCEDURE AddEmployee
    @Name NVARCHAR(100),
    @Age INT
AS
BEGIN
    INSERT INTO Employees (Name, Age) VALUES (@Name, @Age);
END;

---

Looking for more MSSQL resources? Read our  SQL Functions: Types, Examples, and Uses to build a strong foundation!

Q10. View in SQL:

    - A virtual table based on the result set of an SQL query.

CREATE VIEW EmployeeView AS
SELECT Name, Age FROM Employees;

---

Q11. Indexes in SQL:

    - Used to improve the speed of data retrieval operations.

CREATE INDEX idx_Name ON Employees (Name);

---

Q12. Queries and Subqueries in SQL:

    - Queries: SQL statements that retrieve data from a database.

    - Subqueries: Queries nested within other queries.

SELECT Name FROM Employees WHERE Age = (SELECT MAX(Age) FROM Employees);

---

Q13. Aggregate Functions in SQL:

    - Perform calculations on a set of values, returning a single value.

SELECT COUNT(*) FROM Employees;

---

Q14. Joins in SQL:

    - Combine rows from two or more tables based on a related column.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

---

Q15. Difference between UNION and INTERSECTION in SQL:

    - UNION: Combines the results of two or more SELECT statements, removing duplicate rows.

    - INTERSECTION: Returns the common rows between two or more SELECT statements.

SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;

---

Q16. Cursor in SQL:

    - A database object used to retrieve data from a result set one row at a time.

DECLARE @Name NVARCHAR(100);
DECLARE employee_cursor CURSOR FOR
SELECT Name FROM Employees;
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @Name;

---

Q17. Triggers in SQL:

    - Special stored procedures that automatically execute in response to certain events on a table.

CREATE TRIGGER AfterEmployeeInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    -- Trigger logic
END;

---

Q18. Functional Dependency in a Database:

    - A relationship between two attributes in a database, where the value of one attribute determines the value of another.

---

Q19. 1NF (First Normal Form) in Database Normalization:

    - Ensures that each table cell contains a single value, and each record is unique.

---

Looking for more MSSQL resources? Read our  SQL Server Scenario-Based Interview Questions to build a strong foundation!

Q20. 2NF (Second Normal Form) in Database Normalization:

    - Ensures that all non-key attributes are fully functionally dependent on the primary key, removing partial dependencies.

---

Q21. 3NF (Third Normal Form) in Database Normalization:

    - Ensures that all attributes are functionally dependent only on the primary key, removing transitive dependencies.

---

Q22. BCNF (Boyce-Codd Normal Form) in Database Normalization:

    - A stronger version of 3NF, ensuring that every determinant is a candidate key.

---

Q23. Transaction in a Database:

    - A sequence of operations performed as a single logical unit of work.

BEGIN TRANSACTION;
-- SQL operations
COMMIT;

---

Q24. Recovery in a Database:

    - Restoring a database to a consistent state after a failure.

---

Q25. ACID Properties in Database Transactions:

    - Atomicity: Ensures that all operations within a transaction are completed successfully.

    - Consistency: Ensures that a transaction brings the database from one valid state to another.

    - Isolation: Ensures that transactions are executed in isolation from one another.

    - Durability: Ensures that the results of a committed transaction are permanent.

---

Q26. Deadlock in a Database:

    - Occurs when two or more transactions are waiting indefinitely for each other to release locks.

---

Q27. Handling Deadlocks in SQL Server:

    - Setting a deadlock priority, using timeouts, or designing transactions to minimize lock contention.

SET DEADLOCK_PRIORITY LOW;