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