SQL SERVER Interview Questions

1. What are the different types of joins and what dies each do?
2. What are the four main query statements?
3. What is a sub-query? When would you use one?
4. What is a NOLOCK?
5. What are three SQL keywords used to change or set someone’s permissions?
6. What is the difference between HAVING clause and the WHERE clause?
7. What is referential integrity? What are the advantages of it?
8. What is database normalization?
9. Which command using Query Analyzer will give you the version of SQL server and operating system?
10. Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
11. What is the purpose of using COLLATE in a query?
12. What is a trigger?
13. What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
14. What is an execution plan? When would you use it? How would you view the execution plan?
15. What is the STUFF function and how does it differ from the REPLACE function?
16. What does it mean to have quoted_identifier on? What are the implications of having it off?
17. What are the different types of replication? How are they used?
18. What is the difference between a local and a global variable?
19. What is the difference between a Local temporary table and a Global temporary table? How is each one used?
20. What are cursors? Name four types of cursors and when each one would be applied?
21. What is the purpose of UPDATE STATISTICS?
22. How do you use DBCC statements to monitor various aspects of a SQL server installation?
23. How do you load large data to the SQL server database?
24. How do you check the performance of a query and how do you optimize it?
25. How do SQL server 2000 and XML linked? Can XML be used to access data?
26. What is SQL server agent?
27. What is referential integrity and how is it achieved?
28. What is indexing?
29. What is normalization and what are the different forms of normalizations?
30. Difference between server.transfer and server.execute method?
31. What id de-normalization and when do you do it?
32. What is better - 2nd Normal form or 3rd normal form? Why?
33. Can we rewrite subqueries into simple select statements or with joins? Example?
34. What is a function? Give some example?
35. What is a stored procedure?
36. Difference between Function and Procedure-in general?
37. Difference between Function and Stored Procedure?
38. Can a stored procedure call another stored procedure. If yes what level and can it be controlled?
39. Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
40. How do you find the number of rows in a table?
41. Difference between Cluster and Non-cluster index?
42. What is a table called, if it does not have neither Cluster nor Non-cluster Index?
43. Explain DBMS, RDBMS?
44. Explain basic SQL queries with SELECT from where Order By, Group By-Having?
45. Explain the basic concepts of SQL server architecture?
46. Explain couple pf features of SQL server
47. Scalability, Availability, Integration with internet, etc.)?
48. Explain fundamentals of Data ware housing & OLAP?
49. Explain the new features of SQL server 2000?
50. How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
51. What is data integrity? Explain constraints?
52. Explain some DBCC commands?
53. Explain sp_configure commands, set commands?
54. Explain what are db_options used for?
55. What is the basic functions for master, msdb, tempdb databases?
56. What is a job?
57. What are tasks?
58. What are primary keys and foreign keys?
59. How would you Update the rows which are divisible by 10, given a set of numbers in column?
60. If a stored procedure is taking a table data type, how it looks?
61. How m-m relationships are implemented?
62. How do you know which index a table is using?
63. How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
64. How do you find the error, how can you know the number of rows effected by last SQL statement?
65. How can you get @@error and @@rowcount at the same time?
66. What are sub-queries? Give example? In which case sub-queries are not feasible?
67. What are the type of joins? When do we use Outer and Self joins?
68. Which virtual table does a trigger use?
69. How do you measure the performance of a stored procedure?
70. Questions regarding Raiseerror?
71. Questions on identity?
72. If there is failure during updation of certain rows, what will be the state?