:sick:This is ABSOLUTELY a depends trick question. It ABSOLUTELY depends on if you correct the error in the script that was listed. Also your DB settings change the results. Please next time include the DB creation script or at least let us have a clue about the DB settings. Also do not include a script that changes the answer completely if you remove the error including the TABLE CREATE in the first batch of 1280 executions.:-P

Here is how to run the script and get a "WRONG" answer every time.

On a 2005 SQL server with DB recovery set to simple or FULL run the script exactly as listed in the QOTD.

This might be because of the way the First table create statement is included in the "GO 1280" batch. This would definately change the way the logging could occur. So after seeing i got the wrong Guess or Answer I changed the logging to FULL and rand the script again. This time there was only a slight increase in the Fragmentation. Another differant RIGHT wrong answer.

So now I am very interested. Same script same server same database. Only I fix the script problem with the first table create being included in the "GO 1280" batch.

Finally I get some Fragmentation. Is this really Table or DB file fragmentation though. Let's set the DB back to Simple recovery mode, drop both tables, Backup DB, Shrink the DB and find out what we get.

The Second table has the 0.390625 avg frag percentage before the drop and shrink. It has 15.625 after...

Now I take back out my correction. Were I added the missin GO after the "CREATE TABLE FirstTable" of the script and try again.

MAGIC! The Fragmentation is the same before and after SHRINKDATABASE!

I leave the mistake in and try again. This time will FULL recovery. Again MAGIC! The Fragementation is the same before and after SHRINKDATABASE!

I know that with FULL recovery (aka Big tran logs)fragmentation can come from repeated running of the same sripts. So reset by dropping secondtable and running SHRINKDATABASE, then run again with the error in the script.

Again same Average fragmentation both times.

I try once more to get the "Correct" result using the EXACT SCRIPT in the QOTD.

This time I do not reset. I do not drop secondtable or run SHRINKDATABASE.

MAGIC AGAIN! Exact Same or less than 2% change of fragmentation every time after six runs.

So finally wanting to try to get the same results as QOTD author was guessing we would get, I reset again. This time removed the error by adding the missing GO after the CREATE TABLE FirstTable script.

Now I get the "RIGHT" answer. :sick: Great another QOTD where you have to guess at the answer. Is it the one if you leave the script alone or the one where you don't?! :w00t:

So Long story short anyone that carefully read the script or test run it would have selected the RIGHT answer, even thought the QOTD says it is wrong.:cool: