Answers to the SQL Server 2005 Interview Questions!!

Welcome back…

Today I am going to give the answers to the interview questions that I received regarding SQL Server 2005.

Question 1
What are the different versions (editions) in SQL Server 2005

Answer
SQL Server Express Edition, Workgroup Edition, Standard Edition, Enterprise Edition

Question2
What is the difference between RAID 0 + 1 and RAID 1 + 0

Answer
Here the argument is with 0 + 1 is less redundant than 1 + 0. If you stripe then mirror you could potentially lose data. As opposed to 1 + 0 where you mirror then stripe your data will not be lost. To me… in the end the data will not be lost whether you are striping and then mirroring or mirroring then striping. What are you thoughts?

Question 3
What is a “live” lock

Answer
A condition that occurs when two or more processes continually change their state in response to changes in the other processes. The result is that none of the processes will complete. (I got this answer from webopedia)

Question 4
In a heavily transacted system how would you update 1 million rows without causing any blocking?

Answer
My answer was to use either a cursor and do row by row processing or using a WHILE loop and batch the updates in groups of 10. This would minimize contention on the database.

Question 5
Explain the word “federated” in terms of database servers and databases.

Answer
The word “federated” in terms of database servers means SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

In terms of databases it means the databases can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. (I got these answers from the MSDN website)

I hope this has helped you in learning more about SQL Server 2005 and what questions are asked in interviews.