个人中心
gpt4 book ai didi

sql-server - 在 SQL Server 中按 varbinary (MAX) 或 varchar (MAX) 分组

转载 作者：行者123 更新时间：2023-12-05 00:25:43 24 4
gpt4 key购买 nike

在 SQL Server 2008 R2 联机丛书中，这意味着分组列的最大列大小为 8060 字节。

"For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate worktable that is needed to hold intermediate query results."



但是你 可以 按 varbinary (max) 列分组: 
create table dbo.T(
  name varchar(100),
  info varbinary(max)
);
insert into dbo.T values ('aardvark', Convert(varbinary(max),Replicate(Convert(varchar(max),'A'),100000)))
select info, count(*) from T group by info;

以上在 SQL Server 2008 R2 和大概更高版本中工作正常。我是否误读了在线书籍？有谁知道是否允许按大对象类型分组？我也用谷歌搜索过，但没有结果。

最佳答案

对于固定宽度数据类型，您更有可能遇到此问题，因为它们始终存储在行中。可变长度类型可以用指向实际值的指针存储在行外。

CREATE TABLE #T
  (
     C CHAR(4027) DEFAULT REPLICATE('A', 4027)
  );

INSERT INTO #T
DEFAULT VALUES;

SELECT COUNT(*)
FROM   #T T1
       CROSS JOIN #T T2
GROUP  BY T1.C,
          T2.C

The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.



但以下成功。 
SELECT COUNT(*)
FROM   #T T1
       CROSS JOIN #T T2
GROUP  BY CAST(T1.C AS VARCHAR(4027)),
          CAST(T2.C AS VARCHAR(4027))

您最多可以按 308 个 varchar(max) 列进行分组 - 每列仅存储 26 个字节( pointer to the value and other overhead )。

下面失败了

Cannot create a row of size 8078 which is greater than the allowable maximum row size of 8060.



但删除任何 GROUP BY列，它将成功，因为 8078-26 小于 8060。

注意:每个单独的列都有消耗 10,000 字节的值，但这是存储在行外并且与上述计算无关。 
drop TABLE #T

GO

CREATE TABLE #T
(
 C0 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C1 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C2 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C3 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C4 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C5 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C6 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C7 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C8 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C9 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C10 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C11 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C12 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C13 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C14 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C15 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C16 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C17 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C18 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C19 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C20 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C21 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C22 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C23 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C24 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C25 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C26 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C27 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C28 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C29 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C30 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C31 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C32 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C33 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C34 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C35 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C36 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C37 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C38 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C39 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C40 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C41 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C42 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C43 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C44 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C45 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C46 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C47 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C48 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C49 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C50 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C51 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C52 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C53 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C54 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C55 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C56 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C57 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C58 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C59 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C60 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C61 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C62 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C63 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C64 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C65 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C66 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C67 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C68 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C69 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C70 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C71 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C72 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C73 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C74 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C75 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C76 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C77 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C78 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C79 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C80 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C81 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C82 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C83 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C84 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C85 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C86 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C87 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C88 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C89 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C90 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C91 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C92 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C93 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C94 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C95 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C96 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C97 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C98 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
,C99 VARCHAR(MAX) DEFAULT REPLICATE(CAST('X' AS VARCHAR(MAX)),10000)
)


INSERT INTO #T DEFAULT VALUES


SELECT COUNT(*)
FROM #T T1,
     #T T2,
     #T T3,
     #T T4
GROUP BY 
 T1.C0
,T1.C1
,T1.C2
,T1.C3
,T1.C4
,T1.C5
,T1.C6
,T1.C7
,T1.C8
,T1.C9
,T1.C10
,T1.C11
,T1.C12
,T1.C13
,T1.C14
,T1.C15
,T1.C16
,T1.C17
,T1.C18
,T1.C19
,T1.C20
,T1.C21
,T1.C22
,T1.C23
,T1.C24
,T1.C25
,T1.C26
,T1.C27
,T1.C28
,T1.C29
,T1.C30
,T1.C31
,T1.C32
,T1.C33
,T1.C34
,T1.C35
,T1.C36
,T1.C37
,T1.C38
,T1.C39
,T1.C40
,T1.C41
,T1.C42
,T1.C43
,T1.C44
,T1.C45
,T1.C46
,T1.C47
,T1.C48
,T1.C49
,T1.C50
,T1.C51
,T1.C52
,T1.C53
,T1.C54
,T1.C55
,T1.C56
,T1.C57
,T1.C58
,T1.C59
,T1.C60
,T1.C61
,T1.C62
,T1.C63
,T1.C64
,T1.C65
,T1.C66
,T1.C67
,T1.C68
,T1.C69
,T1.C70
,T1.C71
,T1.C72
,T1.C73
,T1.C74
,T1.C75
,T1.C76
,T1.C77
,T1.C78
,T1.C79
,T1.C80
,T1.C81
,T1.C82
,T1.C83
,T1.C84
,T1.C85
,T1.C86
,T1.C87
,T1.C88
,T1.C89
,T1.C90
,T1.C91
,T1.C92
,T1.C93
,T1.C94
,T1.C95
,T1.C96
,T1.C97
,T1.C98
,T1.C99

