Search code examples
sqlsql-serverparameter-passingnvarcharvariable-declaration

MSSQL NVARCHAR(MAX) as Procedure Parameter and Variable Declaration Disadvantages


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) ?


Solution

  • 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