title: Design question on storing meteorological data on SQL Server 2008
body: <p>We're using SQL Server 2008 R2 Enterprise Edition.</p>
  
  <p>We are measuring meteorological data from what we call MetMasts.
  Basically this is a mast with lots of equipment; anemometers (for wind speed) at different positions on the mast, thermometers , and air pressure.
  We measure every second.</p>
  
  <p>And it takes up tooooo much disk space.
  The next generation of this equipment will generate over 10 GB per year each. And we’re going to have more than 1000 of these.</p>
  
  <p>The current table design looks a bit like this:</p>
  
  <pre><code>    CREATE TABLE #MetMast (
      MetMastID INT NOT NULL IDENTITY(1,1), 
      MetMastName NVARCHAR(100), 
      CountryID INT, 
      InstallDate DATE
  )
      CREATE TABLE #MetMastData (
          MetMastDataID BIGINT NOT NULL IDENTITY(1,1),
          MetMastID INT NOT NULL,
          MeasuredAt DATETIME2(0) NOT NULL,
          Temperature REAL NULL,
          WindSpeedAt10m REAL NULL, 
          WindSpeedAt30m REAL NULL,
          AirPressure REAL NULL,
          OneHundredMoreColumns VARCHAR(200),
       CONSTRAINT PK_MetMastData PRIMARY KEY CLUSTERED 
      (
          MetMastID ASC,
          MeasuredAt ASC
      ))
      WITH (DATA_COMPRESSION = ROW) 
      -- ON a file group, with table partitioning
      ALTER TABLE #MetMastData WITH NOCHECK ADD CONSTRAINT FK_MetMast_MetMastID FOREIGN KEY (#MetMast) REFERENCES #MetMast(MetMastID)
  </code></pre>
  
  <p>The data is write once, read many, many times.<br>
  We use it in our data warehouse, where a typical question would be; Count how many times there is a 2 m/s difference between WindSpeedAt10m and WindSpeedAt30m when the temperature is above 20 degrees, per MetMast. </p>
  
  <pre><code>SELECT MetMastId, COUNT_BIG(*) FROM #metMastData 
  WHERE temperature&gt;20 AND ABS(WindSpeedAt10m-WindSpeedAt30m) &gt;2 
  GROUP BY MetMastID
  </code></pre>
  
  <p>In the future a tiny bit of data loss will be accepted.<br> 
  We’re talking lossy compression of data here. I know we will have to define an acceptable error for each of the fields, as in 1% if we measure with 10% accuracy.<br>
  It worked for sound files (MP3 is quite big), so it might work for us as well.<br></p>
  
  <p><strong>But how is this done?<br>
  What table design should I go for? <Br>
  How do I get started with lossy compression of data in database tables?<br></strong></p>
  
  <p>Best regards,</p>
  
  <p>Henrik Staun Poulsen</p>