Is there a benefit to using a surrogate key for a many-to-many table?

Question

It is understood that a table with a clustered index with non-sequential value(s) will cause fragmentation due to page splitting.

In my case, I'm working with a pair of uniqueidentifiers in a many-to-many table and that is the context for this question.

Consider the following tables:

CREATE TABLE [dbo].[Car](
    [CarID] [uniqueidentifier] NOT NULL
    ...
)

CREATE TABLE [dbo].[Option](
    [OptionID] [uniqueidentifier] NOT NULL
    ...
)

-- This is the table that will be queried
CREATE TABLE [dbo].[Car_Option](
    [CarID] [uniqueidentifier] NOT NULL,
    [OptionID] [uniqueidentifier] NOT NULL,
    [LastEdit] [datetime] NOT NULL
)

I've have read the suggestion to use some sort of surrogate key when using non-sequential identifiers (for example, adding an ID INT field to [Car_Option]) to prevent page splitting, however I don't think this applies here and that is what I'm looking to confirm.

If the main lookup value CarID is not part of Car_Option's clustered index, I'll need an additional non-clustered index to cover that CarID column which, I believe, will become just as fragmented as if it were the clustered index because we'd still be inserting non-sequential values. Is this a correct statement?

Option 1

CREATE TABLE [dbo].[Car_Option](
    [ID] [int] IDENTITY(1,1) NOT NULL,      -- Clustered Index
    [CarID] [uniqueidentifier] NOT NULL,    -- Non-Clustered Index
    [OptionID] [uniqueidentifier] NOT NULL, -- Non-Clustered Index
    [LastEdit] [datetime] NOT NULL
)

Option 2

CREATE TABLE [dbo].[Car_Option](
    [CarID] [uniqueidentifier] NOT NULL,    -- Clustered Index
    [OptionID] [uniqueidentifier] NOT NULL, -- Clustered Index
    [LastEdit] [datetime] NOT NULL
)

I'm inclined to do Option 2 and put a clustered index on (CarID, OptionID).

Question: Is there any advantage to using a surrogate key in Option 1 if we never lookup from it and are only querying from, say, the CarID?

Note: I am aware that index maintenance would be required if using Option 2 non-sequential values

Update 1: It is a requirement to use a Uniqueidentifier for CarID and OptionID. The system is very mature and it would take immense effort to change that. The designs of the Car and Option table are not really anything that can reasonibly be changed at this point, the focus is strictly on how using a surrogate key will or will not help my specific query scenario which will look something like this:

SELECT * 
FROM Car_Option 
INNER JOIN Option ON Option.OptionID = Car_Option.OptionID
WHERE CarID IN (
   '80297476-f104-406e-bf2c-535042f2db4e',
   'a6486941-57af-42f4-ae72-ef939581e209', 
   'etc...'
)

Answer 1

Let me rephrase your questions

1 Should I use ID column for cross reference table

IMHO if you don't plan to reference to this table you don't need ID and compound primary key (CarID, OptionID) is possible.

2 What should I use as clustered index

I think on this stage of the game fragmentation should not be in your focus until you get feedback from users proving it is the problem. You should focus on logical design of your tables - define primary keys and foreign key constraints Next step will be choose that index should be the clustered one.

You are correct that page splits are inevitable anyways (for clustered or non clustered indexes)

I think your choice of clustered primary key of (CarID, OptionID) is a good start. However don't forget about index on OptionID for Car_Option

Answer 2

Main reason to use uniqueidentifiers

i) Since GUID are more or less unique,they are helpful in multiple database scenario where databases will be merge,i.e. primary key wont clash

ii) Also GUID are hard to remember so they are ok from business point of view

Main Disadvantage

i) GUID (16 bytes ) are 4 times heavier than INT (4 bytes ).So index build on GUID will be larger and slower than index build on INT columns.

Page splitting

Say in table [dbo].[Car] and [dbo].[Option] there are 10 columns or more than 10 columns some of which are varchar.Also it may contain millions of records. In this case there will be high page fragmentation's insert and Select both will suffer from performance.

In case of [dbo].[Car_Option] page fragmentation will be less because its contain only 3 columns. Still it will have more data page as compared to INT column as clustered Index.

If the lookup value CarID is not part of Car_Option's clustered index, I'll need an additional non-clustered index to cover that CarID column which, I believe, will become just as fragmented as if it were the clustered index because we'd still be inserting non-sequential values.

Sql optimizer build multiple plan and compare the cost of each and choose the plan which cost less.Sql optimizer often choose good enough plan Size of Clustered index is more than Non clustered Index .In case of GUID as Clustered Index size of clustered will be more than Non Clustered index.

In short,Since CI is Wider than NCI, Sql optimizer may choose Non Clustered index plan even of there is one more step involved.

Even though uniqueidentifiers is requirement. I will add INT surrogate key in all 3 tables.Let INT surrogate key interact with each other. Also perform testing in Test or Staging server with real data and analyze the execution plan of important query and check their performance.

CREATE TABLE [dbo].[Car](
    CID int identity(1,1) primary key
    [CarID] [uniqueidentifier] NOT NULL
    ...
)

CarID is NonClsutered Index here

CREATE TABLE [dbo].[Option](
     OID int identity(1,1) primary key
    [OptionID] [uniqueidentifier] NOT NULL
    ...
)

OptionID is NonClsutered Index here

CREATE TABLE [dbo].[Car_Option](
    CID int,
    OID int,
    [LastEdit] [datetime] NOT NULL
)
Create ClusteredIndex CI_Car_Option on [Car_Option](cid,oid)

create table #temp (cid int)
insert into #temp(cid)
Select cid from Car 
WHERE CarID IN (
   '80297476-f104-406e-bf2c-535042f2db4e',
   'a6486941-57af-42f4-ae72-ef939581e209', 
   'etc...'
)

SELECT Option.* ,
FROM Car_Option CO
INNER JOIN Option ON Option.OID = Car_Option.OID
inner join #temp t on t.cid=CO.cid

Also it is important to add Trusted Forieng Key constrain in [dbo].[Car_Option]

There may be few more line of query, but performance is main goal. Without and Test data I am not sure, but in other real example it has work well.

Its all about testing few option in Test/Staging server then deciding. Main objective is to look for overall performance. Even if you have to wrote few line for query.

Question: Is there any advantage to using a surrogate key here if we never lookup from it?

Already answer above.

Other option is to Add Trusted Foreign Key constraint in CarOption table. Sql optimizer uses Trusted Foreign key in making execution plan. It enhance the performance of Select query.

But BULK Insert/Update performance suffer, If there is no bulk insert then you can ignore the performance. OR you can disable the Trusted FK before bulk insert and enable after bulk insert is over.

Note : It is assume that these table will contain million of record and there will be frequent insert/update and Select