,T2.C0
,T2.C1
,T2.C2
,T2.C3
,T2.C4
,T2.C5
,T2.C6
,T2.C7
,T2.C8
,T2.C9
,T2.C10
,T2.C11
,T2.C12
,T2.C13
,T2.C14
,T2.C15
,T2.C16
,T2.C17
,T2.C18
,T2.C19
,T2.C20
,T2.C21
,T2.C22
,T2.C23
,T2.C24
,T2.C25
,T2.C26
,T2.C27
,T2.C28
,T2.C29
,T2.C30
,T2.C31
,T2.C32
,T2.C33
,T2.C34
,T2.C35
,T2.C36
,T2.C37
,T2.C38
,T2.C39
,T2.C40
,T2.C41
,T2.C42
,T2.C43
,T2.C44
,T2.C45
,T2.C46
,T2.C47
,T2.C48
,T2.C49
,T2.C50
,T2.C51
,T2.C52
,T2.C53
,T2.C54
,T2.C55
,T2.C56
,T2.C57
,T2.C58
,T2.C59
,T2.C60
,T2.C61
,T2.C62
,T2.C63
,T2.C64
,T2.C65
,T2.C66
,T2.C67
,T2.C68
,T2.C69
,T2.C70
,T2.C71
,T2.C72
,T2.C73
,T2.C74
,T2.C75
,T2.C76
,T2.C77
,T2.C78
,T2.C79
,T2.C80
,T2.C81
,T2.C82
,T2.C83
,T2.C84
,T2.C85
,T2.C86
,T2.C87
,T2.C88
,T2.C89
,T2.C90
,T2.C91
,T2.C92
,T2.C93
,T2.C94
,T2.C95
,T2.C96
,T2.C97
,T2.C98
,T2.C99


,T3.C0
,T3.C1
,T3.C2
,T3.C3
,T3.C4
,T3.C5
,T3.C6
,T3.C7
,T3.C8
,T3.C9
,T3.C10
,T3.C11
,T3.C12
,T3.C13
,T3.C14
,T3.C15
,T3.C16
,T3.C17
,T3.C18
,T3.C19
,T3.C20
,T3.C21
,T3.C22
,T3.C23
,T3.C24
,T3.C25
,T3.C26
,T3.C27
,T3.C28
,T3.C29
,T3.C30
,T3.C31
,T3.C32
,T3.C33
,T3.C34
,T3.C35
,T3.C36
,T3.C37
,T3.C38
,T3.C39
,T3.C40
,T3.C41
,T3.C42
,T3.C43
,T3.C44
,T3.C45
,T3.C46
,T3.C47
,T3.C48
,T3.C49
,T3.C50
,T3.C51
,T3.C52
,T3.C53
,T3.C54
,T3.C55
,T3.C56
,T3.C57
,T3.C58
,T3.C59
,T3.C60
,T3.C61
,T3.C62
,T3.C63
,T3.C64
,T3.C65
,T3.C66
,T3.C67
,T3.C68
,T3.C69
,T3.C70
,T3.C71
,T3.C72
,T3.C73
,T3.C74
,T3.C75
,T3.C76
,T3.C77
,T3.C78
,T3.C79
,T3.C80
,T3.C81
,T3.C82
,T3.C83
,T3.C84
,T3.C85
,T3.C86
,T3.C87
,T3.C88
,T3.C89
,T3.C90
,T3.C91
,T3.C92
,T3.C93
,T3.C94
,T3.C95
,T3.C96
,T3.C97
,T3.C98
,T3.C99
,T4.C0
,T4.C1
,T4.C2
,T4.C3
,T4.C4
,T4.C5
,T4.C6
,T4.C7
,T4.C8

关于sql-server - 在 SQL Server 中按 varbinary (MAX) 或 varchar (MAX) 分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33694484/

24 4 0
文章推荐： perl - 超时 perl 中的用户输入
文章推荐： angularjs - 如何通过指令更改图像源属性
文章推荐： sql - 在商店过程中添加 dbo 或支持
文章推荐： angularjs - 我可以在服务中使用 $interval 吗？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com