|
Home > Archive > MS SQL Server New Users > November 2005 > Multivalue Table field
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Multivalue Table field
|
|
| Chip99 2005-11-08, 4:10 pm |
| Hi
I've a table with some fields and one of those fields is a field with
different amounts of values (|122|,|12|,|32|). Each value of this field is a
pointer to another table witch has those numbers as references (for example:
122 is the ADKey and further fields are on this table: Price, Tax,
Description and so on). Now I'd like to make a view whitch reads all those
values in this field to get the description and the price.
I know that it's possible to make this with an work-table between those 2
Tables but I wonder if this is still possible to work with this "3D" field.
Thanks for your inputs.
Greetings
ruenef
| |
| Adam Machanic 2005-11-08, 4:10 pm |
| Can you re-work the schema to do away with these "multivalue fields"? This
is really a worst-practice, for a couple of reasons:
A) It's extremely difficult to ensure data integrity. Can you guarantee
that every row in the table has the right data in the right format?
B) No matter how you design the retrieval logic, it's going to be nearly
impossible for the query to make use of indexes. This means table scans and
poor performance.
Best bet is to normalize your data. If you can provide more information on
the business problem and post some sample data, I'm sure we can help you
come up with something better.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Chip99" <Chip99@discussions.microsoft.com> wrote in message
news:85E4AA33-7294-45A1-87B6- 3441562A5576@microso
ft.com...
> Hi
> I've a table with some fields and one of those fields is a field with
> different amounts of values (|122|,|12|,|32|). Each value of this field is
> a
> pointer to another table witch has those numbers as references (for
> example:
> 122 is the ADKey and further fields are on this table: Price, Tax,
> Description and so on). Now I'd like to make a view whitch reads all those
> values in this field to get the description and the price.
>
> I know that it's possible to make this with an work-table between those 2
> Tables but I wonder if this is still possible to work with this "3D"
> field.
>
> Thanks for your inputs.
> Greetings
> ruenef
>
|
|
|
|
|