Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: table design for massive GPS data Date: Sat, 15 Sep 2012 19:21:13 +0200 Organization: Erland Sommarskog Lines: 40 Message-ID: References: <37389863-6d11-4092-9f04-2e27dbccbd2b@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="9c1143bfab35549403b85d70ea1a57d8"; logging-data="32147"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18l664vTuHAfI9xFE4XMlJB" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:TMrxW3cSJvtwIJnAqWwYazpHd6o= Xref: csiph.com comp.databases.ms-sqlserver:1289 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