2

I am experimenting with a possible data structure for an app of mine, and I need to provision a column in one of my SQL Server data tables to hold various data of unpredictable size.

Literally, this could mean a string of text, or a Base64 encoded video clip and everything in between.

I realize that the instant response is going to be that I should provision different tables for different types -- and I don't disagree -- but please humor me here.

varchar(MAX)?  
nvarchar(MAX)?

I am not a DBA so I don't know what type gives me the most flexibility for the lowest storage cost.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt Cashatt
  • 23,490
  • 28
  • 78
  • 111
  • textual data and a base-64 encoded video clip are both strings, just of varying length. Do you want to store *other* types of data (in native format)? – Adam Robinson Jan 15 '12 at 01:27
  • At the moment, I can't imagine that I would ever need to store anything other that an extremely long string of text. Any media can be encoded in Base64 format, correct? Are you thinking that I may need to store bytes? Thanks for your comment. – Matt Cashatt Jan 15 '12 at 01:29
  • For storing Base64 encoded string VARCHAR(MAX) should do. Check out http://stackoverflow.com/questions/2397624/which-sql-data-type-to-store-base64-encoded-file – vmvadivel Jan 15 '12 at 01:35

2 Answers2

7

VARBINARY(MAX)?

In principle, trying to force multiple different data types into a single type is a bad idea. You may be better served with a different table for each type. But if you're never going to search the field, you should be able to do anything with a binary field...

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thanks Dems! I am reading up on this datatype now (want to be prudent for the sake of other SO users before selecting this as the winning answer). Off the top of your head, do you know how much data this type takes vs. other MAX types? Or does it even matter these days since storage is so cheap? I know we aren't supposed to solicit opinions, but yours would really be helpful. Thanks. – Matt Cashatt Jan 15 '12 at 01:39
  • Object size + 2 bytes, I think. And no, I would not worry about the differences in space use. Choose the correct type, don't compromise to make a barely perceptible optimisation that you'll nearly never need. – MatBailie Jan 15 '12 at 01:42
  • Great advice! Thanks to all who posted but this answer seems to be the winner. Thanks again Dems!! – Matt Cashatt Jan 15 '12 at 01:44
1

Consider using the xml datatype. It will permit you to store, query and index arbitrary XML documents.

John Saunders
  • 160,644
  • 26
  • 247
  • 397