After a lot of research I am almost to the point of adding an index on a table to improve reads. The column that is being requested 00's of times a day is based on a GUID value. eg SELECT ColA from TableA where ColGUID = @GUIDVal There is only a CLUSTERED PK on the table and that is on the ID (Identity) column. If I create an index based on the GUID column will I run into issues or doing something unnecessary if I create it as a UNIQUE index? This is a 3rd party db that we are allowed to make some (approved) changes in, eg index management.
I think everyone has already answered this question, but I'll pile on. GUID's are fine for indexes. In fact, they're pretty good for indexes, being highly selective & all. The problem with GUID's comes up when you make them into a clustered index. That perfect, random, distribution leads to incredible page splits & fragmentation, not to mention having to maintain that big fat key on every other index for the table. But, when you use the sequential GUID, you largely alleviate that problem. They're not my favorite choice for indexes, but there's nothing inherently evil about them.
If you're just doing it for reads for that specific query, then I wouldn't worry about adding it as a UNIQUE index, just to save cost on insert time. Other queries might benefit from it being a unique index, however (for example joins - it might choose a different match type knowing that the index was unique - Grant will know more though). Is the GUID a client generated GUID, or is it NEWID or NEWSEQUENTIALID? If it was NEWSEQUENTIALID I would use a fairly high (high 90s) fill factor. If client generated or NEWID, then I would use a lower fill factor (maybe mid 70s). But that's something I would monitor and tune over time.
I can't imagine there being any problems with the index being unique. A GUID is a unique value after all. Although, reading on the web, I found info that GUIDs can collide, it is just extremely rare - someone mentioned a collision in SQL 2000, but it was shrugged off as a bug that was fixed in SQL 2005 ([link]) I would go for the unique index. Is this system mission critical? If you are a lottery winner and hit a GUID collision, will your app explode on a failed insert? :