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.

Thanks BlD

  • Can you add a sample of the data? (not all 255 columns of course, just a few of them a few rows.) And explain what do you mean with "unique fields"? – ypercubeᵀᴹ Jul 16 '14 at 13:02
  • Lets say, your columns are 01,02,03,04 -- To be honest, they are numerical; The data on the other hand is a string representation of binary data. 04 05 30 20 04; Though, I may change it to a binary field as I believe converting it to a binary would save a lot more space compared to nchar(#) – BlndLeadingDef Jul 16 '14 at 16:07
  • What do you plan to do with the data once it's in the database? – Jon Seigel Jul 17 '14 at 1:44
  • As @jonSeigel asked, the type of queries you plan to run on the data is the crucial question. In any case however, if the sensor data are numeric in nature and you plan to have any querying on them like aggregates (average, sum, whatever), I don't think storing them in binary format will be good. SQL-server has the tinyint datatype that can hold values from 0 to 255 and needs just one byte (and a bit more if the column is nullable). – ypercubeᵀᴹ Jul 17 '14 at 8:56
  • Apart from the above there are a number of tables that define the sensors and parameters specified for each test. I have those in manageable tables tied to each test via a foreign key; Based on the parameters I am going to pull the data from the columns I care about. For example, grab column 3,4,6,24,200-212 from sensor's that have the parameters X. Return that data. – BlndLeadingDef Jul 17 '14 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.