Hi,
I need to change the datatype of a very large table from smallint to int...
What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..
pls help on the same!!
Thanks
Cheriyan.Hi,
If the table has thousands of records,definitely it will take some time to alter the datatype and in the mean time make sure that no application is running which is interacting with that table.
Madhivanan|||Thanks Madhivanan
Is there any way to get around this...any alternate solution with would reduce the total time required..|||Not really. You need to do the ALTER TABLE. Dropping any indexes that hit this column would speed it up.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.|||How can I alter my settings in dbForums so that I can be notified when an answer that I would have come up with and is known to everybody can be sent to me as if I posted it, and at the same time increase MY postings counter?|||Go to the dbforums/forumstore page and select a 1 point item and I will get it and have it sent to you. They have some new stuff in, so check it out.|||I checked. All they have left is gag balls and gimp masks in gift wrappers. Give me your address I'll send you a set ;)|||Oh, that's nasty.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.
Are you kidding...that's the only way to FLY!
bcp out...need to do it in CHAR format,drop table, recreate with new types, and bcp back in...|||Out of curiosity, how big is the table (i.e. number of rows)? I am not sure where the turning point is for the performance of BCP vs. just issuing the command. Naturally for a table of 10 rows, you would just issue the alter table command. For a table of 10,000,000 rows, you would probably bcp. Has anyone experimented with the two methods to see where the turnover point is?|||I agree...however...the more paractice the better, especially with less data...the screw ups won't take as long to undo....
But yes, 10 would be silly|||Yes, I found the exact number of rows where BCP becomes more efficient, but I have to let rdjabarov tell you. I will tell you, however, that it is neither a prime number, palindromic, or a perfect square. ;)|||Who cares what the number is, it's how long your mgmt will allow you to keep users from production, that's the determinant. Besides, it depends on what field you ALTER (change, add, or remove), so you (blindman) don't really KNOW the exact number, alright?!|||Oh, brother....|||What? He started it again! Why do I have to come up with the number? It's Brett's job to write code!|||There is no number in these parts...get the scrubs to practice bcp all day long...|||No, no, no. There is no SPOON. There are plenty of numbers. Maybe I should have asked for an order of ten, instead?|||Yes. It's true. I don't really know the number.
Mea culpa.
But I'd still bet its not a prime...
Speaking of no spoons; I've got a stint at a bank in a division called NEO, and they just issued me a generic ID badge that just has NEO where the name should be. I'm half expecing Dr. Smith to leap out at me every time I round a corner.|||Folks, plz clear me here. Will changing the recovery model from full to BULK or Simple have effect on the performance of the alter command? SIMPLE would log all the trans through but clear the log and what about BULK-LOGGED? For i am not sure if each record for that column is written into tran-log or not. sorry for askin stupid questions. :o|||I don't think it is a stupid question. The recovery model setting would help the BCP method, but I do not think it would help the alter table method, as alter table is fully logged, anyway. The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into. Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.|||The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into.
Ahhh...be careful...that's a common misconception that even M$ doesn't make too clear...it's logged at the page level...
Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.
Very good...
It's not about the recovery model...it's about the logging
BUT..the recovery model dictates how you should be performing maintenance|||Yes. I should have made that a bit clearer, rather than just putting in the quotes around "non". My lawyer said I could get away with it, though ;-).|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.
Ok then...but extents for a truncate?
What about bulk insert (or bcp)? Are you saying that it doesn't try to place the data on the appropriate page for a cluster?
So after those processes, the need to be rebuilt?
Thanks fo the info...still clear as mud...
ever find an article that discusses this...
Damn, another reason why I need to buy Kalens book...|||It's an extent, then "an appropriate page", both for BCP and BULK INSERT. If it was on a page level, - can you imagin the amount of defragmentation even in pubs?
