0

I do know that there is a answer here

But I want to know these:

  1. I know how to save them programmatically, but I don't know what data type to save them? Could I still use image datatype when I have converted my images as image to 256KB? Or should I save them into filestream data type?

  2. What kind of fragmentation is the document about? In my scenario, I have 2k+ employees and counting, so that means my application would get bigger over time, If I saved the images as image, and did not followed the rules as stated by the research paper, would my images lose their intact picture? (e.g a picture with blue background somehow changes its color maybe?)

  3. Should I save it in the exact table of my employee details table? Or should I save it in a different table?

  4. Sorry but I did not really understand the NTFS thing, is it the RAM of my computer? Or the RAM of my server? And I am using a file server as my database, should I still convert my images or should I go with using image data type without the conversion?

Community
  • 1
  • 1
  • 4
    Save them as `VarBinary(MAX)` – Trevor Jul 18 '16 at 01:06
  • `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Jul 18 '16 at 06:47
  • And also: `FILESTREAM` is *not* a separate datatype - it's just an *attribute* that you can add to `VARBINARY(MAX)` columns – marc_s Jul 18 '16 at 06:48

1 Answers1

2

If you want to save files of any type in SQL Server then you should use the varbinary data type. You would use varbinary(max) unless you know that the files will be small. You can then convert your images to a Byte array and save that to the database.

You can enable the FILESTREAM option on your SQL Server instance these days and it will then store the data outside the main MDF file although that will be transparent to your code, so you keep using the same queries as you would otherwise.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • Storage is cheap, it's non sense to not just make it a varbinary(max) type. Just my opinion... – Trevor Jul 18 '16 at 02:13
  • whats the difference between varbinary and image data type? i am now converting my images to 256KB, should i still use varbinary? –  Jul 18 '16 at 02:31
  • 2
    The most significant difference between the two data types is that `image` is deprecated and `varbinary` is not. Had you read the documentation for the `image` data type, you'd have seen that it instructs you to use `varbinary` instead. – jmcilhinney Jul 18 '16 at 02:34
  • So should i save the image together with my employee details table? or should i save it to another table?( e.g. emppic table) –  Jul 18 '16 at 06:23
  • There's no specific need to create a separate table just because you want to save the contents of some files. Just treat it like any other data. Put it in a separate table if there's a logical reason to do so, otherwise don't. For instance, if you might want to be able to link the same document to multiple record then a separate table makes sense. – jmcilhinney Jul 18 '16 at 07:18