Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-05-15 : 15:03:18
|
| Hello allI have a problem that I don't really know how to solve. So what better idea than to put it to you guys for your opinions.Issue:We are creating an order system for an online store. A user is able to make an order (whether registered or not) online. We need a method to identify that order/user.A previous implementation used a GUID, and this was stored in the database as a clustered index and on the client machine in a cookie. This ensured uniqueness, and also acted as a security measure (if using an auto-increment field, a rival company could obtain information about the number of orders that were being placed by intermittently creating orders throughout the day, and obtain the incremented orderids). However, they experienced performance problems because the GUID was the clustered index (degrading write performance as new orders would be added, but not in a sequential manner).A colleague has performed some investigation into the matter, and has discovered that using a checksum value of the guid, and indexing that rather than the GUID, can aid retrieval performance and reduce the issues surrounding write performance. He also created a non clustered index on teh guid column. I have no idea of how to tackle this issue, as it is essential to retain the randomness of a GUID, and its uniqueness. The colleagues suggestion is certainly a viable option, but I was wondering if there was any other approach you could recommend. Is there any additional functionality in SQL Server 2005 that we could utilise in order to resolve this issue?Thanks in advance.Hearty head pats |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-15 : 16:08:31
|
| You could add an IDENTITY column to your table, and create the clustered index on that. Change the current primary key clustered index on the table to a unique not null, non-clustered constraint on the GUID column.This would cause the table to grow only on the last page, instead of randomly throughout the table. The ID would not have to be exposed to the external world, just the GUID. If you have line items related to the order, it would probably be faster to relate them to the ID column, instead of the GUId.I don't see the value of using a checksum, except that it is smaller, since it would also be in random order and have the disadvantage of having duplicates to deal with.CODO ERGO SUM |
 |
|
|
|
|
|
|
|
unique identifier for order placement issue