Performance issue with composite primary key or not on Transactional Replication with AlwaysOn Subscribers? [closed]

Question

I configure identity column with composite primary key on Transactional Replication scenario to maintain unique row value.

    CREATE TABLE [dbo].[AD_CLIENT_DETAIL_MST](
    [CLIENT_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SERVER_ID] [char](1) NOT NULL CONSTRAINT   [DF_AD_CLIENT_DETAIL_MST_SERVER_ID]  DEFAULT ('A'),
    [C_NAME] [varchar](300) NOT NULL,
    [CLIENT_TITLE] [varchar](10) NULL,
    [C_FIRST_NAME] [varchar](100) NOT NULL,
    [C_MIDDLE_NAME] [varchar](100) NULL,
    [C_LAST_NAME] [varchar](100) NULL,
    [C_DOB] [date] NULL,
    [RESELLER_ID] [bigint] NOT NULL,
    [RESELLER_NAME] [varchar](300) NULL,
    [CITY] [varchar](50) NULL,
    [STATE] [varchar](50) NULL,
    [COUNTRY] [varchar](50) NULL,
    [COMPANY_NAME] [varchar](500) NULL,
    [COMPANY_LOGO_PATH] [nvarchar](max) NULL,
    [COM_ADD1] [varchar](300) NULL,
    [COM_ADD2] [varchar](300) NULL,
    [BANK_NAME] [varchar](50) NULL,
    [BANK_ACCOUNT_NO] [varchar](50) NULL,
    [PAN_NO] [varchar](20) NULL,
    [TIN_NO] [varchar](20) NULL,
    [BANNER] [varchar](50) NULL,
    [YOCC_NO] [varchar](15) NOT NULL,
    [PRIMARY_NO] [varchar](15) NOT NULL,
    [SECONDARY_NO] [varchar](15) NULL,
    [ALLOWED_CHANNEL] [int] NOT NULL,
    [ACTIVE_OPERATOR] [int] NOT NULL,
    [CALL_ROUTING_TYPE] [bigint] NOT NULL,
    [CALL_ROUTING_PATTERN] [bigint] NOT NULL CONSTRAINT [DF_AD_CLIENT_DETAIL_MST_CALL_ROUTING_PATTERN]  DEFAULT ((0)),
    [LIST_STATUS] [bigint] NOT NULL,
    [SMS_PROVIDER_ID] [bigint] NOT NULL,
    [EMAIL_PROVIDER_ID] [bigint] NULL,
    [IS_BLOCK] [bit] NOT NULL,
    [REG_DATE] [datetime] NOT NULL,
    [TOTAL_USER] [int] NULL,
    [TOTAL_ADMIN] [int] NULL,
    [EXP_DATE] [datetime] NULL,
    [CHANNEL_COUNTER] [int] NULL,
 CONSTRAINT [PK_AD_CLIENT_DETAIL_MST] PRIMARY KEY CLUSTERED 
(
    [CLIENT_ID] ASC,
    [SERVER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

So, when i define two primary key then i will give some slow query performance or not ?

Answer 1

Performance not only depends on the query, but also the kind of data you store in the table.

Let's zoom out and talk about how you choose a clustering key for a table. Generally, you want the clustering keys to be:

• Static - non-changing
• Unique
• Narrow - as few fields as possible, with as small datatypes as practical
• Ever-increasing - which gives you a hotspot at the end of the table, which would sound bad but is usually good in small (sub-terabyte) database designs

Here, you've already got a single field that satisfies all of those requests: CLIENT_ID, which you defined as an identity field. You could just start by using that field alone - adding SERVER_ID doesn't really buy you anything.

However, I'm a little suspicious of the table design because SERVER_ID is only a CHAR(1). That gives you a limited number of servers to work with per CLIENT_ID. I don't know what you're trying to model, but generally an ID wouldn't be a single character.