sql server 2005 image类型字段截取问题

[amplifer]

有一张表存储的图片，直接二进制存入image类型的字段中，网页上查询显示。
最近发现查询速度很慢，开始以为是索引问题，建了索引依然很慢。
经过排查发现image类型的字段存储的长度很大，全部是1MB，实际上图片只有几十KB。
也就是存入数据库的时候没有按照实际的图片大小来插入，而是全部都保持一个默认的长度（1MB）。
并且没有字段记录实际的长度，我用select datalength (value)得到的结果也的确是所有记录都是1MB的长度。
我尝试了用以下方法都不行，结果都变成了字符串，而不是原始的二进制文件。
select substring (value, 0, 100 * 1024)
select cast (substring (value, 0, 100 * 1024) as varbinary)
请问下大家，有没有类似substring能截取image类型，并且得到的还是二进制数据的函数？

[二月十六]

很少有在数据库中存image的，一般都是存上图片的服务器路径，然后在外边直接引用路径即可。

[amplifer]

引用 1 楼 sinat_28984567 的回复:

很少有在数据库中存image的，一般都是存上图片的服务器路径，然后在外边直接引用路径即可。

现在的系统确实都是直接存路劲，文件直接保存硬盘上。 这是以前的老系统，有很多数据了，现在查询很慢想优化一下，版主有什么建议吗？

[吉普赛的歌]

CREATE TABLE img_data(
	id INT IDENTITY(1,1) PRIMARY KEY,
	img VARBINARY(max)
)

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms187993%28v%3dsql.90%29 微软已不推荐 image 类型， 改成 varbinary(max) 试试。 其实最好的办法是数据库不要存储图片， 保存图片后在数据库中保存图片路径即可。 大数据类型特别是图片在数据库中存储， 始终是一块短板，比用文件存储慢是绝对的，不要试着去挑战。

[卖水果的net]

老系统就别在数据库做这个截断操作了，放在程序中吧。

[amplifer]

楼上两位版主，道理我都懂，我现在新做的系统都是直接上传文件到服务器的制定目录存储到硬盘上，然后保存路径到数据库的。 但是现在这个是老系统，已经有几十万上百万的数据量了，没办法改动，所以只能在查询页面做优化。 截取的目的是为了提高页面的查询速度，目前的传输很慢，我在查询分析器里面也试过了，就是因为保存了很多空白的数据。 比如图片本来是50K大小，数据库的记录里面却保存了很多多余的数据，所以想的是截断。 刚才试了下select cast (substring (value, 0, 100 * 1024) as image)，但是图片只显示了一小块，增大这个数字也没用，不知道啥原因。。

[吉普赛的歌]

引用 5 楼 zeal808 的回复:

楼上两位版主，道理我都懂，我现在新做的系统都是直接上传文件到服务器的制定目录存储到硬盘上，然后保存路径到数据库的。 但是现在这个是老系统，已经有几十万上百万的数据量了，没办法改动，所以只能在查询页面做优化。 截取的目的是为了提高页面的查询速度，目前的传输很慢，我在查询分析器里面也试过了，就是因为保存了很多空白的数据。 比如图片本来是50K大小，数据库的记录里面却保存了很多多余的数据，所以想的是截断。 刚才试了下select cast (substring (value, 0, 100 * 1024) as image)，但是图片只显示了一小块，增大这个数字也没用，不知道啥原因。。

#3 中让你换数据类型， 你试了吗？

[卖水果的net]

楼主试试，把图片在另一张表存一下，看看用多大空间

[amplifer]

引用 6 楼 yenange 的回复:

#3 中让你换数据类型， 你试了吗？

你的意思是直接把image类型换成varbinary(max)，然后查询不用截取了是吗？我在本机上试了一下，查询速度很慢啊。

[amplifer]

我在本机模拟了一下，用select datalength(value)可以明显看到前面几条数据的长度太大了，很多无用的数据。

而最后的两条是正常的数据，实际上才40K+。
之前帖子正文还说错了，这里数据都10多M了，蛋疼的设计。

[amplifer]

问题解决了，substring似乎默认最大只支持8000bytes，所以要先转换类型，再截取就OK了。

select substring (cast (value as varbinary(max)), 1, 300 * 1024)

修改前的时间：

修改后的时间：

速度提升非常明显，感谢各位的支持！