Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL
Suppose
there are 45 GB data space and 2GB Index space
about 3 million records in this table.
column XXX is varchar(8000) now
poorly worded question, but actually more interesting than it looks. I seem to remember changing the varchar data type when not (max) was metadata only, and depending on his "text in row" setting the data shouldn't move out-of-row since his existing data fits already. I'm not sure what would happen
Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL
Suppose
there are 45 GB data space and 2GB Index space
about 3 million records in this table.
column XXX is varchar(8000) now
Some other information:
99.99% records has NULL value in this varchar(8000)
the web application may hit t...
@AndriyM Yes exactly. There are various ways to achieve an online-like experience but the details can be tricky. 2016+ makes this so much easier.
I fear this will be tested in production
@AaronBertrand I would test it if I can. I do not have enough permission to minapulate on this table. I can only execute some query and I am really new to ddl. This operation is suggested by my colleages, I am just kind of worried about it takes too long. Thanks you again! — user66902006 mins ago
@TomV Pages will still be rewritten since the structure of the row changes even if the data can stay in row. There will also be lots of logging. To some extent, the data always 'moves'.
I think that's right anyway.
In any case, I'd be doing some pretty comprehensive testing before running it for real.
@TomV Well there's no such thing as an nvarchar(8000) but I know what you mean :)
The details depend on circumstances a bit, but for a traditional offline operation (which this would be) the row is fully rewritten even if it stays on the same page and yes the format changes.
@PaulWhite Shall we assume that when this is done online, the existing rows are not altered but the table is somehow marked that a part is using the old format while new rows, after the change, will use the new?
@ypercubeᵀᴹ My recollection of the implementation is that pages are fully rewritten into a new structure and the engine takes care that any changes to the data while the operation is in progress are made to both old and new structures.
For SQL Server, the examples in particular are extremely helpful for me. The .NET / C# docs are quite good too, and I've never understand why developers are so confused / intimidated by them.
@Taryn I didn't know that it would show you globally enabled trace flags. I don't see that in the screenshots (but I know they are kind of out of date).
I have created a dumpfile using EXPDP process.
I am trying to create an external table using the above said dumpfile.
I am using the following statement to create the external table:
CREATE TABLE HR.DATA_LOAD
(
"EMP_NO" NUMBER(10,0) NOT NULL ENABLE,
"EMP_NAME" VARCHAR2(20 BYTE),
"DEP...
Flag to reopen: Can you re-open this question? It just helped me with a problem and doesn't seem "too localized" to me. It probably looks like a syntax error at first, but this error is more difficult than that.
Thoughts? If it's helping someone else who upvoted, I say let's reopen it.