JohnWrensby JohnWrensby - 21 days ago 6
ASP.NET (C#) Question

Should a large static table go in separate database?

I have a SQL Server 2012 table that is 100MB with 1 million records. This is static lookup table that would at the most change once per year. The only query is greater/less than on two columns to return a scalar. It is used in a standard ASP.Net web application, EF, IIS etc. Should I put this in a separate database? Backups of main db would be smaller? What are the advantages and disadvantages? As far as any EF Code First pains, I could always just read it with a SQLQuery.

Answer

100 Mbytes is not very large, so it should be immaterial to backup sizes. If you are worried about file sizes in the megabytes, then it is time to upgrade your infrastructure.

Your question is on the right path, though. Databases are the unit of backup and recovery.

For such a small table, I would say to just keep it in the database. This is simpler and it makes the application simpler because the application only has to deal with a single database. Also, in the event that the table does change, you will be capturing the changes without doing additional work or creating unexpected bugs.

If the table size were 100 Gbytes, then you might be thinking about another database.