Posted by Dan Guzman on 12/20/05 16:24

> One wonders if the length is a mere 26 bytes, why they used image. A
> varbinary or binary would do.

Image doesn't accept a max column width; the max is always 2GB. I suspect
Serge was mislead by the 'text in row' table option since that is the value
SQL Server reports as the column width for text/ntext/image columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns973281C625A8DYazorman@127.0.0.1...
> serge (sergea@nospam.ehmail.com) writes:
>> 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.
>
> One wonders if the length is a mere 26 bytes, why they used image. A
> varbinary or binary would do.
>
> I can't say that I like this design. The only time I find it defendable,
> is if the database don't have any information of the individual bits,
> but they are handled exclusively by the application and the database is
> just a place where the application saves its persistent data. I would
> expect that to be a technical application for process monitoring or
> some such. One real-world example is the system tables in SQL Server.
> Several of these have status columns that are bit masks. (They are
> integer though.)
>
> For storing selection choices, I would much rather prefer to use a table
> with a row for each choice. A bit mask certainly violates the principle
> of no repeating groups.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]