What is the best ways of storing unstructured data in sql server 2016, except filestream.

Question

What is the best ways of storing unstructured data in sql server 2016, except filestream.

Abhi kr

SSChasing Mays

Points: 644
More actions
November 15, 2017 at 2:17 am

#324569

What is the best ways of storing unstructured data in sql server 2016, except filestream.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Abhi kr - Wednesday, November 15, 2017 2:17 AM
What is the best ways of storing unstructured data in sql server 2016, except filestream.

What kind of unstructured data? XML? JSON?
😎

Abhi kr SSChasing Mays Points: 644 More actions · Answer 2

Abhi kr

SSChasing Mays

Points: 644

November 15, 2017 at 4:04 am

#1968147

data will be of type documents - pdf , doc etc

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

VARBINARY(MAX) columns. Doesn't have to be filestream, normal VARBINARY(MAX) columns work OK.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abhi kr SSChasing Mays Points: 644 More actions · Answer 4

GilaMonster - Wednesday, November 15, 2017 4:14 AM
VARBINARY(MAX) columns. Doesn't have to be filestream, normal VARBINARY(MAX) columns work OK.

I have implemented using Varbinary(max) only , but we are expecting huge data in our pdf or doc files ,
does it will impact performance of our query ?

Please suggest .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Abhi kr - Wednesday, November 15, 2017 5:19 AM
GilaMonster - Wednesday, November 15, 2017 4:14 AM
VARBINARY(MAX) columns. Doesn't have to be filestream, normal VARBINARY(MAX) columns work OK.
I have implemented using Varbinary(max) only , but we are expecting huge data in our pdf or doc files ,
does it will impact performance of our query ?

Yes, it will have a negative impact as they''ll displace a lot of the buffer pool every time they're queried. That's from storing things in the wrong place. Huge files don't belong in a DB, that's why Filestream was created.
But if you insist on not using filestream, then you don't really have a lot of options. The only place you can put a binary file over 8kb in size is a varbinary(max) column.

Have you considered not putting these things in a relational DB?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Rather than storing the large data in the database and if you don't want to use filestream, the simply store the directory/path information of where the files are stored on disk with the understanding that they could "go away" because other people aren't as careful with files as DBAs are with "data".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

And the unasked question, why can't you use filestream or filetable?

Tom Uellner SSCrazy Points: 2995 More actions · Answer 8

Jeff Moden - Wednesday, November 15, 2017 9:07 AM
Rather than storing the large data in the database and if you don't want to use filestream, the simply store the directory/path information of where the files are stored on disk with the understanding that they could "go away" because other people aren't as careful with files as DBAs are with "data".

We have a similar setup. We have to store a lot of PDF documents and they are stored to a share that only the app and network admins have access to. That helps cut down the other people not being careful part.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

TUellner - Thursday, November 16, 2017 9:14 AM
Jeff Moden - Wednesday, November 15, 2017 9:07 AM
Rather than storing the large data in the database and if you don't want to use filestream, the simply store the directory/path information of where the files are stored on disk with the understanding that they could "go away" because other people aren't as careful with files as DBAs are with "data".
We have a similar setup. We have to store a lot of PDF documents and they are stored to a share that only the app and network admins have access to. That helps cut down the other people not being careful part.

Heh... so did we. It was the app and network admins that made a mess. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)