I've got a table by which 3 rows of information are added per second as well as in that we plan to keep around 30M rows. (Older data is going to be removed).
I have to give a column: varchar(1000). I can not tell ahead of time how it is content is going to be but I know it will likely be very repetitive : 1000's to countless rows will have a similar value. It is almost always about 200 figures lengthy.
Since information is being added utilizing a Saved Procedure I see two option
- Give a column varchar(1000)
- Produce a table (int id,varchar(1000) value) Inside the StoredProcedure, look when the value exist for the reason that other table or create it I'd expect this other table to possess a more 100 value whatsoever time.
I understand a few of the tradeoff between both of these options however i have a problem creating my thoughts around the question.
Option 1 is heavier however i get faster card inserts. Requires less joins hence query are simpler. Option 2 is lighter place take longers but query have the possibility to become faster. I believe I am nearer to normal form however I in addition have a table having a single significant column.
In the information I gave you, which option appears better? (You may also develop an alternative choice).
It's also wise to investigate page compression, possibly that you can do the easy factor but still obtain a a little(ant) table. Although, should you have to say is SQL Express, you will not have the ability to utilize it out of the box a company Edition requirement.
I have tried personally frequently during my projects your next approach. Every place would need to undergo a saved method that will get the research value id, or card inserts a replacement otherwise found and returns the id. Specifically for such large posts much like your appears to become, with lots of rows yet so couple of distinct values, the saving in space should trump the extra overhead from the foreign key and research cost in query joins. See also Disk is Cheap... That's not the point!.