January 22, 2009 at 2:59 am
Hi all,
I have a table comprises of 28 million of records and it has the following structure (a simplified version):
CREATE TABLE [dbo].[inv_header](
[uid] [int] IDENTITY(1,1) NOT NULL,
[order_no] [char](30) NOT NULL,
[xmlContent] [text] NOT NULL,
[last_action_date] [datetime] NULL,
)
I would like to modify the following datatypes:
1) order_no (char (30)) to nvarchar(30).
- Use ALTER TABLE ALTER COLUMN T-SQL
2) xmlContent (text) to (ntext)
- Need to create a new table, copy data, delete old table, rename new table
Is this correct? By the way, are there any side effects (content value wise) for changing datatype from char/varchar to nvarchar providing nvarchar is always longer than the original? If I use T-SQL to apply this change of a 28M rec table, can Profiler give me an estimation on how long it would take ?
Thanks very much in advance.
January 22, 2009 at 3:05 am
28 Million records, that's a huge table for this process, i would suggest to take a backup, since i can remember once when i did this sort of operation, SQL has cleared my data, so i would create a new table with required structure and then insert data into that table, hope this is not a production table 🙂
January 22, 2009 at 3:12 am
Unfortunately yes, it's a production table I am dealing with. I am just trying to think of a best way to handle this type of modification on such a large table. Would Profiler help to give me an estimation on the total processing time? Any suggestions ?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply