Should I add a clustered Index on a foreign key that is not unique?

Question

I have access to a table that has a primary key unique clustered index on an nvarchar(36).

I need to create another table that hangs our own data off that unique clustered index in another table and use the field from the table above as the foreign key reference (these 2 fields will be the fields that JOIN the 2 tables). In this table we keep a record of the data as it changes, so we get repeated rows for the FK reference where the previous row has been end dated.

The question I have is would it be best to put the clustered key on the foreign key (that’s the field we’ll always join on) or is that bad practice because it’s not unique and also the order that rows would get added may not be sequential?

If uniqueness is an issue would you consider it a good approach to use the foreign key and the default start date column to make it unique?

Would no clustered index but a non clustered index (filtered for NULL end dates) be the best approach, problem is – there are a lot of columns in the FK table and I’d need to include them all in the NC index that might take it over the limit.

Further info as requested:

We're moving from one system supplier to another, but locally we hang extra data off the suppliers systems, The tables are as follows:

Main table that holds the ExternalID as the PK clustered index, when we go live this will hold 1.4 million rows

CREATE TABLE [dbo].[Inpatient_Activity](
    [RowID] [nvarchar](50) NOT NULL,
    [ExternalID] [nvarchar](36) NOT NULL,
    [Spell_ExternalID] [nvarchar](36) NULL,
    [Activity_Type] [nvarchar](128) NOT NULL,
    [Accepted] [bit] NULL,
    [Ward] [nvarchar](128) NOT NULL,
    [Bed] [nvarchar](50) NULL,
    [Activity_Start] [datetime] NOT NULL,
    [Activity_End] [datetime] NULL,
    [LOA_Outcome] [nvarchar](128) NOT NULL,
    [Reason_Leave_Was_Ended] [nvarchar](128) NOT NULL,
    [Expected_End] [datetime] NULL,
    [Home_Leave_Reason] [nvarchar](128) NOT NULL,
    [Discharge_Delay_Reason] [nvarchar](128) NOT NULL,
    [Updated] [datetime] NOT NULL,
    [DATVER] [int] NOT NULL,
 CONSTRAINT [PK_ipActivity] PRIMARY KEY CLUSTERED 
(
    [ExternalID] ASC
)
) ON [PRIMARY]

As we're moving systems I need to make the table below point at the new one, the old one had an id int identifier but that now seems a bit pointless hence I'm asking the question above.

AT the moment the table below has 174793 rows, but as we move forward every row in the top table will get at least one row in this table of not 2, 3 or 20 rows.

[SERVICE_POINT_STAYS_PATIENT_STATUS](
    [ID that will link to the table above] nvarchar(36),
    [ESTIMATED_DISCHARGE_DATE] [datetime] NULL,
    [REASON_ID] [int] NULL,
    [START_DTTM] [datetime] NOT NULL,
    [END_DTTM] [datetime] NULL,
    [USER_NAME] [varchar](50) NULL,
    [STATUS_ID] [int] NULL,
    [SPECT_REFNO] [numeric](10, 0) NULL,
    [REFERRED_WARD] [numeric](10, 0) NULL,
    [REFERRED_WARD_TIME] [datetime] NULL,
    [DISCHARGE_DESTINATION] [varchar](500) NULL,
    [EDD_VERIFIED] [char](1) NULL,
    [EDD_START_DTTM] [datetime] NULL,
    [DISCHARGE_TODAY] [varchar](150) NULL,
    [DISCHARGE_TODAY_DTTM] [datetime] NULL,
    [REFER_SPECT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT_DTTM] [datetime] NULL,
    [DECISION_TO_ADMIT] [varchar](10) NULL,
    [REFER_SPECT_REFNO] [numeric](10, 0) NULL,
    [EDD_CHANGED_REASON_ID] [int] NULL,
    [MODIF_DTTM_TRIGGER] [datetime] NULL,
    [GRAVIDA] [varchar](50) NULL,
    [BABY_WEIGHT] [varchar](50) NULL,
    [GESTATION] [varchar](50) NULL,
    [ANTE_POST_NATAL] [varchar](50) NULL,
    [POST_OP_WARD_SPONT_REFNO] [numeric](10, 0) NULL,
    [THEATRE_STATUS_ID] [int] NULL,
    [THEATRE_STATUS_DTTM] [datetime] NULL

Answer 1

It would be a great design approach to put the clustered index on your FK if you will be fetching all the lines (I mean all the data of the line) for a given FK, or making joins based on that key : as they're stored physically in this order, they will come up much more quickly. Otherwise you could use a non-clustered index (slower fetching of the actual rows' data).

You may encounter a slower insert performance though.

Values can be multiple in a CI, they are also very efficient to quickly fetch a single, uniquely identified, row's data (Is it the reason why MS puts the clustered index on the PK by default ?).

I would recommend to make some tests using both approaches with a subset of your data, and check the estimated execution plans using CTRL+L, then include the EP via CTRL+M before you execute the test statement. This way you will see wich indexes are being used and how the engine modifies its strategy depending on what you are querying.

I generally avoid includig dates for unicity beacause on multiple insert statements the date for all inserted records is the same... I prefer using the default value of the uniqueidentifier type : myCol uniqueidentifier NOT NULL DEFAULT(NEWID()) which ensures uniqueness among all.

If you need all the columns' data of several rows with the same foreign key I would use the clustered index on non unique values. You may however check the execution plans.