Why would someone store text in an SQL Image field?

Question

I've been asked to get some info out of the database of a system we use. I've found what I'm looking for, but its left me... confused.

For some reason, the developers have the system storing text in an SQL Image field. I suspected this, but wasn't entirely sure, until I took the Hex output from my select statement, put it into a Hex to ASCII converter, and hey presto, it's exactly the kind of message I'm looking for.

What I'd like to know is: why? Is there some legitimate reason why someone would store text in an Image field?

Given the quite serious and private nature of these messages, the only thing I can think of is that someone was seriously mistaken in thinking that storing the text in an Image field would somehow make it more secure? Or is there really just something obvious I'm missing?

EDIT: Covered in the comments, but: At minimum, this text will occasionally likely include accented characters and such. Plus, my manager just let me know that the software was written using SQL Server 2000, or maybe even a version or two earlier. Possibly the simple answer is that there wasn't an analogue to nvarchar or similar back when it was written?

Answer 1

It seems like there are several possible choices, but without going back to the original developers of your system, there's no way to be sure. Some of the possibilities include:

• Conversion from another system. If the developers wrote their software against database engine X (Oracle, MySQL, Access, SQL Anywhere, etc.) and then converted their system to SQL Server 2000, it is possible their conversion tools / methodology was not "smart" enough to use the right data type for their data.
• Improper UTF-8 encoding. As mentioned by others, it could be that the programmers didn't know how to encode UTF-8 or other non-ASCII data, and chose Image as a fallback storage method.
• Data obfuscation. While not an encryption method, storing text in an image field, as you note, makes it non-trivial to view. Perhaps the programmers didn't want to take the time/effort to figure out a true encryption method, and thought the Image data type was "good enough" to hide the text from DBAs like yourself.
• Lack of Database expertise. I've seen more than one case where the programmer chose some "default" data type for a column without really understanding what they were doing. In at least one case, it was because they used their programming language's IDE to define the database, rather than developing the schema through the engine's preferred database design tools. This can often lead to interesting data types that aren't appropriate for the data or choosing deprecated data types, etc.
• Data column "sharing." It is possible the programmers, for whatever reason, are using this image column to store text in some cases, pictures in other cases? It is not best practice by any means, but I've seen systems where similar shortcuts were taken.
• Source data conversion. I don't know where your source text originates. It is possible that the application isn't inserting raw text into an image field, but is uploading a file that contains text in human-readable form. Some document formats have human-readable text inside them, along with control codes. I recall that back in the day, WordPerfect documents stored data in a binary file type, but the actual text was mostly human readable if you opened the file in a text editor. The formatting control codes made lots of random "garbage" in the data, but you could extract the text if you wanted to. If your application uploads files like these, then the raw text might still be viewable in the column. In which case, an "image" data type isn't wrong.
• Feature change over time. It is possible the Image data type was the appropriate type at one time in the design of the application, but the purpose changed over time with no change to the underlying database? Maybe they uploaded files originally, but later switched to inserting text instead. Or maybe they inserted text initially, but planned to eventually upload files instead of text. This is lazy programming; the schema should be changed over time to match the data, but again, this happens often enough...

Answer 2

The only options to store data over 8000 bytes was text, ntext, or image back in the days of SQL Server 2000. The appropriate choice for long character data strings would be text or ntext, with text appropriate for single-byte characters tied to the column collations code page and ntext for Unicode (UCS-2).

I can only speculate as the why developers used the binary image type to store character data but it could be in order to store characters as UTF-8, which is more compact that ntext UCS-2. Although this method saves space, it does come with the cost of not being able to easily evaluate the character data in T-SQL; encoding/decoding of UTF-8 stored as binary needs would need to be done in the application code.

Answer 3

At minimum, this text will occasionally likely include accented characters and such

NTEXT (older and now deprecated though still supported in SQL2016) or NVARCHAR(MAX) (the preferred option since its introduction) should cover that for most purposes.

N[VAR]CHAR types use UCS-2 which can only naturally work the Basic Multilingual Plane. It can store UTF16 using multiple characters for those on other planes (this can cause some confusion working directly in TSQL as string functions don't understand this may double-count or split such characters), but maybe your app's designers were not aware of this and were expecting non-BMP characters and were expecting UCS2 to be a problem for that.

They may also be wanting to store text as UTF8, as suggested by Dan's answer, because that is usually more efficient space-wise than UCS2 or UTF16.

the software was written using SQL Server 2000 earlier. Possibly the there wasn't an analogue to nvarchar when it was written?

It probably isn't that. as TEXT and NTEXT have existed since at least SQL Server 6.5, two versions prior to 2000. They were stored less efficiently back then, but so were IMAGE types (SQL Server 7 improved these matters and there have been further improvements since). My experience doesn't go further back than that and I can't quickly find any useful online reference for older versions.

Another possibility is that the designers intended for it to be able to hold formats other than text, perhaps image data and so forth, and while this didn't make it into the final product support for it may not have been entirely refactored out from early prototypes.