DesNorton - Monday, March 27, 2017 3:07 PM

Indianrock - Monday, March 27, 2017 2:58 PM

Most of our tables have a clustered PK on an integer identity column.   These have a fill factor of 100 and virtually all the non-clustered indexes have a factor of 90.    One table has the clustered PK on the identity column plus a varchar(10) column called ClientID.   I've read that if the clustered PK is ONLY on the identity column, fill factor doesn't make any difference on any of the indexes since data is added to the end.

What about with a "combination" clustered PK like that?

I see no point in having a CL PK on a IDENTITY plus anything.  The leading edge of the PK is guaranteed UNIQUE.  In order to use the PK for any look-ups, you will have to provide the IDENTITY value, which will give you a CLI seek, regardless of anything other predicates.

Indianrock - Monday, March 27, 2017 4:56 PM

It's a large table and pretty busy.  It has about 25 indexes but here's the PK and the one odd-ball ( partitioned index )
Default fill factor in prod is 90, in QA appears to be zero across the board ( out of the box value I presume )


ALTER TABLE [dbo].[ACCOUNT_OWNERSHIP_DOC_SUMMARY] ADD CONSTRAINT [ACCOUNT_OWNERSHIP_DOC_SUMMARY_PK] PRIMARY KEY CLUSTERED
(
    [CLIENT_ID] ASC,
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [CM_Data]
GO

ALTER TABLE [dbo].[ACCOUNT_OWNERSHIP_DOC_SUMMARY] ADD CONSTRAINT [ACCOUNT_OWNERSHIP_DOC_SUMMARY_UC1] UNIQUE NONCLUSTERED
(
    [CLIENT_ID] ASC,
    [ACCOUNT_ID] ASC,
    [OWNERSHIP_DOC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO