-1

I have a table with this config:

enter image description here

Now if you look at COLUMN_11, it is a varchar -1.

But as far as I know, to insert a PDF file into a table, you need filestream, and the column should be VARBINARY(max) right?

so, how is this working? why is there a PDF in a VARCHAR column?

the DocumentBody is this:

enter image description here

Obviously theres millions of this one.

Improve this question
2
  • How do you or somebody else inserts rows in this table?
    – McNets
    Feb 12, 2021 at 12:02
  • 1
    Filestream is not required to insert data into MAX types.
    – Dan Guzman
    Feb 12, 2021 at 12:41

1 Answer 1

Reset to default
5

When you see -1 as the value for a VARCHAR or NVARCHAR data type, that is just a numeric representation for MAX for the .NET SQLDBTYPE as stated here.

So it looks like the application probably just stores the serialized encoded string representation of the PDF in the database in a VARCHAR(MAX) column then.

I assume the developer who designed this thought it was the easiest way to store the PDF, as opposed to VARBINARY(MAX), which would save you space (as Dan Guzman notes in the comments). Additionally it's actually best practice to not store files themselves in the database, but rather on a file system somewhere, and store the location reference to that file in the database instead.

Improve this answer
3
  • Specifically, the value looks to be base64 encoded string of the original binary value. IMHO, it would be better to store the raw value as varbinary(MAX) to save space.
    – Dan Guzman
    Feb 12, 2021 at 12:31
  • @DanGuzman Yes probably so. Likely the developer behind the app wasn't aware of this and choosing VARCHAR was the easiest solution they knew of, so they went with it, I'm guessing.
    – J.D.
    Feb 12, 2021 at 12:33
  • 1
    Tahnks guys this is what I was thinking too. it was easier at the time, so lets do it.
    – Racer SQL
    Feb 12, 2021 at 12:46

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