October 31, 2012 at 2:49 pm
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
October 31, 2012 at 2:54 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2012 at 2:58 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2012 at 3:29 pm
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
October 31, 2012 at 9:05 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply