You need to remove a large table from your datab

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

You need to remove a large table from your database ...

You need to remove a large table from your database. This table has over 200 extents of data and you issue a "DROP TABLE MYLARGETABLE". How is this handled in SQL Server 2005?

Answer: A two part scheme of destruction with a virtual sniper marking each allocation unit for deletion inside the transaction. Then in smaller separate transactions of destruction the allocation units are dropped.

Explanation: While a virtual phase locked laser would be nice, this actually occurs in two phases. The logical phase occurs inside the transaction with the drop statement and all of the allocation units are marked for deallocation and locked. After the transaction commits, the allocation units marked are physically dropped in short batches inside background transactions.

(Continued on next question...)

Other Job Interview Questions

• Which of the following is not available to you?
• Can you use a CASE statement in a WHERE clause?
• You then write a function to use this assembly, but it does not work. What could be the problem?
• Using Report Builder, which of the following statements is correct about formatting numbers?
• What is the recommended way to send mail from SQLAgent in SQL Server 2005?
• What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin
• You are building an application for a Smartphone and want to use SQL Server Compact Edition. However you do not want this to run as a service all the time because of power concerns. What can you do?
• True or false, Report Builder offers direct export to a Microsoft Access database as one of its supported export options?
• Can you use a CASE statement in a WHERE clause?
• How can SQL Server Agent Mail send messages in SQL Server 2005?
• More...