Index and Variable Type Issue

• mr_adeelalisyed

  SSC-Addicted

  Points: 470

  More actions

  November 23, 2009 at 10:16 am

  #218030

  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.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 23, 2009 at 10:28 am

  #1083047

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

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question