Storing Email Body in SQL Server database?

Question

Guys I am building a bulk email sending application for my client and right now I am designing the database architecture. Basically there will be hundreds of thousands emails per day and I need to store them in database.

What will be the best way to store the email's body in database? Do I store them in varchar(max) column, or do I save them in file system and save their path in the database? Or is there any other approach to this? I am only worried about performance of the application. Btw I am using SQL Server 2008 R2.

Answer 1

Generally I don't recommend building bulk email sending activities as there are a lot things to be done to avoid considering your email as spam

However if you decided to do it your self you need to decide the content of the emails, Is it text only, HTML that may contains embedded images,...

You can use varchar(max) for the field type. performance will not be a big issue however consider thinking about the retention policies

if you would like to save the email as file then you can use FILESTREAM which will provide you with better performance given that you use the SqlFileStream APIs