英文:
Calculating the maximum size of BLOB in the BLOB column in SQL Server 2016 database
问题
我有一个名为Table1的表，其中在列blob_column2中包含大量的BLOB数据。
我想要计算存储在此列中的BLOB的最大大小（以KB或MB为单位）。
我需要此输入以进行数据迁移。
是否可以帮助编写查询以计算BLOB的最大大小？
SQL Server数据库版本为2016（v13.0）。
英文:
I have a table Table1 with large amounts of BLOB data in a column blob_column2.
I want to calculate the maximum size of the blob (in KBs or MBs) stored in this column.
I need this input for migrating the data.
Can someone help with the query to calculate the maximum size of the blob?
The SQL Server database version is 2016 (v13.0).
答案1
得分: 1
以下是翻译好的部分：
问题措辞不清楚，因为没有展示任何尝试，所以只能猜测正在询问什么。
您可能正在询问如何找到最大的 blob 大小。查找最大值可以使用 MAX 函数：
SELECT MAX(DATALENGTH(BlobField))
FROM ThatTable
这对迁移来说可能不太有用，除非您迁移到具有像 MySQL 这样的限制长度的 blob 字段的数据库，其中 BLOB 字段只有 65KB。与 varchar(max) 等价的是 LONGBLOB。可能还会涉及到缺少的功能，比如 FILESTREAM 存储。
或者您可能正在询问所有 BLOB 的 总 大小，这在存储计算中很有用：
SELECT SUM(DATALENGTH(BlobField))
FROM ThatTable
您可以同时计算多个统计信息：
SELECT
SUM(DATALENGTH(BlobField)) as TotalSize,
SUM(DATALENGTH(BlobField))/1024.0 as TotalSizeKB,
MAX(DATALENGTH(BlobField)) as MaxSize,
MAX(DATALENGTH(BlobField))/1024.0 as MaxSizeKB,
AVG(DATALENGTH(BlobField)) as AvgSize,
AVG(DATALENGTH(BlobField))/1024.0 as AvgSizeKB
FROM ThatTable
请注意，除了存储供应商，其他人都将 1 KB 定义为 1024 字节，包括文件系统创建者。存储供应商将其定义为 1000 字节。
英文:
The question is badly worded and since no attempt is shown, one can only guess what's being asked
You may be asking how to find the larges (maximum) blob size. Finding the maximum is done with MAX:
SELECT MAX(DATALENGTH(BlobField))
FROM ThatTable
That's not very useful for migration, unless you migrate to a database where blobs have length restrictions like MySQL, where BLOB is just 65KB. The equivalent to varchar(max) is LONGBLOB. There may be more issues from missing features like FILESTREAM storage.
Or you may be asking for the total size of all BLOBs, which is useful in storage calculations:
SELECT SUM(DATALENGTH(BlobField))
FROM ThatTable
You can calculate multiple statistics at once:
SELECT
SUM(DATALENGTH(BlobField)) as TotalSize,
SUM(DATALENGTH(BlobField))/1024.0 as TotalSizeKB,
MAX(DATALENGTH(BlobField)) as MaxSize,
MAX(DATALENGTH(BlobField))/1024.0 as MaxSizeKB,
AVG(DATALENGTH(BlobField)) as AvgSize,
AVG(DATALENGTH(BlobField))/1024.0 as AvgSizeKB,
FROM ThatTable
Keep in mind that everyone except storage vendors defines 1 KB=1024 bytes. That includes file system creators. Storage vendors define it as 1000 bytes
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论