SQL Server: store more integers in a single value

Question

I'm not sure how to explain this probably, so I'll use an example.

I have to store (etc.) some users ID in a single value/column in my SQL Server table, so one value could look like this "4231 3271 3722 7432 4831 3192 ". of course this can be stored as a text, char, nchar, nvarchar and som on. But which is the best? if I have to only store integers(and something to separate them)?

So what is the best way to store multiple integer values inside a single SQL Server cell?

And you don't have to post me any alternative ways to do this, as this is just an example, and it is the only right way to do this, even if I have to use varchar :)

Answer 1

I know you asked us NOT to provide alternate ways of doing this, but here goes:

Normalize!!!

Basically, you create another table with a minimum of 2 columns (UserId and the primary key of the table you described above). This way, you can have any number of User IDs for a single record in this table. You also have the value of placing only ONE User ID in a single column.

Now... If you want to do it the way you described anyway, here is what you can do:

You cannot put multiple integers in a single column and still use the datatype int. You will have to use a text type of some kind (varchar, nvarchar, etc). You should have a common, easy to read delimiter (if they are all integers, then a space or comma will work just fine).

Doing this will cause you problems down the road, and I really encourage you to do it with normalization.

Answer 2

Well you can't store it as a numeric type, since there are space separators. You'll want to choose whichever type uses as little space as possible, while still meeting your requirements. See the tables on this page: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

It seems like char or varchar would make the most sense for your purposes. And since your ID numbers are all the same length (I assume), a char with a defined length seems like the best option.

Answer 3

If these numbers really do represent different values, then I'd agree with Gabriel:Normalize.

But to answer your actual question, you could use fixed width numbers within an int, i.e. 111222333

Answer 4

what is the best way to store multiple integer values inside a single SQL Server cell?

Simple - you don't.

1. CHAR and VARCHAR have a maximum of 8,000 characters. The spaces in your example impact that as well - in your example, the integers with a length of 4 plus the 1 character for the space means you could have a maximum of 1,600 distinct values. Even if you stored the values in XML, which SQL Server supports up to a max of 2 GB, you'll have to use XPATH/XQUERY to get the values out of the XML markup.
2. There's reliable way to keep the data consistent (spacing, characters) when using this approach, making getting the data out a problem waiting to happen.

The Best approach is to normalize the data:

1. There's no limitation to the number of associations
2. Reliable data enforcement - an INT column won't allow alphabet characters
3. Data manipulation performance is far better, because there's no conversion

Answer 5

I agree with the others: Normalize. But I'll add an actual solution as well, which is to use a CLR type. hierarchyid is technically a sequence of integers, but it's difficult to get at the actual "numbers" stored inside. You can write your own CLR data type to store a nested set and generally get better type safety and efficiency than storing it as a varchar or something similar.

Please note, I am not actually recommending this for typical databases. Nested sets do have their uses, but those uses are fairly rare. You should always use 3NF for an OLTP database unless it's a performance issue.