If PK is Non-Clustered index, how to identify clustered index candidate?

Question

I'm designing a database for insurance company. I want to make Primary key as GUID-uniqueidentifier. I want to create Index for each table in database for performance optimization.

I did research and i came to know that GUID primary key should be Non Clustered always. I agreed that clustered index on GUID will create fragmentation in memory.

I have sample tables,

CREATE TABLE [dbo].[ProductState](

    [ProductStateId] [uniqueidentifier] NOT NULL,
    [ProductId] [uniqueidentifier] NOT NULL,  -- Foreign key
    [StateId] [uniqueidentifier] NOT NULL, -- Foreign key
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductState] PRIMARY KEY NONCLUSTERED 
(
    [ProductStateId] ASC
)

This is a mapping table between two master table in which only foreign key columns are available and which are again GUIDs.

Another sample table,

CREATE TABLE [dbo].ProductCompare(
    [ProductCompareId] [uniqueidentifier] NOT NULL,
    [ProductRateId] [uniqueidentifier] NOT NULL,          -- Foreign key
    [ProductId] [uniqueidentifier] NOT NULL,              -- Foreign key
    [Program] [int] NOT NULL,
    [MarkUpValue] [decimal](19, 10) NULL,
    [IsSelected] [bit] NOT NULL,
    [ComparisonValue] [varchar](max) NULL,
    [ComparisonOrder] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductCompare] PRIMARY KEY NONCLUSTERED 
(
    [ProductCompareId] ASC
)

This is child table which has reference of master table along with another column, but i don't see any column here which can work as Clustered Index key.

In my entire database there are many scenarios of this kind where i don't have any column which can work as Clustered Index key.

I need suggestion how should i manage Indexing for database of insurance company web application.

Answer 1

that GUID primary key should be Non Clustered always

This is not necessarily true. Indexing over effectively random data, which most GUID/UUID values are, can lead to excess page splitting and fragmentation which can be a concern for rapidly growing data. Depending on how your data grows, simple index maintenance run regularly (but not too often) my alleviate all of that practically, or it may not be a significant problem for your data at all anyway.

If you are generating the GUIDs in the database using a default constraint, and you aren't using UUIDs instead of smaller values (such as integers) for your keys for privacy reasons, you could instead use NEWSEQUENTIALID() instead of NEWID() in your defaults to reduce the amount of randomness such that it is insignificant.

If you are generating your GUIDs in your application, then to get the same effect you could use UuidCreateSequential or equivalent (many UUID related libraries wrap this function, it is the function that NEWSEQUENTIALID() itself is based upon. It is possible, though requires an inefficient little bit or hackery, to use NEWSEQUENTIALID() in a stored procedure or trigger too (though you can't use it in functions).