Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: unique index on X columns but not Primary key

    my table :

    CREATE TABLE [dbo].[users] (
    [ID] [int] NOT NULL ,
    [A1] [nvarchar] (100) NULL ,
    [A2] [nvarchar] (100) NULL ,
    [A3] [nvarchar] (100) NULL
    ) ON [PRIMARY]


    i must keep ID columns as primary key

    ALTER TABLE [dbo].[users] WITH NOCHECK ADD
    CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
    (
    [ID]
    ) ON [PRIMARY]



    but now A1+A2 must be unique

    how can i do it ?

    thank you

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Code:
    CREATE UNIQUE NONCLUSTERED INDEX A1A2_Index_2 
    on dbo.Users(A1,A2)
    with PAD_INDEX,FILLFACTOR=80
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    thank you

    i've found :
    CREATE UNIQUE INDEX [IX_Users ON [dbo].[Users]([A1],[A2]) ON [PRIMARY]


    shuld i add NONCLUSTERED and with PAD_INDEX,FILLFACTOR=80 ?

    thank you

  4. #4
    Join Date
    Jun 2005
    Posts
    115
    thank you

    i've found :
    CREATE UNIQUE INDEX [IX_Users ON [dbo].[Users]([A1],[A2]) ON [PRIMARY]


    shuld i add NONCLUSTERED and with PAD_INDEX,FILLFACTOR=80 ?

    thank you

  5. #5
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by quentin
    thank you

    i've found :
    CREATE UNIQUE INDEX [IX_Users ON [dbo].[Users]([A1],[A2]) ON [PRIMARY]


    shuld i add NONCLUSTERED and with PAD_INDEX,FILLFACTOR=80 ?

    thank you
    its better add fillfactor to reduce the page split,because A1 and A2 will not insert in a order.so my advice is add fillfactor
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Jun 2005
    Posts
    115
    add fillfactor

    thank you

Posting Permissions

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