sql-server add tag
Paul White (imported from SE)
When I use a variable of a large object (LOB) data type in SQL Server, is the whole thing kept in memory at all times? Even if it is 2GB in size?
Top Answer
Paul White (imported from SE)
The 2GB LOB limit only applies to *persistent storage*; variables have no limit.

SQL Server **starts** with an pure in-memory implementation.

It **changes** to a *tempdb*-based backup scheme if the size of the LOB data *grows beyond* 512KB (524,288 bytes).

The backup scheme stores LOB data in a tree of internal *tempdb* LOB pages.

These *tempdb* pages are cached in the buffer pool as usual, but may be flushed to disk by the lazy writer as needed.

There are extra overheads in using the *tempdb*-backed scheme even if all pages remain in memory.

When the variable goes out of scope, the pages are synchronously deallocated. This can take time for very large LOBs.

    -- 512KB: In-memory
    @V1 varchar(max) = REPLICATE(CONVERT(varchar(max), 'X'), 524288),
    -- 512KB + 1: Buffered, tempdb-backed
    @V2 varchar(max) = REPLICATE(CONVERT(varchar(max), 'X'), 524289);

The limit is in bytes, so the number of characters stored depends on the encoding.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.