Compressing Data for storage in SQL 2008 Database

[wiebe@CARYA · Accepted solution]

---

@justinthehansen wrote: 

my thought was to make a string out of the number but you are telling me to compress it to Binary?

---

If size is an issue, that's usually helpful (but do read on).

---

@justinthehansen wrote: 

when i view it in the database, wont I just see a string of Binary numbers? 

---

Yes, that would be a consequence. Converting to binary will render it unreadable. Compression would be even worse. Binary would at least be decipherable (by normal humans), for compressed data you (or I) need to run a decompression algorithm.

---

@justinthehansen wrote:

that would be more space than just the original number, right?

---

No, especially if you store singles in stead of doubles. A single is 4 bytes per value. Storing 7 ASCII digits is 8 bytes per value (decimal point), and you need a separator between values. And this is not considering optional exponents (1.234567E-12). Note that storing the binary data also means the data is exactly read back as it is stored. This is not the case for ASCII data storage, as the resolution is limited.

---

@justinthehansen wrote: 

I cant manipulate the binary data in MS access

---

If you've mentioned that as a requirement before, I missed it.

You can't edit it manually easily, as the data won't be human readable.

@justinthehansen wrote: 

so why would i store it that way?

Often, programs write the data and read the data. If no human needs to read it in MS Access, it's a valid solution.

---

@justinthehansen wrote:

 I view it as ASCII so how would i save it as Binary?

---

If you have a string of values, convert it to an array of doubles\singles. Then, flatten the array, or cast it to a string. This will give you a string with the binary values of the array. That string can be (optionally compressed) an stored (to database, file doesn't really matter).

[justinthehansen]

ok, I think this could be my solution.

Ill try to work up an example to make sure i completely understand.

thanks so much!

[ATE-EGNE]

Also, do you have Labview's database connectivity toolkit? The Database Logging (parameterized).vi should help you see how large sets of data can be stored quickly.