T-SQL Interview Questions

1. What is T-SQL?

T-SQL, or Transact-SQL, is a procedural extension of SQL used in Microsoft SQL Server. It adds features like error handling, variables, and procedural programming.

2. What are the differences between T-SQL and SQL?

• SQL is a standard query language, while T-SQL is a proprietary extension by Microsoft.

• T-SQL includes procedural features such as loops and conditionals.

3. What are primary keys and foreign keys?

• Primary Key: Uniquely identifies each record in a table.

• Foreign Key: Establishes a relationship between two tables by referencing a primary key in another table.

4. What is a JOIN in T-SQL?

A JOIN combines rows from two or more tables based on a related column. Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

5. Explain the difference between WHERE and HAVING clauses.

• WHERE: Filters rows before grouping.

• HAVING: Filters groups after aggregation.

6. What is a stored procedure in T-SQL?

A stored procedure is a precompiled set of T-SQL statements that perform a specific task.

7. How do you optimize a T-SQL query?

• Use proper indexing.

• Avoid SELECT * and fetch only required columns.

• Use query execution plans to analyze performance.

• Write efficient JOINs and use appropriate filters.

8. What is a CTE (Common Table Expression)?

A CTE is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

9. Explain the purpose of the ROW_NUMBER() function.

ROW_NUMBER() assigns a unique sequential integer to rows within a result set, starting at 1 for the first row.

10. What is the difference between DELETE and TRUNCATE?

• DELETE: Removes rows with conditions and logs each deletion.

• TRUNCATE: Removes all rows without logging individual deletions.

11. What is a cursor in T-SQL?

A cursor is a database object used to iterate through result sets row by row.

12. How do you handle errors in T-SQL?

oUsing TRY…CATCH blocks to catch and handle exceptions.

13. What is a UNION vs. UNION ALL?

• UNION: Combines results and removes duplicates.

• UNION ALL: Combines results without removing duplicates.

14. Explain indexing and its types.

Indexing improves query performance by organizing data. Types include clustered and non-clustered indexes.

15. What are triggers in T-SQL?

Triggers are special procedures executed automatically in response to certain database events, such as INSERT, UPDATE, or DELETE.

16. What is the difference between a temporary table and a table variable?

• Temporary Table: Stored in tempdb and visible across multiple batches.

• Table Variable: Stored in memory and has limited scope.

17. What is the COALESCE function?

COALESCE returns the first non-null value from a list of expressions.

18. What are the differences between VARCHAR and NVARCHAR?

• VARCHAR: Stores non-Unicode data.

• NVARCHAR: Stores Unicode data and uses more storage space.

19. How do you implement pagination in T-SQL?

Using OFFSET-FETCH with the ORDER BY clause.

20. What is the difference between ISNULL() and NULLIF()?

• ISNULL(): Replaces NULL with a specified value.

• NULLIF(): Returns NULL if two expressions are equal.

21. What is a derived table?

A derived table is a subquery used in the FROM clause of a SELECT statement.

22. How do you identify and resolve deadlocks?

• Use SQL Server Profiler or Extended Events to capture deadlock information.

• Resolve by optimizing query logic or using NOLOCK hints.

23. What are aggregate functions?

Functions like COUNT, SUM, AVG, MAX, and MIN that perform calculations on a set of values.

24. Explain transaction management in T-SQL.

Transactions ensure data consistency using BEGIN TRANSACTION, COMMIT, and ROLLBACK.

25. What is the purpose of the WITH (NOLOCK) hint?

Allows reading data without acquiring locks, improving performance but risking dirty reads.

26. What is the difference between CHAR and VARCHAR?

• CHAR: Fixed-length data type.

• VARCHAR: Variable-length data type.

27. How do you create a view in T-SQL?

Use the CREATE VIEW statement followed by the SELECT query.

28. What are table-valued functions?

Functions that return a table data type.

29. How do you update data in T-SQL?

Use the UPDATE statement with the SET clause to modify data in a table.

30. What is a partitioned table?

A table divided into smaller, more manageable pieces called partitions, based on a column value.

31. What is the difference between IN and EXISTS?

• IN: Checks for values within a list or subquery.

• EXISTS: Checks for the existence of rows in a subquery.

32. What are sequence objects in T-SQL?

Sequence objects generate numeric values in a sequence.

33. How do you create relationships between tables?

Use primary and foreign keys to establish relationships.

34. What is the use of the CROSS APPLY operator?

CROSS APPLY joins a table with a table-valued function.

35. What are sparse columns?

Sparse columns optimize storage for null values.

36. Explain the CASE statement in T-SQL.

The CASE statement allows conditional logic in queries.

37. What is the difference between a subquery and a correlated subquery?

• Subquery: Independent query used within another query.

• Correlated Subquery: Depends on values from the outer query.

38. How do you backup a database in T-SQL?

Use the BACKUP DATABASE statement with options like FULL or DIFFERENTIAL.

39. What is a GUID?

A globally unique identifier used for unique keys.

40. What is a window function?

A function that performs calculations across a set of rows related to the current row, e.g., RANK() or OVER()

41. What is a JSON function in T-SQL?

Functions like JSON_VALUE() and JSON_QUERY() handle JSON data.

42. Explain the MERGE statement.

MERGE performs insert, update, or delete operations in a single statement.

43. How do you rename a column in T-SQL?

Use the sp_rename stored procedure.

44. What is the OUTPUT clause?

Returns data from inserted, updated, or deleted rows.

45. What is a schema in T-SQL?

A schema is a logical container for database objects.

46. How do you prevent SQL injection?

Use parameterized queries and validate user inputs.

47. What is a data type in T-SQL?

Defines the kind of data a column can hold, e.g., INT, VARCHAR, DATE.

48. What are indexed views?

Views with an index created to improve performance.

49. How do you check the structure of a table?

Use the sp_help or INFORMATION_SCHEMA.COLUMNS.

50. How do you perform dynamic SQL in T-SQL?

Use the EXEC or sp_executesql commands to execute SQL strings dynamically.