SQL (Doesn't) Server

SQL Server: pasty wad of absolute Shit, or revolting stream of projectile vomit? The world may never know. If you ever get a chance to play with SQL Server, for an amusing day of knee-slapping hilarity, do this:

1. Dance and frolic as you dump a remote table consisting of 10 million rows.
2. Skip and jump while copying the 90MB compressed dump to a remote server.
3. Raise a jocular toast as a BULK INSERT command does its work.
4. Be happy that this, the hard part of the process, only took half an hour.
5. With nary a worry, start a transaction to protect the existing production data.
6. Confidently remove records from production version of the fact table.
7. Relax and start the final insert from the staging table to the production table.
8. Become confused that half an hour has passed and process continues.
9. With trepidation, begin searching for why load still isn't finished an hour later.
10. Notice in horror that the transaction log has increased by at least 7GB for 1GB of data.
11. Angrily commence yelling at SQL Server.
12. Dejectedly give-up on process, and click 'cancel'.  Repeat.
13. Worried, find the process ID of the insert and kill it after ten hair-raising minutes have transpired and load demands to continue.  Repeat.
14. Blanch as SQL Server reports it won't kill the process until a rollback has occurred, and this rollback will take 47,000 seconds.
15. Become irate while rollback time increases to 69,000 seconds, and continues to grow.
16. Frantically shut down database service.  Repeat.
17. Transform into the Incredible Hulk from sheer rage, and demolish the nearest wall when database refuses to restart.
18. Attempt recovery without resorting to backup file, while sobbing uncontrollably.
19. Restore from backup.
20. Maniacally plant C4 on server chassis in a futile attempt to ensure future cooperation.
21. Drool mindlessly while you're hauled away in a straight-jacket.

All this, for a wimpy 10 million row insert that consisted of about one gigabyte of data. This is by no means a huge table, and it loaded in half an hour from a frigging file. One would assume that SQL Server’s internal format would take less time, especially since a transaction wouldn’t have to track anything but the possibility of rolling back inserts on an empty table. These assumptions were obviously incorrect, though I’ve no comprehension of what SQL Server was possibly doing through the entire debacle. The sooner we can replace this system with Postgresql via ODBC, the better.

Until Tomorrow