The greatest SQL Server development tools ever

A very special situation at work has required us to look into software for comparing and merging data between two databases with very similar data. Originally we had planned to use Microsoft's SQL Server replication, but the replication system's inability to cope with special constraints forced us to look elsewhere for a solution. A co-worker recommended Redgate's SQL Data Compare product, and as it turns out that it rocks something fierce. It does exactly what I hoped it would, and is stupid simple to use.

Whilst looking at that I also took a look at SQL Compare from the same company. Also mind numbingly good.

Like most shops (should have) we have three environments (or more): Dev, Test and Production. Migrating from the development environment on up has consistently been a chore (at best) and headache inducing, I hate this and want to quit (at worst). With these tools much of the pain is completely side-stepped with minimal tweaking required at migration time.

SQL Server cursors - How I got into and out of an infinite loop

Recently while coding at work, a sql server cursor of mine got stuck in an infinite loop. The infinite loop was very surprising to me because I did not expect the result set from the cursor query to change as I updated or inserted new records into the table my cursor query was based on. What I now know is that when a cursor is based on some table, and you update or insert a new record into that table, the cursor will take that data change into consideration. How exactly? Well I'm not sure... (...if you are sure please leave a comment :)

I side-stepped the issue by creating a table variable that I first inserted into, and then spun the cursor off of instead of the spinning directly off of the actual table. Since I am not making any changes to the table variable, but only the actual table, I have not gotten caught in the unending cursor trap.

In addition to the overly aware cursor issue was another problem I have no explanation for. A co-worker noted that one of the fields would sometimes but not always change from Run to Run Complete. Apparently the use of a cursor can induce strange behavior when updating a record that is part of the cursor's result set. In this specific case I was updating the record that the cursor was currently on. Until I have a better grasp of cursors, my use of a table variable seems to side-step this issue by keeping the table my cursor uses and the table I am populating separate.

SQL Server - Enforce foreign key constraints and cascading deletes

Apparently if Enforce foreign key constraint is set to No in the designer cascading deletes will not take place. The programmatic variant of setting that to No is:

 ALTER TABLE MyTable NOCHECK CONSTRAINT MyFKConstraint

Swapping out NOCHECK with CHECK will enforce the fk constraint.

Although I think this behavior is reasonable, and even desirable it has put me on my heels more than once. Hopefully writing it down will keep me from forgetting again.