Use of non-clustered index on guid type column in SQL Server
foreign-keys, indexing, optimization, sql-server, t-sql
Solution
An index on a `<anytype>` is by far the best option you have to improve joins and singleton lookups. Lacking this index the query will always have to scan the entire table end-to-end with (often) abysmal performance results and concurrency gone down the drain.
It is true that `uniqueidentifier` makes poor choice for indexes for the reasons you mention, but by no means does that implies that you should not create these indexes. Changing the data type to INT or BIGINT would be advisable, if possible. Using `NEWSEQUENTIALID()` or `UuidCreateSequential` to generate them would help with fragmentation issues. If all alternatives fail you may have to do index maintenance (Rebuild, reorganize) operations more often than for other indexes. But by no means do any of these drawbacks outweigh the benefit of having the index in the first place!
Problem
I would like optimize the performance of a database that my team is using for an application. I have been looking for areas to add foreign keys, and in turn index those columns to improve the performance of joins. However, many of our tables are joined on an id that is a `GUID` type, generated upon insertion of an item, and the data associated with that item in other tables is generally has column `item_id` containing the GUID. I have read that adding clustered indexes to GUID type columns is a very bad decision because the index will need to be constantly reconstructed in order to be effective. However, I was wondering, is there any detriment to utilizing a non-clustered index in the scenario described above? Or is it reasonable to assume that it would help performance? I can provide more information if needed.