SQL Server Interview Questions

1. What is indexing?
   Types of index. details about Clustered and non clustered index.
 
2. What is CTE (Common Table Expression)? advantage?
   Use of it, Recursive CTE?
 
3. Difference between Temp table and table variable?

4. What is Transaction?
   Describe Isolation levels deeply.
 
5. What is locking ? Types of locking., locking levels

6. What is service broker?

7. Backup and restoration. Describe backup types?
   one scenario to Restore using 3 backup types.
 
8. Query for delete duplicate records keep original.

9. Query for paging

10. Query for hierarchy structure.

11. varChar vs nvarchar vs Char

12. How to clear identity field.

13. Nth largest salary of an employee.

14. Rows to column without using loop.

15. What is Cursor ? Types of cursor?

16. Performance Tuning? Profiler,Query tuning advisor.

17. What precautions taking for Query optimization?

18. Triggers ? Instead of Trigger? Have u used DDL trigger?

19. Normalization? Denormalization? Types of normal forms?

20. Apply Clause?

21. Pivot and UnPivot?

22. Difference of SQL Server 2005 and 2008 / 2008 and 2008R2

23. DB Mail vs SQL Mail?

24. Database design for Online shopping.

25. ER diagram ? Explain what for ER Diagram? Table Relation ship.

26. Indexed view? What conditions consider when creating indexed view?

27. SSRS Architecture? Tablix? Types of Group? Chart? New Controls in SQL server 2008R2? Have u used map control?

28. SCD ?

29. What is Collation? explain sensitivities.

30. How can i pass multiple value as a parameter to SP. explain table valued type and xml.

31. What is Dirty Read? phantom read,un repeatable read , lost updates

32. Deadlocking?

33. Explain system databases?

34. Replication , logshipping , mirroring

35. what datatype for using image?

36. What is SQL Injection? how to protect sql injection?

37. Error handling?

38. Table scan Index Seek and Index Scan?

39.  DBCC Commands?

40.  What are Ranking Functions?

41. Date Functions / string functions?

42. What is the FILLFACTOR?

43. Correlated subquery ? Is it good method?