0

My biggest table owes most of its size to a stupid NVARCHAR(max) column that I wager could easily be NVARCHAR(4000) or smaller. What steps can I take to become confident that this change is safe to make and to make it safely?

8
  • 3
    Talk to whomever created it/uses it in the App and understand the use cases and get their buyoff on changing it. Commented Mar 6 at 19:38
  • 1
    make a select that checks the length of all rows of that column and output then once that are bigger. then you need to find the common denominator that can reduce the size to 4000
    – nbk
    Commented Mar 6 at 20:32
  • 5
    RE: "owes most of its size" is this the motivation for making the change? Changing from NVARCHAR(max) to NVARCHAR(4000) won't reduce storage requirements if all data fits into that - as this depends on the size of the data not the declared max Commented Mar 7 at 9:50
  • But memory grants are based on declared column lengths so worthwhile from that perspective. Commented Mar 7 at 15:56
  • @MartinSmith Yes and you're right, except for this technicality: "Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation, which counts against the 8,060-byte row limit during a sort operation.".
    – J. Mini
    Commented Mar 7 at 20:22

3 Answers 3

0

I think you should read this article.

Be careful when you pass from NVARCHAR(max) to NVARCHAR(4000) because everything longer than 4000 will be cut.

Just curious, what are you storing in that NVARCHAR(max)?

Logs?

In that case the solution is not passing to NVARCHAR(4000) but storing the logs into files outside the database.

4
  • The column is just for metadata.
    – J. Mini
    Commented Mar 8 at 14:09
  • Awesome, but: "Just a couple of gotchas: it only works with row compression (not page), and it only works for upsizing datatypes, not downsizing."
    – J. Mini
    Commented Mar 8 at 17:22
  • ah, dammit. Sorry. Commented Mar 10 at 18:02
  • Do you need to query the table based on the values in the metadata or do your query other columns and then retrieve the metadata values ? Commented Apr 2 at 9:25
0

To change from nvarchar(MAX) to nvarchar(4000), You have to be sure that nothing is not longer than 4000 character. So, to do that you can create expression which count the value length of that column. If all lengths is smaller than 4001, you can change it safely.

Be careful and test first on test or stage environment. please get a look on these links.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16

https://www.quora.com/Are-the-limits-of-varchar-8000-and-nvarchar-4000-because-of-the-SQL-server-page-size-8KB-8192-Bytes-If-not-what-else-is-the-reason#:~:text=NVARCHAR%20data%20type%20stores%20variable,of%20the%20data%20being%20stored.

0

As well as checking the maximum current length of the data in that field, you should be looking at where the data is coming from so that you can ensure it won't be truncated or cause an application error in the future.

If it's static data then reducing the field length will be fine if there aren't any rows > 4000 chars, but if it is generated (from other fields) or editable by a user/application then you need to check that they can't try and save > 4000 chars to that field once it has been reduced.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.