Sql-server – Split or combine tables

database-designsql server

Background

I have a table with the below format:

id(int) type(varchar) Name(varchar) Description(varchar) Value(decimal)

There are three different types possible, data from each of these types will never be compared against each other and for all intents and purposes are completely unrelated apart from the fact that they can share this table structure.

This is a very small table (10 000 rows), however it will be regularly joined with tables that have around 10 million rows based on the id.

Question

Would splitting the table into three separate tables based on the type be beneficial for performance? If so would it be a negligible difference?

Doing so requires little modification to existing code, and no additional types will ever be added.

From a design and readability point of view is it preferable to have this table together or split, considering that in other instances in the DB separate tables exist for each type as they do not share a common structure.

Best Answer

Splitting won't give you much performance gain. Joining this table using ID (Should have Clustered Index created for this columns) will give you a better performance.