473,700 Members | 2,879 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Store Multi-Select values in an image data type?

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
Dec 20 '05 #1
4 3005
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
--

Dec 20 '05 #2
serge (se****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 20 '05 #3
> 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" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1... serge (se****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 20 '05 #4
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

Dec 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

37
4888
by: ajikoe | last post by:
Hello, Is anyone has experiance in running python code to run multi thread parallel in multi processor. Is it possible ? Can python manage which cpu shoud do every thread? Sincerely Yours, Pujo
4
4664
by: Frank Jona | last post by:
Intellisense with C# and a multi-file assembly is not working. With VB.NET it is working. Is there a fix availible? We're using VisualStudio 2003 Regards Frank
12
3876
by: * ProteanThread * | last post by:
but depends upon the clique: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=954drf%24oca%241%40agate.berkeley.edu&rnum=2&prev=/groups%3Fq%3D%2522cross%2Bposting%2Bversus%2Bmulti%2Bposting%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den ...
6
8168
by: cody | last post by:
What are multi file assemblies good for? What are the advantages of using multiple assemblies (A.DLL+B.DLL) vs. a single multi file assembly (A.DLL+A.NETMODULE)?
4
17869
by: mimmo | last post by:
Hi! I should convert the accented letters of a string in the correspondent letters not accented. But when I compile with -Wall it give me: warning: multi-character character constant Do the problem is the charset? How I can avoid this warning? But the worst thing isn't the warning, but that the program doesn't work! The program execute all other operations well, but it don't print the converted letters: for example, in the string...
0
1326
by: Mark | last post by:
Hi all, I am just playing around with writing a simple space invaders game and was wondering what was the best way to store the layout of the "aliens". I am currently using a multi-dimensional array to store all the "invader" objects.. invaders Which stores 15 invader objects (5 across, three down). When I want to display the "aliens", or figure out which alien has been "shot", I simply
2
2530
by: Winshent | last post by:
I have a multi line text in an admin page on my cms. I am trying to capture carriage returns as and replace them with <p></p> bfore the string gets written to the database. I have tried all the charcontrols option and chr(13) with success.. was using the following code: Dim s As String = MyTextbox.Text
2
1347
by: Alan Silver | last post by:
Hello, I am designing a form that allows people to request the formation of a limited company. When they fill in the form, they have to supply a certain amount of information relevant to their choice (company name, address, various other options). I was thinking about doing this with a wizard control as that allows me to split the form into manageable chunks. One of the features is that they can optionally specify a number of
33
13337
by: Prasad | last post by:
Hi, Can anyone please tell me how to store a 13 digit number in C language ? Also what is the format specifier to be used to access this variable ? Thanks in advance, Prasad P
0
2326
by: Sabri.Pllana | last post by:
We apologize if you receive multiple copies of this call for papers. *********************************************************************** 2008 International Workshop on Multi-Core Computing Systems (MuCoCoS'08) Barcelona, Spain, March 4 - 7, 2008; in conjunction with CISIS'08. <http://www.par.univie.ac.at/~pllana/mucocos08> *********************************************************************** Context
0
8721
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9209
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9069
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8964
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
4400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4653
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3085
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2385
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2024
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.