I have a table like this (with more columns though):
CREATE TABLE [dbo].[MyTable]( [SnapKey] [int] NOT NULL, [SnapDt] [smalldatetime] NOT NULL, [Company] [varchar](4) NOT NULL, [ProfitCenter] [varchar](10) NOT NULL, [CostCenter] [varchar](10) NOT NULL, ) ON [MyPartition]([SnapKey]) CREATE CLUSTERED INDEX [IDX1] ON [dbo].[MyTable] ( [SnapKey] ASC )
Table is partitioned on SnapKey. SnapKey is the date part of SnapDt, stored as an integer, for example 20160131 and 20160229.
Each partition contains only 1 SnapKey. For each SnapKey partition, I have about 5 million rows. Currently I'm keeping only the last day of each month in my table.
I always use SnapKey for querying. No updates happen to the data. For each day, data is populated into the table and then we run some reports on it during the month.
Question: If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?
I couldn't find any clear answer, so I tried to populate the table with data but I ran out of storage, so I decided to ask you, to see if there is any theoretical explanation.
By storing 5 more days, we will store 6 times the data (for historical reporting). We will keep the last 6 days of the month instead of only the last day.
Our queries will not change and our reports are still over 1 single day (one SnapKey).
We have one SnapKey per month. For now we have
20160131 20160229 20160330
...and so on. One SnapKey for each month-end.
By having 5 days more, SnapKey will look like:
20160126, 20160127, 20160128, 20160129, 20160130, 20160131 20160224, 20160225, 20160226, 20160227, 20160228, 20160229 20160325, 20160326, 20160327, 20160328, 20160329, 20160330 ...and so on
You'll see that we store 6 times more data, but still work on only one SnapKey in our queries. This means we always have:
WHERE SnapKey = xxxxxxxx
in all of our queries.