I have a table with a few standard fields plus a JSON document in one of the columns. We use SQL Server 2017 to store everything and data is consumed by a C# application using Entity Framework 6. The table itself will possibly have tens of thousands of entries, and these entries (or rather, the JSON column on them) will have to be updated daily. I'm trying to figure out what data type to use for the best performance.
I've read this:
SQL Server 2008 FILESTREAM performance
And currently, JSON documents came as files ranging from 30-200 KB. There is a possibility of going above the 256 KB barrier, but the probability of going above 1 MB is currently very low. That would point to NVARCHAR. Also, here:
What's best SQL datatype for storing JSON string?
People suggest that storing JSON as NVARCHAR(MAX) is the way to go.
However, two things worry me:
- First, is fragmentation over time, with so many writers (that's one of the areas Filestream seems to have an advantage no matter the column size). I'm not sure how will that affect the performance...
- Second, I'm not sure whether storing so much text data in the database won't slow it down due to the size alone? As far as I understand it, another advantage of FileStream is that database size cost is pretty constant, no matter the file size on the disk, and that helps to maintain performance over time. Or am I wrong?
What would you choose, given my use case?
It is not just a matter of performance, but also a matter of development time, knowledge and maintenance. If everything is already in c# using the entity framework, why would you use something more complex? My approach would be to use the solution the developers are most comfortable with until a performance bottleneck occurs and then to benchmark.
Comparison between two solutions with realistic table sizes will give you a real insight on if any adaptations are worthwhile.