Removing Identity Column from a large table

To disable an identity column we basically rely on management studio, which intern create temp table transfer the data back and forth for us. But if the table is bit huge with millions of rows, you will get a time out error. Normally most of the solution given by people would be to create a temp table with no identity column transfer the rows into it and then rename the table.



The catch in this is , there is a very bright chance of you missing some relationship as you have to create them manually. So the innovative way invented by meeeeeeeeee is ;) (tanta da tanta da doiiiiii hehe back ground music) is the below


1. Create a teamporary table as shown below


select * into temp from main

2. select count(*) from temp


select count(*) from main

check if the rows are equal just in case




3. Truncate table main

Remove the identity property from the GUI.

4, ALTER TABLE main

NOCHECK CONSTRAINT ALL


INSERT into main 

select * from temp


ALTER TABLE main

CHECK CONSTRAINT ALL






5. select count(*) from main


select count(*) from temp

check if the rows are equal just in case




6. Drop table temp  -- Dont put the main table name then ur inviting a disaster


Thats it for the day folks. Hope to see you all soon with some thing uninteresting ;).Until then its me signing off



With Smiles
Santhosh

Understanding Max DOP Settings

I am back again folks with one more article. Donno now a days I am enjoying writing in english rather than tamil. Did I start thinking in english by any chance? (enof enof frst start thinking dude kind of thoughts).

Today we are going to talk abt MAX DOP and a interesting fact about it.There is a lot of wrong thinking that max DOP is to determine the no.of processors used by SQL Server. Its not basically affinity mask is the one to do that. This one jst to determine the maximum number of threads an operator (or an operation) in a query can use. Even the explanation in BOL is not quite upto the mark :(. Quite confusing isn't then check this out page then :).

This time again with a peculiar problem. I have set max DOP as 4 in one of my server interestingly I am seeing alteast 20-30 parallel threads. Believe me that scared me a lot. After lots of googling, talking to PSS engineer nothing worked out.

Atlast I went to my last resource social.msdn.com(Believe me folks one of the best place to hang around for sql server techies) a couple of MVPs replied back telling dude MAX DOP is not for query rather its applicable for each operator ooch..Linky dinky of this question.so no wonder my queries are executing in 30+ threads. So my recommended recipe to set up max DOP is to set a one for the server level and if you think a couple of your queries are performing worst due to this then set a one in the query level.

Thats it for the day folks. Hope to see you all soon with some interesting problem.Until then its me signing off with one full & one broken leg :)

With Smiles

Santhosh