| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-11-15 : 17:13:52
|
| DECLARE @aGuid UNIQUEIDENTIFIERSET @aGuid = CAST(GETDATE() AS BINARY(8)) + CAST(CAST(NEWID() AS BINARY(8)) AS UNIQUEIDENTIFIER)This will cause the beginning bytes of records created one after another to be same or similar.In http://www.informit.com/articles/article.asp?p=25862&seqNum=5, Jimmy Nilsson recommends tomake the trailing bytes same or similar in his COMBs for best scalability. I will domore testing before we proceed, but my thinking is:- Our DB is unlikely to hold more than a few hundred thousand records in the largest table, so the dropoff in performance may not be significant- We don't have to store the creation date/time separately and have one more index - the first 8 bytes of the GUID are the creation date/time- Uniqueness should not be a problem with the first 8 bytes of newid() added to a time stamp that has a 3.3ms resolution.Any comments? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-15 : 19:15:27
|
| All true - but why compromise.Is it so bad to have a compound index?>> We don't have to store the creation date/time separately and have one more index Do you know what a compound index is?>> Our DB is unlikely to hold more than a few hundred thousand recordsSo what's the problem with a compound index?And why do you want a guid anyway? Have a look at identities.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-16 : 01:19:07
|
| "Uniqueness should not be a problem with the first 8 bytes of newid() added to a time stamp that has a 3.3ms resolution"And what about on the odd occasion when it is? This is for merge replication, right? Bad things will happen when there is a collision in the replication!Kristen |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-16 : 14:50:12
|
quote:
Originally posted by Kristen
Bad things will happen when there is a collision in the replication!
What are the bad things? |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-16 : 14:56:02
|
quote:
Originally posted by nr
All true - but why compromise.Is it so bad to have a compound index?
In the current version of the product every ID comprises 5 fields and there are composite indices on those. Dealing with multiple fields as an ID is a pain in foreign keys and we have a lot of those. I've been wanting single ID fields for a long time.The DB will use merge replication, that's why we want to use GUIDs. We don't want to deal with offsets in identity colums. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-16 : 15:42:54
|
| "Smart Keys" (keys that have multiple meanings) are not really a good idea. If you intend to actually query the "date" portion of the key then I suspect performance will suffer. Going from a 5 column key to a single column key for RI should be more than enough reward.. DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-16 : 16:04:12
|
quote:
Originally posted by Marioi
quote:
Originally posted by Kristen
Bad things will happen when there is a collision in the replication!
What are the bad things?
Well .. like ... the rplication will fail!Kristen |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-16 : 16:56:50
|
quote:
Well .. like ... the rplication will fail!
Do you think that the COMB strategy is significantly better in this respect: 6 bytes for date/time and 10 bytes for a random number? |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-16 : 17:02:16
|
quote:
Originally posted by byrmol
"Smart Keys" (keys that have multiple meanings) are not really a good idea.
So you would go with 8 more bytes plus index key in every table?I guess I am spoiled by the benefits of the date and time in the ID. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-16 : 17:13:04
|
| I don't really understand what you are doing. Why would the "CreatedDate" column be part of the key (and consequently used in RI)?DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-17 : 11:52:09
|
quote:
Originally posted by byrmol
I don't really understand what you are doing. Why would the "CreatedDate" column be part of the key (and consequently used in RI)?
I would have one 16-byte uniqueidentifier field whose first 8 bytes would hold date and time. I would have an index on this uniqueidentifier field. The field is needed for merge replication. With this strategy I could sort in the date/time order when I sort on the uniqueidentifier field. I assume that I could also filter on the date and time portion of this field with some syntax like 'starts with' (don't know whether that's doable with binary fields). |
|
|
|
adweigert
Starting Member
22 Posts |
Posted - 2005-11-17 : 12:17:51
|
| I can tell you from experience this is not a good idea. Everytime you go to query that date value you are going to have to parse it out.I did something similar where I was storing a unique date with a type value. Everytime I wanted to query or even filter or sort on the year or the type value I had to extract the values out.I have regretted doing this ever since I did it in the heat of the moment thinking it would be a good idea since the data wasn't large and I wouldn't use it much. Come to find out, it has become a highly used field because it contained a data element users wanted to filter / query on.So, my two cents, just don't do it. |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-17 : 12:28:47
|
quote:
Originally posted by adweigertSo, my two cents, just don't do it.
You convinced me. Since our app has a metadata-driven schema, Creation Date Time wouldn't be available in the field selector, only this 'unique ID', therefore they couldn't get the date and time without programmatic extraction. What was I thinking... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-17 : 13:12:34
|
| Might still be worth building it into the GUID so that the indexing of the PKs is less fragmented. But I agree, don't plan to "extract" it back out of the GUID!Kristen |
|
|
|
Marioi
Posting Yak Master
132 Posts |
Posted - 2005-11-17 : 13:49:34
|
quote:
Originally posted by Kristen
Might still be worth building it into the GUID so that the indexing of the PKs is less fragmented.
I will try the COMB approach. |
|
|
|
|
GUIDs that start with date/time?