Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Index and Variable Type Issue

    Hello experts,

    Im 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]
    -----------------------------------------------------------------------

    Ive 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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could create lookup tables e.g. one for databases, with your current column and add a surrogate key. Repeat for the other attributes that make up your composite key, substituting the surrogate keys and vwola - you have your composite key.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •