101 reputation
1
bio website
location
age
visits member for 3 years, 2 months
seen 8 hours ago

Jan
31
comment Strategy for SQL Server Partitioning
Yes, I could do that. But we also have a requirement to make the data available for reporting as soon as it arrives. So we do need to switch the data in every hour. I guess there is nothing I can do to improve the situation.
Jan
28
awarded  Supporter
Jan
28
comment Strategy for SQL Server Partitioning
Hi, Remus, I had read up on the partition limitation. Storing data every hour is not exactly the requirement: we receive data by the hour but for a given day - not for a specific hour. So we end up storing data for a company only one instance per day (inspite of this being updated every hour at a given point every day, say 1800 hrs, we would say that is the official data for company A for date X). Thank you for your response. It is helpful to know that I am not missing any tricks. Please also take a look at my comment to mrdenny as that sheds some light on the motivation.
Jan
28
comment Strategy for SQL Server Partitioning
Hi, mrdenny, the goal is to keep the archive table as free of write operations as possible. The archive table is heavily indexed and is the source for client application queries. If the hourly receive data is written directly into the archive table then we would have problems of index fragmentation, statistics getting outdated and locks being taken - all of which would affect query performance. Let me know if you think differently. Thanks
Jan
27
asked Strategy for SQL Server Partitioning