Using SQL Server Read/Write Image Ever Day With A Table

LRC1006 0 Reputation points
2024-04-25T12:58:15.02+00:00

I've a case that need to save Image in database, than user can search/upload site(DB) to site(DB) ever day from winform.

Every image size average 250kb - 350kb, The total usage with image perhaps growth 110GB/year without delete form database.

I'll will using varbinary(MAX) to stored this, I want to now the sql server performance and another better way to do this case

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,835 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2024-04-25T21:27:08.46+00:00

    One alternative is FILESTREAM. In this case you are still storing the images in SQL Server, but the images are actually stored transactionally in the file system, but logically still part of the database. For the best performance, you should use the OpenSqlFilestream API, but that adds extra complexity.

    While your images are decently big, the limit when FILESTREAM pays off is usually given as 1 MB, so you are below that limit.

    The other alternative is store the images in the file system and only store the file path in SQL Server. This may be faster, and reduces the size of the database. But in case of a disaster it can be difficult to make a consistent restore of file system and database.

  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor
    2024-04-26T02:36:52.5966667+00:00

    Hi @LRC1006

    Referring from this doc:

    Objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read: write ratio and rate of object overwrite or replacement are important factors.

    Also, check the answer in this similar thread: Storing images in SQL Server?

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments