June 5, 2012 at 5:52 pm
You guys and gals please let me know if I need to weigh in with a definitive declaration about how this system should be indexed. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2012 at 5:57 pm
TheSQLGuru (6/5/2012)
You guys and gals please let me know if I need to weigh in with a definitive declaration about how this system should be indexed. 😀
Sure... whatcha got, Kevin?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2012 at 8:48 pm
Jeff Moden (6/5/2012)
TheSQLGuru (6/5/2012)
You guys and gals please let me know if I need to weigh in with a definitive declaration about how this system should be indexed. 😀Sure... whatcha got, Kevin?
Actually I was just being funny - I don't have time for a good read of this thread. Quick read results in this analysis:
1) Do need another table for location-specific information
2) We likely don't have the full story here, but if all or most of querying is done as stated then I agree with clustered index being date, locationid with both of those being not null and the CI being declared as unique assuming won't get 2 rows for same location within 3ms (datetime datatype spread). You can use larger more precise datatype if desired perhaps to ensure 'uniqueness' of records for given timestamp. As for that combo NOT being unique, I agree with identity and adding that as 3rd column to make it unique instead of letting the engine uniquefy it under the covers. The identity gives you another benefit of itself being unique and possibly allowing for efficient access via that alone if necessary.
3) This is a huge thread for someone's homework. 😉
Best,
Kevin
P.S. Scott probably drinks frequently from the Joe Celko fountain. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply