Professional Web Applications Themes

Unique Index for two columns in a table - Microsoft SQL / MS SQL Server

Hi, I would like to add a unique index that consists of two fields in a table. e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination must be Unique. Can anyone tell me the actual sql syntax to create this index? Thanks, June....

  1. #1

    Default Unique Index for two columns in a table

    Hi,

    I would like to add a unique index that consists of two fields in a
    table.
    e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination
    must be Unique.
    Can anyone tell me the actual sql syntax to create this index?

    Thanks,
    June.
    June Guest

  2. #2

    Default Re: Unique Index for two columns in a table

    Depending on whether you want a clustered or non-clustered index:

    CREATE UNIQUE CLUSTERED INDEX index_name ON tbl_A (col1,col2)

    or

    CREATE UNIQUE NONCLUSTERED INDEX index_name ON tbl_A (col1,col2)


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: Unique Index for two columns in a table

    You might consider specifying a unique constraint instead of a unique
    index since uniqueness is a characteristic of you data. SQL Server will
    create a unique index to support a unique constraint so these are
    functionally identical in terms of performance.

    Unique constraint syntax:

    ALTER TABLE tbl_A
    ADD CONSTRAINT UN_tbl_A_1
    UNIQUE (field1,field2)

    Unique index syntax:

    CREATE UNIQUE INDEX Index_tbl_A_1
    ON tbl_A (field1,field2)

    Also, either of the above indexes can be created as clustered or
    non-clustered. See the Books Online for details.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "June Moore" <com.au> wrote in message
    news:google.com... 


    Dan Guest

  4. #4

    Default Re: Unique Index for two columns in a table

    create unique index _ix on tb_name(col1,col2)


    --
    -oj
    Rac v2.2 & QALite!
    http://www.rac4sql.net


    "June Moore" <com.au> wrote in message
    news:google.com... 


    oj Guest

Similar Threads

  1. Question Remove Duplicate Rows in MySQL Table Having No Primary Key or Unique Index
    By deltaforce in forum Brainstorming Area
    Replies: 0
    Last Post: July 25th, 10:14 AM
  2. Unique Index
    By Alex in forum PostgreSQL / PGSQL
    Replies: 30
    Last Post: January 20th, 09:00 PM
  3. Generating unique values for TEXT columns
    By Frank D. Engel, Jr. in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 3rd, 06:39 PM
  4. Calculating a unique hash on 4 columns in T-SQL?
    By Steven in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 8th, 01:04 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139