Microsoft SQL Server forum - Store Multi-Select values in an image data type?

Author

Store Multi-Select values in an image data type?

serge

2005-12-20, 3:23 am

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

David Portas

2005-12-20, 7:23 am

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
--

Erland Sommarskog

2005-12-20, 7:23 am

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dan Guzman

2005-12-20, 9:23 am

> 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:Xns973281C625A8
DYazorman@127.0.0.1...
> serge (sergea@nospam.ehmail.com) writes:
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

serge

2005-12-20, 9:23 am

Thank you both for your answers.

I am looking at an OLD SQL CRM application that
lets you create Views (application related Views and
not SQL Views) of your Companies list. You
select company names from all the Companies list
and then save the Views for later re-use.

For example the app displays me 50,000 companies
and I manually select 10 of them and save my
first view and give it a name "My Top 10 clients".

Another example I would select "My Top 100 clients"
and another "My Top 1000 clients".

If I do this right now in this app those 1000 ClientNos
will be stored in one IMAGE column on the same
record where my View information "My Top 1000 clients"
is being saved.

If I run the SQL Profiler every time I save my
View I see a lot of calls for "sp_cursorfetch" (I think)
or when I call my View to load I see a lot of cursor calls
(I am not sure if I remember if they were actually cursor
calls) but I saw a #Temptable being created and each
selected ClientNo's Name being inserted to this temp table.

Well it didn't look nice the SQL Profiler statements so
that's why I was just trying to see if the developer(s) of
this old CRM I was looking at had good reason(s)
to store the selections of a user into a single column.

Based on your answers if I need to implement such
a scenario I will stick to One-to-Many table relationship
for all the reasons you have explained.

Thanks again

Sponsored Links