From: esquel@sommarskog.se   
      
   migurus (migurus@yahoo.com) writes:   
   > First idea is to keep data normalized in one table, one record per GPS   
   > reading. It looks very simple to implement, but I doubt the server will   
   > be happy with this high number of small records.   
      
   Why not? As long it is properly indexed, I don't see any problem.   
      
   What you should consider though, is to partition the table (which   
   requires Enterprise Edition). When five years has passed, and you   
   want to purge old data, this is a swift affair with partitioning,   
   provided that your partition aligns with what you want to purge.   
      
   > I we were to do this, what would be a good candidate for primary key here?   
      
   (VEHICLE_ID, GPS_TIME). Although, you should probably have GPS_TIME as the   
   first column in the clustered index to avoid fragmentation. GPS_TIME would   
   also be your partitioning column.   
      
      
   > So, the table would look:   
   > create table idea2 (   
   >      VEHICLE_ID     int     not null   
   > ,     RUN_DATE     date     not null   
   > ,     GPS_READINGS     varbinary or whatever appropriate to keep daily   
   > portion of readings   
      
   You may save some space with this, because you save row overhead. But you   
   get a much more complex solution which is more difficult to understand and   
   maintain. And if the neeeds changes in the future and people want to run   
   queries like "which vehicles have been in that area at some time?", that   
   is hopeless with the above. I strongly recommend against it.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)