1

I have a base 64 image with 78232 characters (didn't put the encode because of obvious reasons).

When I save to a VARCHAR(max) column in SQL Server, I'm not getting the entire value. I'm getting a string with only 43679 characters

Since I read that VARCHAR(max) can store up to 2^31-1 = 2.147.483.647 characters I think something is wrong.

Can someone explain why?

gregoryp
  • 920
  • 4
  • 15
  • 35
  • 2
    How are you checking the value? I think this is an issue with SSMS. If you just use `LENGTH()` you should see the full length. – Gordon Linoff Feb 27 '18 at 13:03
  • @GordonLinoff Yes, you're right! The length is correct, but how to retrieve all text then? – gregoryp Feb 27 '18 at 13:07
  • 2
    . . Here is one method: https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement. I've used something similar in the past. – Gordon Linoff Feb 27 '18 at 13:09
  • @GordonLinoff thank you very much – gregoryp Feb 27 '18 at 13:24
  • 1
    You could also store the **binary image** into a `VARBINARY(MAX)` column, and avoid the time- and space-consuming "Base64" encoding .... – marc_s Feb 27 '18 at 13:30
  • Right click on SSMS query window and select "Query Options..." option. On Query options popup there is setting under "Results >>Grid" tab for "Maximum Characters Retrieved". – DatabaseCoder Feb 27 '18 at 13:34
  • 1
    `43679` is an *oddly specific* number. And in fact if you searched on `SQL Server 43679` you'd have found plenty of results. – Damien_The_Unbeliever Feb 27 '18 at 14:04
  • Classic example of why choosing the correct datatype is so important. – Sean Lange Feb 27 '18 at 14:16
  • 1
    @Damien_The_Unbeliever . . . I can empathize with the OP. When I first encountered this, I assumed there was some error on my part and wasted a lot of error trying to find a bug. I would never think that 43,679 is some magic number in software. 65,535 -- yes, but not 43,679. Anyone know where it comes from? – Gordon Linoff Feb 27 '18 at 14:44
  • 2
    @GordonLinoff According to [this](https://github.com/aspnet/EntityFrameworkCore/issues/7209) the limitation is in the results pane of SSMS query window and is mentioned as a known issue. I think "obscure" might be a better adjective if true. – SMor Feb 27 '18 at 16:04
  • @GordonLinoff Yes, that's pretty obscure. But thank you very much for your explanation. – gregoryp Feb 28 '18 at 14:14

0 Answers0