I've got a routine that'll be creating individual tables (Sql Server 2008) to keep the outcomes of reviews produced by my application (Asp.internet 3.5). Each report will require its very own table, because the posts for that table would vary in line with the report configurations. A table will contain approximately 10-5,000 rows, rarely a lot more than 10,000.
The next usage rules will apply:
- Once saved, the information should never be up-to-date.
- Whenever recent results for the table are utilized, all data is going to be retrieved.
- Not one other table will have to execute a join with this particular table.
Knowing this, can there be any reason to produce a PK index column up for grabs? Will doing this aid the performance of locating the information by any means, and when it might, would this over-shadow the additional load of upgrading the index when placing data (I understand that 10K records is really a relatively bit, but this solution must have the ability to scale).
Update: Listed here are more particulars around the data being processed, which matches in to the current design decision of 1 table per report:
- Tables will record some number values (set at runtime in line with the report configurations) that correspond to another group of reference varchar values (also set at runtime in line with the report configurations).
- Whenever information is retrieved, it some publish-processing around the server is going to be needed prior to the output can be shown towards the user (thus I will be locating all values).
I'd be also concered about someone declaring they needed to produce a new table for every time the report was run. However, considering that different posts (in number, title and datatype) could certainly be required for each time the report was run, I do not see a fantastic option.
The only real other factor I'm able to think about is with an ID column (determining the ReportVersionID, akin to another table), ReferenceValues column (varchar area, that contains all Reference values, inside a specified order, separated by some delimiter) and NumericValues column (just like ReferenceValues, as well as the amounts), after which after i retrieve the outcomes, put everything into specialized objects within the system, separating the values in line with the defined delimiter). Performs this appear more suitable?
Primary secrets aren't essential for every data tables. True, they're usually quite helpful and also to abandon them is foolish. However, additionally to some primary missions of speed (which To be sure would doubtfully be positively affected) can also be those of originality. To that particular finish, and pricing the consideration you've already clearly taken, I recommend the just have for any primary key is always to govern the expected originality on the table.
Update: You pointed out inside a comment when you probably did a PK it would come with a name column that presently doesn't exist and isn't needed. Within this situation, I'd advise from the PK altogether. As @RedFilter stated, surrogate secrets never add any value.
I'd make it simple, just keep report results transformed into json or xml, inside a VARCHAR(MAX) column