The question here is simple but although i have searched a lot, i haven't found an answer on the internet..
Is there any disadvantages using nvarchar(max) comparing with nvarchar(n) in procedure parameters and variable declarations ? ( not for storing, just for execution )
let me give an example
CREATE PROCEDURE prFoo
@pParam1 NVARCHAR(MAX)
@pParam2 NVARCHAR(32)
....
DECLARE @dVariable1 NVARCHAR(MAX)
DECLARE @dVariable2 NVARCHAR(32)
....
Is there any performance differences between (@pParam1, @dVariable1) and (@pParam2, @dVariable2) ?
Actually, when i doing this test, i was expecting to see no difference but results has been shocked me
Sharing below the code which i used to test
declare @i int = 0
declare @n int = 1000000
declare @memlimit int = 3072
declare @limited NVARCHAR(3072) = ''
declare @unlimited NVARCHAR(MAX) = ''
declare @start DATETIME
declare @limitedMS INT
declare @unlimitedMS INT
-- measure @limited case
set @i = 0
set @start = GETDATE()
while (@i < @n)
begin
set @limited = @limited + 'a'
if len(@limited) = @memlimit
begin
set @limited = ''
end
set @i = @i + 1
end
set @limitedMS = DATEDIFF(MILLISECOND, @start, GETDATE())
-- measure @unlimited case
set @i = 0
set @start = GETDATE()
while (@i < @n)
begin
set @unlimited = @unlimited + 'a'
if len(@unlimited) = @memlimit
begin
set @unlimited = ''
end
set @i = @i + 1
end
set @unlimitedMS = DATEDIFF(MILLISECOND, @start, GETDATE())
select @limitedMS as LimitedMS, @unlimitedMS as UnlimitedMS
the output is the below
LimitedMS UnlimitedMS
1816 3373
as you can see, even if i use @memlimit to limit memory allocation for @unlimited variable, it still works slower than @limited version