在TSQL中，Nvarchar(Max)只能获取4000个字符？

Question

这是针对SS 2005的。

为什么我只得到了4000个字符而不是8000个？

@SQL1的字符串被截断到4000个字符。

ALTER PROCEDURE sp_AlloctionReport(
    @where NVARCHAR(1000),
    @alldate NVARCHAR(200),
    @alldateprevweek NVARCHAR(200))
AS
    DECLARE @SQL1 NVARCHAR(Max)

    SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, 
    VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, 
    VenueInfo.Address2, '' As AllocationDate, '' As AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
    VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, 
    VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, 
    [VenueCategories].[Category] + '' Allocations'' AS ReportHeader, 
    ljs.AbbreviationCode AS PrevWeekCampaign
    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
    INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
    LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                    WHERE ' + @alldateprevweek + ') ljs
                ON VenuePanels.PanelID = ljs.PanelID) 
    INNER JOIN (SELECT VenueInfo.VenueID, VenuePanels.PanelID, VenueInfo.VenueName, VenueInfo.CompanyName, VenuePanels.ProductCode, 
                VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, 
                CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
                VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, 
                VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, 
                ljs2.AbbreviationCode AS PrevWeekCampaign
                FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
                INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) 
                INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) 
                INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
                LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                                FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                                WHERE ' + @alldateprevweek + ') ljs2
                            ON VenuePanels.PanelID = ljs2.PanelID
                WHERE ' + @alldate + ' AND ' + @where + ') ljs3
                ON VenueInfo.VenueID = ljs3.VenueID
    WHERE (((VenuePanels.PanelID)<>ljs3.[PanelID] And 
        (VenuePanels.PanelID) Not In (SELECT PanelID FROM CampaignAllocations WHERE ' + @alldateprevweek + ')) 
        AND ' + @where + ')
    UNION ALL
     SELECT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, 
    VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, 
    CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
    VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, 
    VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, 
    ljs.AbbreviationCode AS PrevWeekCampaign
    FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
    INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) 
    INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) 
    INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
    LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                    WHERE ' + @alldateprevweek + ') ljs
                ON VenuePanels.PanelID = ljs.PanelID
    WHERE ' + @alldate + ' AND ' + @where

    Select @SQL1

- Malcolm

---

+1，我使用占位符解决了这个问题，然后再替换它们。 - gotqn

我认为这是对这个问题更好的答案：https://dev59.com/q2cs5IYBdhLWcg3wtmID - IHTS

@IHTS 我同意 - 我认为最好让用户直接转到其他的问答页面。 - whytheq

Answer 1

您已将此声明为nvarchar(max)，这允许2GB的数据，因此它将存储2GB。

发生了什么：

• 直到对@sql1进行赋值之前，该数据类型尚不是nvarchar(max)
• 在此之前，它是一组字符串，每个字符串都小于4000 (常数)
• 您正在将短常量与短变量（短=＜4000）连接
• 因此，您已将4000个字符放入@sql1中

因此，您必须确保右侧有nvarchar(max)。

一个想法。第二行将nvarchar(max)与常量连接起来 = nvarchar(max)

SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...
   ....

这与每种语言中发生的整数除法没有任何区别。

declare @myvar float
set @myvar = 1/2 --gives zero because it's integer on the right

运算符优先级（推断数据类型优先级）始终是“分配”最后执行...那么为什么在 SQL Server 中的 Unicode 字符串会有所不同呢？

- gbn

---

+1. 我想补充一下，您可以拥有一个超过8000 varchar(max)或4000 nvarchar(max)的单个字符串常量。但如果它们小于8000/4000，则不会是max类型。 - Shannon Severance

+1 但我认为这很愚蠢。假设对于 MS 来说是可以的，如果你不需要它，那么使用/分配 (MAX) 大小就没有意义，但当你需要它时（例如上面的情况，也是我的情况），我觉得把字符串截断是很愚蠢的。 - gotqn

Answer 2

更新: gbn的回答是正确的，而我错了。根据MSDN的说明，nvarchar(max)支持最多2^31-1字节的数据，以UCS-2存储（每个字符2个字节，再加上BOM的2个字节）。你的问题似乎是字符串连接，并非数据类型限制。

话虽如此，如果你用它来构建SQL字符串，为什么不使用VARCHAR呢？你是否有一些字段名无法由数据库的本地字符集（通常是Latin-1）表示？

最后——你可以通过在存储过程中不使用动态SQL来简化整个问题。创建一些接受where子句字符串并返回表格的表值函数，然后在你的存储过程中JOIN它们。作为奖励，它几乎肯定会更快，因为至少数据库将能够将SP主体缓存为准备好的语句。

- Daniel Pryden

---

这并没有回答问题：@sql1被声明为nvarchar(max)，它允许2GB的存储空间。 - gbn

@gbn：你说得对，我已经编辑了我的答案。不过我仍然认为在这里使用表值函数是更好的解决方案。 - Daniel Pryden

@丹尼尔：是的，或者varchar，或者像你提到的那样使用实际SQL。 - gbn

很遗憾，这对我不起作用。因此，声称只需将nvarchars设置为nvarchar（max），它就会起作用？ - Peter PitLock

Answer 3

我解决了问题，只需要在每个字符串之前包含N个字符，问题就得到解决。例如：

declare @sql nvarchar(max) = '' + @Where + 'SomeThing';

必须是

declare @sql nvarchar(max) = N'' + @Where + N'SomeThing';

如果将字符串设置为空，也必须设置为N''。

if @where is null
set @where = N''

:-) 简单的回答

- Mahdi