One of the great things about .NET and Visual Studio 2005 is how easy it allows us to change the value of variables and such while debugging.  If you have a boolean variable, and you want it to be true, you can set a breakpoint where you need it, hover over the variable and change it.  The other option is to go to the quickwatch window and modify it in there. 

The only type that has given me trouble is a DateTime variable.  I'm not sure exactly why, but when I hover over it, it will say something like {2/17/2007 2:44:33 PM}.  When I try to change it in the little popup, it complains about the }.  I then tried to go into the QuickWatch window and change it in there.  Same problem.  I then decided to try to edit one of the internal values (minutes, hours, etc).  It wouldn't let me do that either. 

I'm not sure what the problem is.  I wasn't quite sure how to search for something like this on the web so I just played around with it for a few and found a solution.  In the quick watch, you can type whatever expression you want.  (DateTime variable was called dt). I went ahead and typed the following in the quickwatch window.

dt = DateTime.Parse("2/17/2007 1:44:33 PM")

This seemed to do the trick. 

While working on a project,  I needed to change the identity column for a certain set of tables to be negative while keeping the current positive values that it already had.  I found a quick and easy way to do it using the following:

DBCC CHECKIDENT('your_table', RESEED, new_identity_value)

There were however a few drawbacks to this:
1) You cannot set the identity increment along with it.  This means if you change it to negative, you cannot start at 0 or -1 if your increment is positive.  Your only real solution is to put it at a huge negative number such as -1 billion or -2^31 and have it count forward.

2) If you happen to change the identity seed to a number (say 10) but a number like 15 already exists, assuming the increment is 1, it will eventually reach 15 and sql server will throw an error

3) If you ever insert into the table by turning identity insert on you may run into issues.  Say you changed the seed to be a negative value, but you are inserting id of 50 forcefully - your  new identity seed is now 50, it is no longer the negative number. 

4) If you perform a truncate table,it will reset it to the identiy seed defined when the table was created.

Without going into too much detail, the project I was working on involved a sql server database that sometimes gets populated directly (via an application) and other times gets populated by another sql server.  Records generated by the application are given a negative number so it does not conflict with records generated by the other server.  (There was not a way to use sql replication for many reasons, so this was the best solution at the time). 

Because the sql command above had the listed problems, we decided to set the identity columns to -2^31. Since the increment is 1 and we can't changed it (problem #1), it'll take a while before we reach 0.  Unfortunately because we will occasionally be receiving records from another server, everytime it does an identity insert with a positive number, it's going to reset what we've done (problem #3)

The other problem is if we reset the identity BACK to -2^31, we will run into problem #2. The only solution I could think of to solve this was to do the following:

After the server does its inserts, we need to set it to the minimum negative possible that won't cause a conflict (e.g. if our last negative number used was -1 billion, we need to set it to -1 billion so that the next id created will be -999,999,999) 

We can select the identity column by using the IDENTITYCOL keyword.  We can use min/max functions to get us the min or max identity.  The only problem is we not only want the minimum identity, but we want the min usable identity that won't conflict.  See solution below:

DECLARE @TEMP int
SELECT @TEMP =
   (CASE when max(IDENTITYCOL ) < 0 THEN max(identitycol) ELSE -2147483647 END) 
FROM my_table where IDENTITYCOL < 0

We now use the value of @TEMP to re-assign the identity column

I don't know if this is the only solution, but it was all I could do at the time because I could not find a way to dynamically change the identity seed value for multiple tables.  I'm curious if there's another way!

I had a project where I needed to have a SQL Server database replicate another database (files were given in ascii format).  Every month there was a huge set of files to download to make sure everything was up-to-date, and on a daily basis, "update" files were given.  The files consisted of changes/additions.  The task was to keep the sql server database synchronized with the main database.

For the monthly files, I could clear the database completely and insert all of the records directly.  For the daily files, however, it requires to update anything that already exists and insert anything new.  Didn't have to worry about deletes.

Since the update files were in ascii format, I used sql bulk insert to store them into temp tables.  I could then use sql statements to move the records into the permanent database.

One thing I wanted to avoid was having to do something like this:
   IF EXISTS (SELECT KEYFIELD FROM MAINTABLE)  .. UPDATE or INSERT.

The updates would have required entering all the fields.  While I could have used a code generator to do this for me,  the solution I came up with made more sense.
  
Delete from  MAINTABLE Where KEYFIELD in (Select KEYFIELD from TEMPTABLE).
Followed by:
INSERT INTO MAINTABLE SELECT * FROM TEMPTABLE

It's pretty straightforward: This will remove all records from maintable that need to be "updated".  Next I insert all records from the temptable, and now I've done it all without having to get field-specific.  It may be a little more painful for SQL Server to handle, but since I had potentially 50+ tables this seemed like the best approach at the time.

One thing to keep in mind is that the main database files were pretty big while the update files were pretty small.  Tens of thousands of records in main table compared to tens or hundreds in "update" file/table. If this wasn't the case, I wouldn't recommend this approach.