469,924 Members | 1,418 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

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.
Jul 20 '05 #1
3 27087
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
--
Jul 20 '05 #2
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.

Jul 20 '05 #3
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.

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Laurence | last post: by
3 posts views Thread by vj_dba | last post: by
4 posts views Thread by p175 | last post: by
7 posts views Thread by rAinDeEr | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.