Home Forums SQL Server 2008 T-SQL (SS2K8) Varbinary(max) parameters in Nested Stored Procedures by value or reference RE: Varbinary(max) parameters in Nested Stored Procedures by value or reference

  • September 16, 2014 at 9:32 pm

    #1745944

    rob.delong (9/15/2014)

    Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.

    Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.

    My interest is in understanding the potential memory hit when handling large BLOBs in this environment.

    For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?

    Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.

    A short answer is no extra memory hit, the concept of passing by value or reference is irrelevant in SQL Server. All data is stored in units call pages which are read into memory when needed, there is no way of bypassing this.

    😎