1. What is SQL Server?
Answer:
Microsoft SQL Server is a relational database management system (RDBMS) developed
by Microsoft. It stores and manages data using T-SQL (Transact-SQL) language.
🔹 2. What are the different types of SQL statements?
Answer:
DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
🔹 3. What is a primary key?
Answer:
A Primary Key uniquely identifies each record in a table.
Cannot have NULLs.
Only one per table.
CREATE TABLE Employee(
EmpID INT PRIMARY KEY,
Name VARCHAR(100)
);
🔹 4. What is a foreign key?
Answer:
A Foreign Key enforces referential integrity between two tables.
It links one table’s column to another table’s primary key.
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
🔹 5. What is the difference between INNER ,
JOIN LEFT JOIN, and
RIGHT JOIN?
Type Description
INNER JOIN Returns matching records from both tables
Type Description
LEFT JOIN Returns all from left + matching from right
RIGHT JOIN Returns all from right + matching from left
🔹 6. What is a clustered index?
Answer:
Determines the physical order of data in a table.
Only one clustered index per table (usually on primary key).
CREATE CLUSTERED INDEX IX_EmpID ON Employee(EmpID);
🔹 7. What is a non-clustered index?
Answer:
Stores a separate structure pointing to actual data.
Multiple non-clustered indexes allowed per table.
CREATE NONCLUSTERED INDEX IX_Name ON Employee(Name);
🔹 8. What is normalization?
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data
integrity.
Forms:
1NF – Atomic columns
2NF – No partial dependency
3NF – No transitive dependency
🔹 9. What is denormalization?
Answer:
The process of combining tables to improve read performance (reduces joins) at the cost of
redundancy.
🔹 10. What is the difference between DELETE, TRUNCATE, and
DROP?
Command Action Rollback Removes Structure
DELETE Removes rows Yes No
TRUNCATE Removes all rows (faster) No No
DROP Deletes table No Yes
🔹 11. What are stored procedures?
Answer:
Precompiled SQL statements stored in the database for reuse.
CREATE PROCEDURE GetEmployees AS
SELECT * FROM Employee;
🔹 12. What are functions in SQL Server?
Answer:
Functions return a single value or table and can be used inside queries.
Example:
CREATE FUNCTION GetBonus(@Salary INT)
RETURNS INT
AS
BEGIN
RETURN @Salary * 0.1
END
🔹 13. What is a trigger?
Answer:
A Trigger automatically executes in response to certain events (INSERT, UPDATE,
DELETE).
CREATE TRIGGER trg_Audit
ON Employee
AFTER INSERT
AS
INSERT INTO AuditLog SELECT * FROM inserted;
🔹 14. What is a view?
Answer:
A View is a virtual table based on SQL query results.
CREATE VIEW vw_ActiveEmployees AS
SELECT Name, Dept FROM Employee WHERE IsActive = 1;
🔹 15. What are transactions?
Answer:
A transaction is a unit of work that ensures ACID properties (Atomicity, Consistency,
Isolation, Durability).
BEGIN TRANSACTION
UPDATE Account SET Balance = Balance - 100 WHERE AccNo = 1;
UPDATE Account SET Balance = Balance + 100 WHERE AccNo = 2;
COMMIT;
🔹 16. What is a CTE (Common Table Expression)?
Answer:
A temporary result set used for readability and recursion.
WITH EmployeeCTE AS (
SELECT EmpID, ManagerID FROM Employee
)
SELECT * FROM EmployeeCTE;
🔹 17. What is the difference between HAVING and WHERE?
Clause Works On Used With
WHERE Rows Before GROUP BY
HAVING Groups After GROUP BY
SELECT Dept, COUNT(*) FROM Employee
GROUP BY Dept
HAVING COUNT(*) > 5;
🔹 18. What are temporary tables?
Answer:
Used to store temporary data for session or batch execution.
Local: #Temp
Global: ##Temp
CREATE TABLE #Temp (ID INT);
🔹 19. What is the difference between RANK(), DENSE_RANK(), and
ROW_NUMBER()?
Function Description
ROW_NUMBER() Gives unique sequential number
RANK() Skips rank numbers for ties
DENSE_RANK() Doesn’t skip rank numbers
🔹 20. What is indexing and why is it important?
Answer:
Indexes speed up query performance by allowing faster data retrieval. However, they can
slow down write operations (INSERT, UPDATE, DELETE).
Would you like me to create a Word document with these 20 SQL Server questions and
answers — nicely formatted for printing or interview use (like the previous ones)?