One of the great things about Visual Studio 2005 is how it easily allows us to change the values of our variables while we are debugging.  For example, while debugging a program, you have a boolean variable that is currently set to false. Setting it to true will allow you to step into a piece of code that you test.  This can be easily done by highlighting the variable and waiting for the popup that shows you the current value.  You can then change this value by clicking on 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. 

Sometimes it's just necessary to get rid of foreign key constraints and triggers in order to do maintenance on a database.  Imagine trying to delete all records from 100+ tables, but you now have to put them in dependency-order in order to do it.  While foreign key constraints help maintain relational integrity, it can be a huge pain in the rear when you are trying to do basic operations.  Of course doing this should only be done when you know-for-sure that you won't ruin the overall integrity of the database.

Disable all Constraints on a table

ALTER TABLE yourtable NOCHECK CONSTRAINT ALL

This will disable, but not remove all of your constraints. It will not, however, remove Primary Key and uniqueness constraints.  If you want to only remove a specific constraint, you can replace the word ALL with the name of the constraint - no quotes.  To re-enable replace NOCHECK with CHECK

Sometimes we need to disable triggers because they just get in our way.

To disable all triggers on a table:

DISABLE Trigger ALL ON yourtable

Replace the word ALL with a specific trigger name to disable a specific trigger.

Some notes:
1) Trigger enabling/disabling is only available for Sql Server 2005
2) In order to truncate a table, you must DROP foreign key constraints rather than disable them.  Use Delete and change your identity seed with DBCC CHECKIDENT if that will solve your problem.
3) Use with caution

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

DBCC CHECKIDENT('my_table', RESEED, @TEMP ) 

Now we just need to be sure to run this everytime we need to reset the identity back to a negative number.

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!

One of the recent projects I've been working on forced me to do the following:
Delete from tableName where its primary key=X. 

The tricky part is this: We don't know the column_name of the primary key.  The reason for this is because the table_name may be different each time i run the statement;  The sql statement is completely dynamically built.

The table name is determined by grabbing it from a table that stores table names (along with other info)

So now, I have the table name, but need to get the primary key's name.

I found two ways to do this:

SELECT col.name FROM sysindexes indx
    INNER JOIN sysobjects sobj ON indx.id = sobj.id
    INNER JOIN sysobjects pk ON indx.name = pk.name AND pk.parent_obj = indx.id AND pk.xtype = 'PK'
    INNER JOIN sysindexkeys ik on indx.id = ik.id AND indx.indid = ik.indid
    INNER JOIN syscolumns col ON ik.id = col.id AND ik.colid = col.colid
WHERE 
    sobj.name = 'table_name'

After talking to a co-worker, he mentioned that there is an Information_Schema View which can give you similar information but is a little easier to use.

SELECT ky.COLUMN_NAME FROM information_schema.table_constraints tbl 
    INNER JOIN information_schema.key_column_usage ky 
ON tbl.constraint_name = ky.constraint_name
    WHERE tbl.constraint_type = 'PRIMARY KEY' AND tbl.table_name = 'table_name'

So now once I have this, I can create my sql statement (C# psuedo):

string x = "DELETE FROM " + tableName + " WHERE " + primaryKeyColumn + " = " + value.ToString()

In my scenario all of my primary keys are ints, so this will work, if not we'd have to do a few extra things...

I had a project where I needed to have a SQL Server database replicate/mirror another database.  The goal was to import a huge set of ascii files (each file representing a table), and perform bulk inserts.  This was done once a month to ensure that the entire database was exactly up to date.  Additionally, every day there would be a set of addendum tables.  This was a much smaller set of tables that contained a relatively small amount of data, and some records would need to be inserted while others needed to be updated. 

In order to ensure that we do not add duplicate records, I needed a way to go through the entire addendum/change file and find records that already exist versus the ones which are new.  The typical approach would be to loop through each record and check to see if the item already exists.  If it does, we need to update, if not we need to insert.  While this is a straightforward approach, the problem was that is that it required writing an UPDATE statement that would require naming every single column. There were over 50 tables, so it would have required writing a unique update statement for each of them.  It also would involve using Cursors to loop through each record of the temporary addendum table.  I needed an easier approach as the project was time sensitive.

Since the tables had their own keys, and since we did not use identity columns as the primary key, I took advantage of that.  This is what I came up with:

DELETE from  MAIN_TABLE Where KEYFIELD in (Select KEYFIELD from TEMP_TABLE).

INSERT INTO MAIN_TABLE SELECT * FROM TEMP_TABLE

Where MAIN_TABLE is the full table and TEMP_TABLE is a temporary sql server table that holds the addendum/changes.  It's pretty straightforward.  I remove all records from the original table  that exist in the daily addendum/change files, and then I insert them all back into the main table.  This involves no UPDATEs, just inserts.  Since the tables match up exactly, there's no need to specify column names, and I can do this very easily for all 50+ tables.

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.