我的VARCHAR(MAX)字段的上限是4000;有什么好处?

我的一个数据库中有一个表,它是一个电子邮件队列。发送到特定地址的电子邮件会累积到一封电子邮件中,这是通过存储过程完成的。在存储过程中,我有一个表变量,用于构建电子邮件的累积体,然后循环发送每封电子邮件。在我的表var中,我将body列定义为 VARCHAR(MAX) ,因为对于给定的电子邮件地址,当前可以累积任意数量的电子邮件。尽管我的专栏被定义为 VARCHAR(最大值) 它的行为好像是 VARCHAR(4000) 不是 如果抛出任何异常,它只会在4000个字符之后默默地停止连接更多的数据。

MERGE语句将累积的电子邮件正文构建到@EMAILS.body中,该字段将自身截断为4000个字符。

我已经更新了MERGE语句,试图将整个指定的字符串强制转换为VARCHAR(MAX),但它仍在默默地将自身截断为4000个字符。。。这是我的新合并:

MERGE @EMAILS AS DST 
USING (SELECT * FROM @ROWS WHERE ROWID = @CURRID) AS SRC 
ON SRC.ADDRESS = DST.ADDRESS 
WHEN MATCHED THEN 
    UPDATE SET 
        DST.ALLIDS = DST.ALLIDS + ', ' + CONVERT(VARCHAR,ROWID), 
        DST.BODY = DST.BODY + 
            CONVERT(VARCHAR(MAX),
                '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+
                ' '+CONVERT(VARCHAR,SRC.DATED,8)+
                ':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+
                SRC.BODY+' (Message ID '+
                CONVERT(VARCHAR,SRC.ROWID)+')'+
                CHAR(13)+CHAR(13)
            )
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (ADDRESS, ALLIDS, BODY) VALUES (
        SRC.ADDRESS, 
        CONVERT(VARCHAR,ROWID), 
        CONVERT(VARCHAR(MAX),
            '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+
            ' '+CONVERT(VARCHAR,SRC.DATED,8)+
            ':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+
            SRC.BODY+' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
            +CHAR(13)+CHAR(13)
        )
    );

下面是我的存储过程代码。。。

ALTER PROCEDURE [system].[SendAccumulatedEmails]
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SENTS  BIGINT = 0;

    DECLARE @ROWS TABLE (
        ROWID    ROWID, 
        DATED    DATETIME, 
        ADDRESS  NAME, 
        SUBJECT  VARCHAR(1000), 
        BODY     VARCHAR(MAX)
    )
    INSERT INTO @ROWS SELECT ROWID, DATED, ADDRESS, SUBJECT, BODY 
    FROM system.EMAILQUEUE 
        WHERE ACCUMULATE = 1 AND SENT IS NULL
        ORDER BY ADDRESS, DATED

    DECLARE @EMAILS TABLE (
        ADDRESS  NAME, 
        ALLIDS   VARCHAR(1000),
        BODY     VARCHAR(MAX) 
    )

    DECLARE @PRVRID ROWID = NULL, @CURRID ROWID = NULL
    SELECT @CURRID = MIN(ROWID) FROM @ROWS
    WHILE @CURRID IS NOT NULL BEGIN
        MERGE @EMAILS AS DST 
        USING (SELECT * FROM @ROWS WHERE ROWID = @CURRID) AS SRC 
        ON SRC.ADDRESS = DST.ADDRESS 
        WHEN MATCHED THEN 
            UPDATE SET 
                DST.ALLIDS = DST.ALLIDS + ', ' + CONVERT(VARCHAR,ROWID), 
                DST.BODY = DST.BODY + '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                            +CONVERT(VARCHAR,SRC.DATED,8)
                            +':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY
                            +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
                            +CHAR(13)+CHAR(13)
        WHEN NOT MATCHED BY TARGET THEN 
            INSERT (ADDRESS, ALLIDS, BODY) VALUES (
                SRC.ADDRESS, 
                CONVERT(VARCHAR,ROWID), 
                '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                    +CONVERT(VARCHAR,SRC.DATED,8)+':</i> <b>'
                    +SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY
                    +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
                    +CHAR(13)+CHAR(13));

        SELECT @PRVRID = @CURRID, @CURRID = NULL
        SELECT @CURRID = MIN(ROWID) FROM @ROWS WHERE ROWID > @PRVRID
    END 

    DECLARE @MAILFROM VARCHAR(100) = system.getOption('MAILFROM'), 
    DECLARE @SMTPHST VARCHAR(100) = system.getOption('SMTPSERVER'), 
    DECLARE @SMTPUSR VARCHAR(100) = system.getOption('SMTPUSER'), 
    DECLARE @SMTPPWD VARCHAR(100) = system.getOption('SMTPPASS')

    DECLARE @ADDRESS NAME, @BODY VARCHAR(MAX), @ADDL VARCHAR(MAX)
    DECLARE @SUBJECT VARCHAR(1000) = 'Accumulated Emails from LIJSL'

    DECLARE @PRVID NAME = NULL, @CURID NAME = NULL 
    SELECT @CURID = MIN(ADDRESS) FROM @EMAILS
    WHILE @CURID IS NOT NULL BEGIN
        SELECT @ADDRESS = ADDRESS, @BODY = BODY 
        FROM @EMAILS WHERE ADDRESS = @CURID

        SELECT @BODY = @BODY + 'This is an automated message sent from an unmonitored mailbox.'+CHAR(13)+'Do not reply to this message; your message will not be read.'
        SELECT @BODY = 
            '<style type="text/css">
                * {font-family: Tahoma, Arial, Verdana;}
                p {margin-top: 10px; padding-top: 10px; border-top: single 1px dimgray;} 
                p:first-child {margin-top: 10px; padding-top: 0px; border-top: none 0px transparent;}
            </style>' 
            + @BODY 

        exec system.LogIt @SUBJECT, @BODY

        BEGIN TRY 
            exec system.SendMail @SMTPHST, @SMTPUSR, @SMTPPWD, @MAILFROM, 
                             @ADDRESS, NULL, NULL, @SUBJECT, @BODY, 1
        END TRY 
        BEGIN CATCH
            DECLARE @EMSG NVARCHAR(2048) = 'system.EMAILQUEUE.AI:'+ERROR_MESSAGE()
            SELECT @ADDL = 'TO:'+@ADDRESS+CHAR(13)+'SUBJECT:'+@SUBJECT+CHAR(13)+'BODY:'+@BODY
            exec system.LogIt @EMSG,@ADDL
        END CATCH

        SELECT @PRVID = @CURID, @CURID = NULL
        SELECT @CURID = MIN(ADDRESS) FROM @EMAILS WHERE ADDRESS > @PRVID
    END

    UPDATE system.EMAILQUEUE SET SENT = getdate()
    FROM system.EMAILQUEUE E, @ROWS R WHERE E.ROWID = R.ROWID
END