Search code examples
sql-server-2008-r2extended-properties

How to save a binary file in extended property?

I use this code to save a string in my extended property,

EXEC sp_addextendedproperty @name = N'SampleProperty1', @value = N'SampleValue1'

My question is, can I use that code when saving a binary file like an image?

Solution

  • I suggest you consider storing the image on disk and setting the extended property to be the path/URL to the image. While an extended property in theory could be any data type supported by SQL_VARIANT (so you could pass varbinary data), the limit is 8,016 bytes, so unless your images are very simple/tiny they're going to be truncated and unusable anyway. Another problem with storing them in extended properties is that within SQL Server you can't really troubleshoot them - you can only show the binary representation, not the image itself, when you use functions like sys.fn_listextendedproperty.