Is there a big technical difference between VARBINARY(MAX) and IMAGE data types?
Solution 1
They store the same data: this is as far as it goes.
"image
" is deprecated and has a limited set of features and operations that work with it. varbinary(max)
can be operated on like shorter varbinary
(ditto for text
and varchar(max)
).
Do not use image
for any new project: just search here for the issues folk have with image
and text
datatypes because of the limited functionality.
Solution 2
I think that technically they are similar, but it is important to notice the following from the documentation:
ntext, text, and image data types will be removed in a future version of MicrosoftSQL 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.
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.
Solution 3
They store the same data: this is as far as it goes.
"image" is deprecated and has a limited set of features and operations that work with it. varbinary(max) can be operated on like shorter varbinary (ditto for text and varchar(max)).
Do not use image for any new project: just search here for the issues folk have with image and text datatypes because of the limited functionality.
In fact, VARBINARY
can store any data that can be converted into a byte array, such as files, and this is the same process that IMAGE
data type uses, so, by this point of view, both data types can store the same data.
But VARBINARY
have a size property, while IMAGE
accepts any size up to the data type limits, so when using IMAGE
data type, you will spend more resources to store the same data.
In a Microsoft® SQL Server®, the IMAGE
data type is really deprecated, then you must bet in VARBINARY
data type.
But be carefull: The Microsoft® SQL Server® CE® (including the latest 4.0 version) still using IMAGE
data type and probably this data type will not "disappears" so soon, because in Compact Edition versions, this data type is better than any other to fast files storage.
Junior Mayhé
Brazilian Software Engineer graduated in Information Systems. Fluent in English and Spanish. ITIL and SCRUM certificated professional. Solid experience throughout the life cycle of software development. Working for over 15 years designing, building and deploying solutions for areas like Human Resources, Finance, Publishing, Telecommunications, Entertainment, Healthcare, Logistics and Energy. International experience interacting with professionals from several countries like Italy, Belgium, Portugal, Holland, USA, Colombia, Spain and Mexico. 10 years of experience in systems analysis, requirements gathering, object-oriented analysis, data modeling, technical specification. 15 years in systems development, maintenance, testing for national and international companies. 4 years interacting with professionals from different countries, of which 2 years playing technical project leadership role abroad, delegating, and following up tasks. 3 years negotiating with infrastructure and service providers. 2 years defining the IT tools and infrastructure to be implemented and processes to be improved, covering software, hardware, communications needs, to meet the needs of the organization. 2 years organizing and supervising the acquisition and update of computer systems, whether hardware or software (licenses, servers, contracts). Specialties: Systems Analysis, Requirements Analysis, Functional Analysis, Software Development, Project Management, SCRUM Agile methodology, Waterfall methodology, ITIL, Software Testing, Continuous Integration, C# programming language, ASP.NET MVC, Entity Framework, Windows Forms, WPF, WCF, .NET Core, ASP.NET Web API, JavaScript, Angular 6, TypeScript, JQuery, HTML5, CSS3, XML, XSD, XAML, SQL Server, PHP, MySQL, MongoDB, BPM, UML, GIT, Prototyping, Database Modeling and Administration Personal Site | Linkedin profile
Updated on June 08, 2020Comments
-
Junior Mayhé about 4 years
I was reading on internet these statements about SQL Server data types:
VARBINARY(MAX)
- Binary strings with a variable length can store up to 2^31-1 bytes.IMAGE
- Binary strings with a variable length up to 2^31-1 (2,147,483,647) bytes.
Is there a really big technical difference between
VARBINARY(MAX)
andIMAGE
data types?If there is a difference: do we have to customize how ADO.NET inserts and updates image data field in SQL Server?
-
Andy Joiner about 11 yearsLimitation of VARBINARY(MAX): Partial updates are not supported on columns that have a FILESTREAM as a source. (tekkies.co.uk)
-
yoel halb almost 10 yearsAccording to codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream it is really possible to save it, and it is even using it for testing, and also it is possible to read the text directly in the file, which might be great for testing, so I don't see a difference as long as you the required casts, and of course thee is usage for strings not just for testing but also for saving text based articles (though the correct naming for this is not "Image")