Re-enable, drop, or skip that index.

Enabling compression on every single table blindly? That's a pretty bad idea. Remember that compression will ALWAYS increase CPU overhead, and won't always reduce space used. Adding page level compression...

For two separate queries, something like this:

CREATE TABLE #EmployeeInfo (

ID INT,

...

Are you using a static login on your linked server? Impersonating? If you log into the remote server directly using those credentials, are you allowed to select from the table?

Michael L John (11/5/2010)

---

After I though about your statement:

You cannot alter a column to add/remove the identity property. You would have to create a new column completely and then drop...

What happens if you try to force WITH CHECK in the create statement?

I wonder if Gail has a 6th sense and cringes every time a database shrinking post pops up...

Anyway, the reason your tempdb is 20 gigs is because there were big...

Michael L John (11/5/2010)

---

I stand corrected. It's early, and the caffiene hasn't kicked in.

I still think that leaving the clustered index off will be faster when performing the insert....

True, but then you'd need to modify the app to use read_uncommitted to be able to read the changed value if it's in mid transaction, and you said you can't...

Are you doing this WITH NOCHECK? If you create a FK with nocheck, then this flag gets set (as it hasn't validated existing data and therefore can't be trusted).

I assume...

What you're looking to do isn't really possible without a schema change.

Adding the InUse column is a great idea, but you said you can't do that.

In essence what you're trying...

The locking system really hasn't changed between 2005->2008, since there's really not many ways it can change. If a view is taking an exclusive lock on a table in 2005,...

Can you post the query it's hanging on?

Sometimes creating better indexes can solve locking/blocking issues, and in some cases using (nolock) is needed. I'd be willing to bet some indexing...

Michael L John (11/5/2010)

---

How about option 3?

1. Use BCP to copy the data out.

2. Create the new table, without the identity. Leave it as an integer(or a bigint),...

Yep - include a WAITFOR...