Posted by David Portas on 12/20/05 13:41

serge wrote:

> I was working on figuring out where a certain application was
> storing the multiple selection choices I was doing through the app.
> I finally figured out that they were being store in an IMAGE
> data type colum with the variable length of 26 bytes.
>
> This is the first time I ran into such way of storing multiple
> selections in a single Image data type.
>
> Is this a better alternative than to store into a One-to-Many
> tables? If so then I'll have to consider using the Image data
> type approach next time I have to do something like storing
> 1 to thousands of selections.
>
> Thank you

Define what you mean by "better alternative". This is a very poor
solution if you need to manipulate those values in the database. Much
easier to write TSQL against tables using a foreign key to implement
one-to-many relationships. It isn't likely to scale well either. How
are you going to search efficiently on an IMAGE type that contains
20,000 or 100,000 elements of data? How will you enforce referential
integrity with an IMAGE? It's for those kinds of reasons that
normalization is important.

Also, note that the IMAGE datatype is deprecated from SQL Server 2005
forwards and MS say it will be dropped in some future version.
VARBINARY(MAX) is the new type that provides more functionality,
although it won't answer the problems I mentioned before.

Just what advantage are you looking for that you cannot get from a more
conventionally designed data model?

--
David Portas
SQL Server MVP
--

[Back to original message]