Multiple tables or 1 big table

Ward 21 Reputation points


I'm going to log the consumption (water, electricity, gas, ect) for plenty of devices (take 400 devices which log every hour and we need to keep the data at least 2 or 3 years) .

Now i was wondering if i can better put them in 1 table with the following colums:
Type: Water, Electricity or Gas
Device: Which device we are logging
Value: The actual value
Date: date when the record was logged

Or can i better create a table for every device with only 2 column (Value and Date).

Because when i need the data for reporting, i never going to need all the devices (report we want to make are 32 devices).
I can make the Type and Device column indexed, but performance wise isn't it better to read only the tables i need?
Or is it more difficult to read multiple tables at the same time?

Thanks at advance!
Kind regards,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,590 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 36,091 Reputation points

    400 devices x 24 h x 365 days x 3 years = 10.5 million records, that is very less data; go the way with one table.
    I have tables with billions of records and no performance issues.

    One table per device has the disadvantage, that you always have to add a new table if you get a new device, you have to change reports, and so on.

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ward 21 Reputation points

    @Olaf Helper thanks for the reply.

    Will go for the one table then.

    0 comments No comments

  2. Cris Zhan-MSFT 6,596 Reputation points


    From the perspective of the size, complexity and use(report) of the data that needs to be stored, or the performance of the query , I think you only need to create a total table include all devices.
    It is easier to query the data of 32 devices from one table than to combine results from 32 independent tables.

    0 comments No comments