What would be the best way to store one valuer per hour per day per customer? We have a table that stores a value per each hour per each per day per customer. A simplification of the table would be like this (removed some extra fields and the indexes):

CREATE table #timevalues (
dateinsert date,
customerid int,
hour0 int,
hour1 int,
....
hour23 int
)


The problem is that this table is huge, and altough we have an index with the dateinsert and customerid that allows us for a fast seek of the record, retrieving the record also takes time. Our issue is more with the speed and being future proof than with the space.
What would be a recomendation to improve this?
We were thinking about replacing the column format to store a json instead of the fixed columns, but the result would be less optimal I guess.
We regularly need to get stuff like:


Daily totals customer XXX in last month
Average per hour per day
If the values don't change too often, I would use and Slowly Changing Dimension (Type 2) approach. In this case, you will store a new row only when your value changes. This is mostly common used on Data Warehouses to track historical data.

In a nutshell:

1) Change your table to something like this:

CREATE TABLE dbo.CustomerHistory (
CustomerHistoryKey int identity (1,1),  -- PK and surrogate key
CustomerId int NOT NULL,
Value int,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL 
)

ALTER TABLE dbo.CustomerHistory ADD  CONSTRAINT PK_CustomerHistory  PRIMARY KEY CLUSTERED 
(CustomerHistoryKey)

CREATE UNIQUE NONCLUSTERED INDEX [BK_CustomerHistory] ON dbo.CustomerHistory 
(CustomerId, StartDate)

CREATE UNIQUE NONCLUSTERED INDEX [IX_CustomerHistory_currentRow] ON dbo.CustomerHistory 
(CustomerId, EndDate)
WHERE EndDate = CAST('99990101' AS datetime)



Avoid using nulls on StartDate and EndDate. Instead, choose a lower and higher date value, such as 19000101 and 99990101


2) Continue running your routine every hour to check if Value was changed, but only add a new row for those customers with changes. To add a new row, do:


DECLARE @ChangeDate datetime = GETDATE()
Set the current row EndDate equals to @ChangeDate
Add a new row with the updated Value with StartDate equals to @ChangeDate and EndDate equals to '99990101'


How it works:


You will store much less data if your values doesn't change to often
If you need to create a relationship pointing to a specific point in time, use CustomerHistoryKey
If you need to check one value in time, you can search by CustomerId and time &gt;= StartDate and time &lt; EndDate


Check the following link for an quick overview: https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row