Timeline for Sql-server – Convert all ntext columns to nvarchar(max)
Current License: CC BY-SA 4.0
25 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 16, 2021 at 17:55 | comment | added | satcha | great i would do that and i will add this note. thanks a lot @Charlieface | |
| Dec 16, 2021 at 17:43 | comment | added | Charlieface |
@satcha If they are more than 8k you definitely should not care, because nvarchar(max) more than 8k is also stored off-row so there will be no change at all. And I suggest writing against that note "not necessary"
|
|
| Dec 16, 2021 at 17:41 | comment | added | satcha | @Charlieface i dont know if all DATALENGTH(column) are less than 8000. | |
| Dec 16, 2021 at 17:32 | comment | added | Charlieface |
@satcha What about just doing WHERE DATALENGTH(yourColumn) <= 8000 that at least limits it to only rows where the data can actually fit in-row. I still don't think it's necessary, because you are only concerned about size, and this if anything will make it larger not smaller (due to page splits). That post was concerned about performance of off-row data, so that's why it was necessary to force it back to in-row.
|
|
| Dec 16, 2021 at 17:13 | comment | added | Thom A♦ | You never did explain what "have" means, unfortunately, @satcha . | |
| Dec 16, 2021 at 17:12 | comment | added | satcha | @Larnu as i said to charlie, unfortunately I have to do it. but thanks for trying to help me i really appreciate thanks :) | |
| Dec 16, 2021 at 16:57 | comment | added | Thom A♦ | I suggest seeing Charlie's comments on your prior question about that, @satcha . | |
| Dec 16, 2021 at 16:53 | comment | added | satcha | @Larnu, sorry but as what i read in the answer link When you alter the column from the TEXT/NTEXT to NVARCHAR(MAX), the way the data is stored is not changed so, that's why i update column to store it in the right way. | |
| Dec 16, 2021 at 16:41 | comment | added | Thom A♦ | "i update column to itself" Why @satcha ? That is pointless other than to waste time. See my prior comment. With respect, I see no reason to add redundant and unperformant code to my solution. | |
| Dec 16, 2021 at 16:39 | comment | added | satcha | @Larnu cause after i convert ntext to nvarchar i update column to itself but it's take a long time on large tables. so, i used RBAR to update some rows at time | |
| Dec 16, 2021 at 16:38 | comment | added | Thom A♦ | What issue, @satcha ...? The above works, so what is the "issue"? What isn't working? | |
| Dec 16, 2021 at 16:33 | comment | added | satcha | but if someone can help to modify my code to fix my issue i ll really appreciate | |
| Dec 16, 2021 at 16:31 | comment | added | Thom A♦ | Why do this RBAR in the first place, when a set based solution is almost always better, @satcha ..? | |
| Dec 16, 2021 at 16:28 | comment | added | satcha | thanks @Charlieface i will check the link | |
| Dec 16, 2021 at 16:24 | comment | added | satcha |
@larnu i will change my code to convert the right data accordingly, but as what i said my issue is only how can i loop the #chunkobjects table to select id one by one .
|
|
| Dec 16, 2021 at 16:23 | comment | added | Thom A♦ |
You have to do what, @satcha ? Do an UPDATE that CONVERTs your already (n)varchar(MAX)/varbinary(MAX) to a MAX of the same data type? Why do you "have" to do that? There is literally no point to running a query UPDATE dbo.MyTable SET VarcharMaxColumn CONVERT(varchar(MAX),VarcharMaxColumn); other than to waste time.
|
|
| Dec 16, 2021 at 16:23 | comment | added | Charlieface | @satcha If you really wanted to do it, and I see no point in it, see this answer stackoverflow.com/questions/35903375/… | |
| Dec 16, 2021 at 16:21 | comment | added | satcha | @Charlieface thanks a lot, unfortunately I have to do it that's why i changed my code then i tried to use top ( ) and then update column it will be more better. | |
| Dec 16, 2021 at 16:14 | comment | added | Charlieface |
@satcha As discussed on your previous question, there is no need for the UPDATE. It does nothing for the size of your database
|
|
| Dec 16, 2021 at 16:12 | history | edited | Thom A ♦ | CC BY-SA 4.0 |
deleted 54 characters in body
|
| Dec 16, 2021 at 16:11 | comment | added | Thom A♦ |
I don't follow I'm afraid, @satcha . The above will change all the existing columns in your database from one of the old deprecated data types to the relevant MAX.
|
|
| Dec 16, 2021 at 16:09 | comment | added | satcha |
select some recors to update from original table to the temp table like what i did : SELECT top(10) ID, column_id, tableName, columnName, isNullable FROM #objectsToUpdate;
|
|
| Dec 16, 2021 at 16:02 | comment | added | Thom A♦ | What is a "chunk table"? | |
| Dec 16, 2021 at 16:01 | comment | added | satcha | Thanks for your help. Now i saw there is no update columns, i have to update column after convert. and no chunk table. i have the large tables so i have to use the chunk tables. | |
| Dec 16, 2021 at 15:53 | history | answered | Thom A ♦ | CC BY-SA 4.0 |