Index and Variable Type Issue

  • Hello experts,

    I’m trying to make a composite key but using the combination of 4 different keys. However when I tries to do so SQL Server through error that

    ‘Total size of an index or primary key cannot exceed 900 bytes.’

    I realise mine is well over that limit. Here is the structure of my table.

    -----------------------------------------------------------------------

    CREATE TABLE [dbo].[TotalColumn](

    [ColName] [varchar](500) NOT NULL,

    [TbName] [varchar](500) NOT NULL,

    [DbName] [varchar](100) NOT NULL,

    [CapturedDate] [datetime] NOT NULL,

    [ColId] [int] NOT NULL CONSTRAINT [DF_TotalColumn_ColId] DEFAULT ((10)),

    [ColType] [varchar](20) NULL,

    [ColCreationDate] [datetime] NULL,

    [ColLastModifiedDate] [datetime] NULL,

    [ColLength] [varchar](20) NULL,

    CONSTRAINT [PK_TotalColumn_1] PRIMARY KEY CLUSTERED

    (

    [ColName] ASC,

    [CapturedDate] ASC,

    [TbName] ASC,

    [DbName] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    -----------------------------------------------------------------------

    I’ve table and database names which need varchar(500) and varchar(100) respectively. Can anybody suggest what other data type I can use indeed of these massive numbers in varchar?

    Thanks a lot in advance.

  • Not much you can do here if that is the combination that ensures uniqueness.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply