Unique Index for two columns in a table

[June Moore]

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.

[David Portas]

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

[Dan Guzman]

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....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"June Moore" <ju******@yahoo.com.au> wrote in message
news:e5**************************@posting.google.c om...

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.

[oj]

create unique index _ix on tb_name(col1,col2)
--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net
"June Moore" <ju******@yahoo.com.au> wrote in message
news:e5**************************@posting.google.c om...

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.