📘 Table of Contents
- ✅ Beginner Level SQL Server Questions
- 🔶 Intermediate Level SQL Server Questions
- 🔴 Advanced Level SQL Server Questions
✅ Beginner Level SQL Server Interview Questions & Answers
1. What is a Database Management System (DBMS)?
A DBMS is a software application that interacts with end users, applications, and the database itself to store, retrieve, and manage data efficiently.
2. What is a Relational Database Management System (RDBMS)?
An RDBMS stores data in tables with rows and columns and uses keys to maintain relationships between them. It ensures consistency and integrity using constraints and supports SQL.
3. What is SQL?
SQL (Structured Query Language) is the standard language for accessing and managing data in relational databases.
4. What is a Database?
A database is a structured collection of data that supports storage, retrieval, and manipulation. In SQL Server, it includes tables, views, stored procedures, and more.
5. What are Tables and Fields?
Tables store data in rows and columns. Each column (field) represents a data attribute, and each row is a record.
6. What is a Primary Key?
A primary key uniquely identifies each row in a table and doesn't allow NULL or duplicate values.
7. What is a Unique Key?
A unique key enforces the uniqueness of values in a column. Unlike the primary key, it allows one NULL value.
8. What is a Foreign Key?
A foreign key creates a relationship between two tables by referencing the primary key in another table.
9. What is a JOIN in SQL?
JOIN is used to retrieve data from multiple tables based on logical relationships between the columns.
10. What are the Types of JOINs in SQL?
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN: Returns all rows from the left table and matched rows from the right.
- RIGHT JOIN: Returns all rows from the right table and matched rows from the left.
- FULL JOIN: Returns all rows when there is a match in either table.
- CROSS JOIN: Returns the Cartesian product of two tables.
- SELF JOIN: A table joins with itself.
11. What is Normalization?
Normalization is the process of organizing data to eliminate redundancy and ensure data integrity by splitting tables and defining relationships.
12. What is Denormalization?
Denormalization involves combining normalized tables to reduce joins and improve read performance.
13. What are Normal Forms and How Do They Work?
- 1NF: Atomic values, no repeating groups.
- 2NF: 1NF + No partial dependency.
- 3NF: 2NF + No transitive dependency.
- BCNF: All determinants must be candidate keys.
14. What is a View?
A view is a virtual table based on the result of a SELECT query. It doesn't store data physically.
15. What is an Index?
An index improves the speed of data retrieval by creating a quick-access path to data.
16. What are the Types of Indexes?
- Clustered Index: Sorts and stores data rows in the table.
- Non-Clustered Index: Stores pointers to data rows separately.
- Unique Index: Ensures unique values.
- Full-Text Index: Used for text searches.
17. What is a Query?
A query is a SQL command to retrieve or modify data from a database.
18. What is a Subquery?
A subquery is a query nested inside another SQL query to provide input data to the outer query.
19. What are the Types of Subqueries?
- Correlated Subquery: Depends on the outer query.
- Non-Correlated Subquery: Can be executed independently.
20. What is a Constraint?
Constraints enforce rules at the column level to maintain data accuracy and integrity.
21. What are the Types of Constraints?
- PRIMARY KEY: Unique and not null.
- FOREIGN KEY: References another table.
- UNIQUE: Ensures uniqueness.
- CHECK: Validates a logical expression.
- NOT NULL: Disallows NULL values.
- DEFAULT: Sets default values.
22. What is Data Integrity?
Data integrity ensures that data is accurate, consistent, and reliable. It is enforced using keys and constraints.
23. What is Auto Increment?
Auto increment in SQL Server is implemented using the IDENTITY property. It automatically generates sequential values, usually for primary keys.
24. What is the Difference Between Clustered and Non-Clustered Index?
| Clustered Index | Non-Clustered Index |
|---|---|
| Sorts and stores data rows | Stores pointers to data rows |
| Only one allowed per table | Multiple allowed |
25. What is a Self JOIN?
A self join is a join where a table is joined with itself to compare rows within the same table.
26. What is a CROSS JOIN?
A cross join returns the Cartesian product of two tables, combining each row of the first with all rows of the second.
27. What is an Alias in SQL?
An alias is a temporary name assigned to a column or table in a SQL query for readability.
28. What is the Difference Between UNION and UNION ALL?
- UNION: Combines results and removes duplicates.
- UNION ALL: Combines results including duplicates.
29. What is the Difference Between NULL and Blank?
- NULL: Represents unknown or missing value.
- Blank: Represents an empty but known value (e.g., '').
30. What is the Difference Between BETWEEN and IN Operators?
- BETWEEN: Filters values within a range.
- IN: Filters based on a list of values.
🔶 Intermediate Level SQL Server Interview Questions & Answers
31. What is a Cursor in SQL Server?
A cursor is a database object used to process each row returned by a query individually. It's used for row-by-row operations that cannot be done with set-based SQL.
32. What are the Types of Cursors in SQL Server?
- Static: Snapshot of data; does not reflect changes.
- Dynamic: Reflects all changes made to the underlying data.
- Forward-Only: Can only move forward; fastest and least resource intensive.
- Keyset-Driven: Keyset is fixed; reflects value changes but not new rows.
33. What is a Stored Procedure?
A stored procedure is a saved collection of SQL statements that can be executed as a program. It accepts parameters and improves code reuse, performance, and security.
34. What is a Trigger?
A trigger is a special type of stored procedure that executes automatically in response to events like INSERT, UPDATE, or DELETE on a table or view.
35. What is the Difference Between DELETE and TRUNCATE?
| DELETE | TRUNCATE |
|---|---|
| Can delete specific rows using WHERE | Removes all rows without WHERE |
| Logs each row deleted | Minimal logging |
| Can be rolled back | Can be rolled back if inside a transaction |
| Keeps identity value | Resets identity |
36. What are Local and Global Variables?
- Local: Declared with
@, accessible within its scope. - Global: System-defined with
@@, available across sessions.
37. What is a Recursive Stored Procedure?
A stored procedure that calls itself directly or indirectly. Used for hierarchical or tree-structured data traversal. Must include an exit condition to avoid infinite loops.
38. What are the Advantages and Disadvantages of Stored Procedures?
- Advantages: Precompiled, secure, reusable, faster.
- Disadvantages: Hard to debug, complex version control, tightly coupled with database.
39. What is a User-Defined Function (UDF)?
A UDF is a function written by the user to return a single value or a table. UDFs cannot modify the database state.
40. What are the Types of User-Defined Functions?
- Scalar: Returns a single value.
- Inline Table-Valued: Returns a table from a single SELECT.
- Multi-Statement Table-Valued: Builds a table using multiple statements.
41. What is a Check Constraint?
A check constraint restricts the values of a column by validating data against a logical expression. Example: CHECK (Age >= 18)
42. Can We Create a Clustered Index on a Unique Key Column?
Yes, if there is no existing clustered index, you can create one on a unique key column.
43. Can TRUNCATE Be Used on a Table Referenced by a Foreign Key?
No. SQL Server does not allow truncating a table that has a foreign key constraint applied to it.
44. What is a Schema?
A schema is a logical container for database objects like tables, views, and procedures. It helps organize and secure objects within a database.
45. What is the Difference Between Schema and Database?
- Schema: Logical grouping inside a database.
- Database: Physical storage containing one or more schemas.
46. What Are Different Types of SQL Commands?
- DDL: CREATE, ALTER, DROP
- DML: SELECT, INSERT, UPDATE, DELETE
- DCL: GRANT, REVOKE
- TCL: COMMIT, ROLLBACK, SAVEPOINT
47. What is a Composite Key?
A composite key is made up of two or more columns that together uniquely identify a row in the table.
48. What is a Candidate Key?
Candidate keys are columns that can qualify as a unique identifier. One becomes the primary key; others are alternate keys.
49. What is an Alternate Key?
An alternate key is any candidate key that is not the primary key. It still has a unique constraint.
50. What is Collation in SQL Server?
Collation determines how string data is compared and sorted. It includes rules for case, accent, kana, and width sensitivity.
51. What Are the Types of Collation Sensitivity?
- Case Sensitive (CS): A ≠ a
- Accent Sensitive (AS): e ≠ é
- Kana Sensitive (KS): Japanese Kana variants
- Width Sensitive (WS): Half-width ≠ Full-width
52. What is a Materialized View?
A materialized view stores the result of a query physically. In SQL Server, similar functionality is provided by indexed views.
53. What is the Difference Between View and Materialized View?
| View | Materialized View |
|---|---|
| Virtual, not stored | Physically stores data |
| Always reflects latest data | May need refresh or maintenance |
| Less performance gain | Improves query performance |
54. Difference Between Stored Procedure and UDF
| Stored Procedure | User Defined Function |
|---|---|
| Can modify data | Cannot modify data |
| Called with EXEC | Used in SELECT |
| No return type (but can return values) | Returns a value or table |
55. Difference Between Stored Procedure and Dynamic SQL
- Stored Procedure: Precompiled and reusable.
- Dynamic SQL: Built at runtime using strings, more flexible but riskier.
56. Difference Between HAVING and WHERE Clause
- WHERE: Filters rows before aggregation.
- HAVING: Filters after aggregation (GROUP BY).
57. How to Fetch Alternate Records from a Table?
Use ROW_NUMBER() or modulus logic:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS rn FROM Employees
) AS Temp
WHERE rn % 2 = 1;58. How to Select Unique Records from a Table?
Use DISTINCT:
SELECT DISTINCT Department FROM Employees;59. Where Can We Use Inline Table-Valued Functions?
They are useful in joins, APPLY operations, or as a replacement for views when you need to pass parameters.
60. Can We Create a Foreign Key Without a Primary Key?
No. A foreign key must reference a column that has a PRIMARY KEY or UNIQUE constraint.
🔴 Advanced Level SQL Server Interview Questions & Answers
61. What is a Page in SQL Server?
A page is the smallest unit of storage, typically 8 KB in size. Pages store rows of data, index entries, or metadata. Multiple pages make up an extent (64 KB).
62. How to Create an Empty Table from an Existing Table?
SELECT * INTO NewTable FROM OldTable WHERE 1 = 0;63. How to Fetch Common Records from Two Tables?
Use INTERSECT or INNER JOIN:
SELECT column1 FROM TableA
INTERSECT
SELECT column1 FROM TableB;64. What is OLTP (Online Transaction Processing)?
OLTP systems handle real-time transactions like order entry, banking, or retail POS. They are optimized for fast, consistent insert/update/delete operations.
65. What is a Linked Server?
A linked server allows SQL Server to connect and execute queries on external data sources (other SQL Servers, Oracle, Excel, etc.).
66. What are ACID Properties in SQL Server?
- Atomicity: All operations in a transaction complete or none do.
- Consistency: Data must be in a valid state before and after a transaction.
- Isolation: Transactions operate independently.
- Durability: Committed data is permanent even after failure.
67. What are UNION, INTERSECT, and EXCEPT Operators?
| Operator | Description | Removes Duplicates |
|---|---|---|
| UNION | Combines result sets | Yes |
| UNION ALL | Combines all including duplicates | No |
| INTERSECT | Common rows from both queries | Yes |
| EXCEPT | Rows in first query not in second | Yes |
68. What is a Global Temporary Table and Its Scope?
Global temp tables (##TempTable) are visible to all sessions and remain available until all sessions referencing them are closed.