1

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

  • We will need to return just the row from the foreign key table that isn't end dated. If I added an identity column and created the clustered index on the foreign key and then the identitiy column (in that order) would I be improving things (would the join still use the CI?) pr am I just creating unnecessary overhead? – chris Aug 26 '15 at 8:50
  • If you just need one row of data, with a NULL end date and the FK, create an index including these 2 columns, it should be used for your query (check that out with the estimate execution plan !). The cluster determines the physical arrangement of the rows, so it could help, although slower insertion speed. – user2955677 - Greg Aug 26 '15 at 13:19

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.