Msg#: 4332474 posted 4:02 pm on Jun 29, 2011 (gmt 0)
You **could** use varchar. But something tells me you are likely to use programming operations on these - splitting up the array **after** reading in. A more efficient way would be to use a second table and joins:
If you use this approach, you can store your numbers as int fields, which would be much faster - it would also allow you to leverage the power of mysql searches. With the varchar method you'd have to resort to (unnecessarily) complicated regexps. Instead you can do
select main_table.title as title,array_table.array_member where main_table.id=array_table.record_id and array_table.array_member = 22
title|member --------------- widget|22 widget|22
You can also explore left, right, and inner joins if there are null values (no records in the joined table), etc - all of which you can't do storing a comma-separated list of values. Faster results, more possibilities.