2

In SQL Server, if you create a column of type NVARCHAR(MAX), and populate only 128 characters inside - does it consume more storage than if you'd create it as NVARCHAR(128) - or is it the same size?

Dale K
  • 25,246
  • 15
  • 42
  • 71
ZiGi
  • 81
  • 1
  • 7
  • When the value is stored in-row, the space will be the same. The MAX type will be more if it needs to be stored out-of-row due to other columns in the row/page. In addition to storage, consider SQL Server will need to account for possibly memory for MAX types in query plans so don't use varchar(MAX) unless you need more than 4000 characters. – Dan Guzman Apr 25 '20 at 17:29
  • To add on to @DanGuzman's comment, I consider column data type widths as a type of constraint. If the application you're writing is expecting nothing longer than (in your example) 128 characters, make it an `nvarchar(128)` regardless of whether an `nvarchar(max)` would store those same characters in an equivalent amount of storage. – Ben Thul Apr 26 '20 at 01:25

2 Answers2

5

I made a small experiment, use the following code to create 2 tables and populate it with equally random 128 characters:

CREATE TABLE dbo.MyTextFix (
    [val] NVARCHAR(128) NOT NULL
)
CREATE TABLE dbo.MyTextMax (
    [val] NVARCHAR(MAX) NOT NULL
)

SET NOCOUNT ON;
DECLARE @COUNT INT = 0
WHILE (@COUNT <= 100000)
BEGIN
    DECLARE @VAL NVARCHAR(128) = CAST(NEWID() AS NVARCHAR(128)) + '-' + CAST(NEWID() AS NVARCHAR(128)) + '-' + CAST(NEWID() AS NVARCHAR(128)) + '-' + CAST(NEWID() AS NVARCHAR(128))
    INSERT INTO dbo.MyTextFix ([val]) VALUES (@VAL)
    INSERT INTO dbo.MyTextMax ([val]) VALUES (@VAL)
    SET @COUNT = @COUNT + 1
END

And now - we'll use the "Disk Usage by Table" to check the actual size of the tables:

enter image description here

As you can see, even though one table has a NVARCHAR(128) and the other has NVARCHAR(MAX) data types - if we only populate the 128 characters - both tables are the same size.

Dale K
  • 25,246
  • 15
  • 42
  • 71
ZiGi
  • 81
  • 1
  • 7
0

Storage may be same . You need to consider the following

  1. MAX will store data in out of row in LOB allocation
  2. When you use MAX, and run a query ,optimizer may grant unnecessary more memory
sri
  • 16