Storing Files greater than 8000 bytes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Storing Files greater than 8000 bytes

Storing Files greater than 8000 bytes

Rate Topic

Display Mode

Topic Options

Author

Message

tina_cpc

Posted Wednesday, October 31, 2012 2:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:17 PM
Points: 1, Visits: 2

Hi,

Is there task which will be capable of uploading/storing a file in a database which can be greater than 8000 bytes?

Thanks,

tinac99

Post #1379535

Evil Kraig F

Posted Wednesday, October 31, 2012 2:54 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:09 PM
Points: 5,658, Visits: 6,100

Can you expand on what you're looking for a little bit?

File storage is usually done in VARBINARY(MAX). Under most scenarios the front end should be delivering/retrieving these files, not directly inserting them via a SQL Server INSERT INTO, as there's little to nothing SQL will be able to do with it other than hang onto them.

Also, a word of note, you really want to look into BLOB (or LOB) optimizations before you go down this path. Large Object Binaries are notoriously evil for performance and maintenance if you don't do things like include them in separate filegroups and prepare for the maintenance load. In general storing these in the database should be avoided altogether unless you need point in time recovery to be synced between these files and other data in the database. If that's not required, you're usually best off simply storing a filepath for the front end to recover the file itself directly.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1379540

Sean Lange

Posted Wednesday, October 31, 2012 2:58 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 8,560, Visits: 8,213

tina_cpc (10/31/2012)

Hi,

Is there task which will be capable of uploading/storing a file in a database which can be greater than 8000 bytes?

Thanks,

tinac99

Not sure what you mean exactly by task but yes you can store large files. There are a couple of ways you can store files with sql server.

You can have a system folder and store only the location in the database. This is kind of the old school way of dealing with this. It is kind of pain to keep straight because the file system and dbms can get out of synch.

As an alternative, you can store the actual file in the database. Storing the file in the database can be accomplished a couple of ways. The first is to use varbinary(max) datatype. This will store the file as a binary in the actual table. The other approach is to use FILESTREAM.

There is no "best" way to handle this. It depends some on the details of what you are doing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1379542

haiao2000

Posted Wednesday, October 31, 2012 3:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:38 PM
Points: 132, Visits: 336

tina_cpc (10/31/2012)

Hi,

Is there task which will be capable of uploading/storing a file in a database which can be greater than 8000 bytes?

Thanks,

tinac99

If it is the first time you think of storing files in the database, maybe you need to think again, we all love to have everything like images, video, music files, etc. in the database, but unless you plan to store just a few files. The reality is storing that kinda stuffs in the DB is extremely expensive in all dimentions. Take Sean's old school suggestion and you won't go wrong for sometime down the road

Post #1379551

Lynn Pettis

Posted Wednesday, October 31, 2012 9:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 21,592, Visits: 27,401

Depending on your DR/HA requirements, you could also look at using the filestream to store the files, sice it looks like you are using SQL Server 2008 based on the forum you are asking the question.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1379591

« Prev Topic | Next Topic »

Permissions