This is a question more so on the table definition vs actual programming.
I have 6 sets of potentially 255 unique fields. I have a potential of getting 40 million records per year of these.
For the most part, I'm working with old and new data. The older the data, the more empty the data is. The newer the data, the more fields are filled.
I'm using SQL Server 2014, is it more efficient to make a table with 255 rows, or, create a mapping table to single data byte rows?
I guess the question boils down to: Is large amount of rows (something like 200x as large) better than a sparsed table?
Previous suggestions at (incorrectly posted at SO) mentioned normalizing. I have attempted to do that using the mapping table of string data. The problem lies mostly in the uniqueness of the string fields which is, unfortunately, mostly unique. Creating mapping per field would increase the cost more than decrease applicable situation. If I were to do that anyways, I feel that a sparse table would be better across the board.
Unique: This is data gathered about a situation; For example if we take a sample of the air right now, there's particulates in it. The data contains the values of the particulates in the air. Each sample is uniquely different, but, there will be 255 samples. (This was an example; In my case, I am gathering data that represents itself in strings; Though it's binary encoded so I could convert to binary.) Unfortunately each sensor I use, doesn't have to report all 255 samples. They can report whichever they please. So, I could use sensor A, which reports 1-20, sensor B which reports 3,9, 40-90.
What I plan to do with this data: There are tables that describe the test executed and the sensors used. These tables, are going to be my parameters to my query. I plan on querying: If sensor a, with parameters X = 2, Y = 2, Z = 2, exists, present me the data in columns 3-5, 25-190, and 252. This is a simple data returned query. That's going to be mostly what I do.
Once again, thanks for your time - replies, and posts.