A question on perfomance in Tables Vs one table

SQL Server Administration
1

Hi,
I have worked on projects where we would have to move data to a table each week; for example an accounts table. but we did not want to add it to the existing table, it had to be fresh data all to itself. So a lot of people will rename this old with the current date; and through a script create a new accounts table for the new data load.
I like to create a history table with the last field as a default to the date that the data was inserted. This was we do not have a ton of tables building up, that at the very least you have to search through; if you want something.
Question: performance wise is there a difference?
Like does the separate tables take up more space or resources?
Thank you

2

you haven't really given enough specifics to answer your question. however this

So a lot of people will rename this old with the current date;

I find dubious at best. This is an Accounts table. Surely Accounts, once created, never change. So, why would people do this?

In general, though [quote="itm, post:1, topic:7880"]
have a ton of tables building up
[/quote]

is a bad idea, especially the way you're doing it (IIUC)

3

No, a lot of tables are not a good idea.

Typically what causes most people to do that is that they cluster the original table on an identity column, which is useless for processing the table.

Instead, cluster the table properly, that matches the processing you need, and the older rows will never be accessed anyway. At some point you can still move those rows to a history table, but they will not generally be read anyway so it's not as critical to do.

4

Well first you said that you would need more information, what would you need.
Second you want to know why we would be doing this to an account table, well again I have seen this done to numerous different kinds of tables. We are currently doing this be cause we are testing a process and we want to keep, each week the weeks data that was brought in a processes that was tested. So we keep these because we want to compare how the data is as compared to the other loads. And here we are doing this quite a number of different tables.
But again my question is, is there any benefit or determent of doing one or the other.
Thank you

5

The detriment is that code is a nightmare to write and maintain when it has to refer to different tables to get different time periods. And there will be additional overhead if you need to read data from multiple separate tables.

6

Thanks that makes senesce, out of interest, does having multiple tables take up more space or more resources in anyway?

7

Yes. A few extra control blocks are read, and the buffers require separate memory for every table.

8

Thanks that was what I was looking for.
Thank you