vbinary vs Image SQL Server Data Type来存储二进制数据?

I need to store binary files to the SQL Server Database. Which is the better Data Type out of Varbinary and Image?

我需要将二进制文件存储到SQL Server数据库。从Varbinary和Image来看，哪一种数据类型更好?

4 个解决方案

#1

123

Since image is deprecated, you should use varbinary.

由于不赞成使用图像，所以应该使用varbinary。

per Microsoft (thanks for the link @Christopher)

每个微软(感谢链接@Christopher)

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

在Microsoft SQL Server的未来版本中，将删除ntext、文本和图像数据类型。避免在新的开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序。使用nvarchar(max)、varchar(max)和varbinary(max)代替。

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

固定长度和可变长度数据类型，用于存储大型非Unicode和Unicode字符和二进制数据。Unicode数据使用Unicode UCS-2字符集。

#2

varbinary(max) is the way to go (introduced in SQL Server 2005)

varbinary(max)是实现的途径(在SQL Server 2005中引入)

#3

There is also the rather spiffy FileStream, introduced in SQL Server 2008.

还有在SQL Server 2008中引入的令人眼花缭乱的文件流。

#4

https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql

image

图像

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. Still it IS supported to use image datatype, but be aware of:

变长二进制数据从0到2 ^还有(2147483647)字节。仍然支持使用图像数据类型，但要注意:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql

varbinary [ ( n | max) ]

varbinary [(n | max)]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

变长二进制数据。n可以是1到8000的值。马克斯表示最大存储大小是2 ^还有字节。存储大小是输入的数据的实际长度+ 2字节。输入的数据长度可以为0字节。varbinary的同义词ANSI SQL是二进制变化的。

So both are equally in size (2GB). But be aware of:

所以两者大小相等(2GB)。但是要注意:

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016#features-not-supported-in-a-future-version-of-sql-server

https://docs.microsoft.com/en - us/sql/database engine/deprecated数据库引擎-功能- sql - server - 2016 # features-not-supported-in-a-future-version-of-sql-server

Though the end of "image" datatype is still not determined, you should use the "future" proof equivalent.

虽然“image”数据类型的结束仍然没有确定，但是您应该使用“future”证明等效项。

But you have to ask yourself: why storing BLOBS in a Column?

但你必须问问自己:为什么要在列中存储BLOBS ?

https://docs.microsoft.com/en-us/sql/relational-databases/blob/compare-options-for-storing-blobs-sql-server

#1

123