0

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.

  • What do you mean by "create fragmentation in memory" ? Fragmentation is usally more of a disk thing. With Guid, you will probably get more page split as the new data will not be added at the end of the index but if this is the key that allowed to identify each row, then I guess it is ok to use it as primary key. (specially if you cannot change the table desing and replace those GUID by Identity for example) – Dominique Boucher Apr 3 at 12:31
  • @DominiqueBoucher - if there is excess free-space fragmentation in an index, you will end up needing more memory to keep the same amount of data in memory, so it can be more than just a disk/IO issue. Pages are held in the buffer pool in memory in the same format as they are on disk, so if an index is wasting 33% of its allocated space due to many part empty pages then it could potentially use 33% more RAM too. – David Spillett Apr 3 at 12:39
  • When you automatically add a synthetic key to every table, you still need to declare unique constraints on the natural key. On of these alternate keys can be the clustered index. Although as @DavidSpillett correctly notes, there's no need to avoid clustered indexes on UNIQUEIDENTIFIER so long as they are generated with NEWSEQUENTIALID(). – David Browne - Microsoft Apr 3 at 16:00
4

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).

| improve this answer | |
  • I'm generating GUIDs in database using NEWID() and GUIDs are NON CLUSTERED Primary key(don't want to change). My only concern is what can be the CLUSTERED Key for the given tables ? is there any other way to handle this ? – Heta Desai Apr 3 at 15:06
  • Looking at those tables I would probably stick with the PKs being the clustering keys. You could add an extra integer auto-increment value and cluster on that (which can be slightly better than keeping the table in a heap format, and makes non-clustered indexes smaller (adding 4 bytes to each row per index instead of 16 for a UUID)) but that feels a bit "smelly" as you would then have two synthetic keys in one table. A clustered index can have significant benefits for range queries upon those values, so perhaps your datecreated columns are suitable. – David Spillett Apr 5 at 15:42
  • If i create auto-increment key as clustered index and uniqueidentifier(PK) as non-clustered key, will it help me to improve the performance ? – Heta Desai Apr 6 at 6:29
  • It depends what performance issues you are having/expecting. It may save space and reduce a little IO compared to having a UUID as your clustering key but it introduces an unnecessary column: you already have all your data and a surrogate key. Unless it causes a security concern I'd instead either cluster on the existing PKs and use sequential UUID or switch to integer keys which will save a lot more space. Or just cluster on the UUID PKs as-is and monitor the indexes to make sure they are tidily maintained. – David Spillett Apr 6 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.