CODEWITHSUNDEEP
sql-server
Lieven Cardoen
Lieven Cardoen

Reputation: 25959

Is a nvarchar(max) less performant than a nvarchar(100) for instance?

Is a nvarchar(max) less performant than a nvarchar(100) for instance?

Upvotes: 5

Views: 3701

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294297

Yes. When comparing a variable or column of type MAX the internal code uses stream semantics. The variables types of length less than 8000 use direct comparison semantics. A simple example:

create table A (k int, x varchar(8000));
create clustered index cdxA on A(k);
go

insert into A (k, x) select number, name from master..spt_values;
go

declare @s datetime = getutcdate(),
  @i int = 0;

set nocount on;
while(@i < 100000)
begin
  declare @x varchar(8000);
  select @x = x from A where k = 1 and x = 'rpc';
  set @i = @i + 1;
end

select datediff(ms, @s, getutcdate());

Running this repeatedly yields measured loop times of 2786, 2746, 2746, 2900, 2623, 2736, so an avg of around 2.7s.

The very same code, but replaced the two occurences of varchar(8000) with varchar(max) yields measured times of 4916, 5203, 5280, 5040, 5543, 5130, an average time of 5.2s significantly higher than the non-max type.

Conclusion is that in very tight loops the varchar(max) is slower in comparing and assigning when compared with the non-max types. Like all optimizations, it should be only considered and applied after careful measurement reveals that it is a bottleneck.

Note that the difference is visible even on a data of actual length 3 chars, and is not derived from differences in storage.

Upvotes: 2

Robin Day
Robin Day

Reputation: 102478

The general rule is to use the data type that most suits the data you are storing in it. If you're talking about a varchar(100) then it sounds unlikely you will need to store data as large as a varchar(max) can handle and should probably stick with a varchar(100)

The questions to ask yourself are what data are you storing, how often are you storing and retriving it. How are you using it, lookups, searching or storage only?

As for the differences, varchar(max) is NOT equivalent to TEXT. The main enhancement in varchar(max) is that the data IS still stored in the row unless is exceeds the max length of 8k at which point is is stored in a blob.

See this question that is more specific about the differences between varchar(max) and text.

Using varchar(MAX) vs TEXT on SQL Server

Upvotes: 2

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

Note that in addition to Alex's answer you cannot index an nvarchar(max) column, so in that circumstance it can be a limitation in terms of performance.

Upvotes: 5

Alex Bagnolini
Alex Bagnolini

Reputation: 22382

Same question was answered here (SO) and here (MSDN)

Quoting David Kreps's answer:

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

Upvotes: 6

Related Questions