0

I want to set up a table to store waveform data from an oscilloscope. The table would have a column of varbinary[max] because the waveform data from the scope is returned as a binary sequence that ranges from 1,400 bytes to 14,000,000 bytes. I would have a half dozen more int and small varchar for parsing information. So each row in the table will represent all information related to this waveform.

I am a complete noob to working with databases. I just set this one up and created my insert queries. I have no idea how using such a large type may affect responsiveness, speed, or anything else.

I can say that I will never want to use this varbinary column to do any filtering or searching. This table is mostly for record storage.

3
  • If you drop a table that contains a VARBINARY(MAX) column with the FILESTREAM attribute, any data stored in the file system will not be removed. source, that link was found here stackoverflow
    – Luuk
    Jan 2, 2020 at 18:53
  • 1
    @Luuk that's specific to storing the VARBINARY(MAX) in FILESTREAM, outside of the database, which wasn't mentioned, and isn't available in Azure SQL Database. Jan 2, 2020 at 18:57
  • After reading just a little about FILESTREAM it seems like that may be the best way to store my data. Thank you for the information about this. I do not plan to drop this table any time soon, but this is good to know. Jan 2, 2020 at 19:01

1 Answer 1

2

[MAX] columns are to be considered like BLOBs (Binary Large Objects) and are physically stored on another "page". This means that practically, they are like on another table, seamlessly linked to the one with the rest of the data. One of the main limitations is that you can't index the [MAX] column.

I don't see any issue in you doing things as you suggest.

5
  • 2
    By default small BLOBs are stored in the row, and larger ones are stored in separate pages. This is controlled by the 'large value types out of row` table option. learn.microsoft.com/en-us/sql/relational-databases/… Jan 2, 2020 at 18:59
  • 1
    You are right, I dismissed that a bit quickly as by default it is OFF and whenever I see something over 8000 chars, I take the mental shortcut of considering it "too big".
    – Pierre
    Jan 2, 2020 at 19:02
  • 1
    @DavidBrowne-Microsoft Per the document you linked the default value for "large value types out of row" is 0. Is that correct? Jan 2, 2020 at 19:06
  • 1
    Correct. So by default large value types are stored in the row, if they are small enough. Jan 2, 2020 at 19:07
  • Are you sure about that first comment? The doc your linked reads [Text in row: When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.] So by default, BLOBs are stored our of row, however small. Does not apply to for varchar though, as you said. Up to 8000 in row.
    – Pierre
    Jan 2, 2020 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.