Skip to main content
4 of 5
added 87 characters in body
Gulrez Khan
  • 373
  • 1
  • 7
  • 20

Performance issue with composite primary key or not?

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 ?

Gulrez Khan
  • 373
  • 1
  • 7
  • 20