Creating primary key with noncluster index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating primary key with noncluster index

Author

Topic

sql_buddy
Starting Member

41 Posts

Posted - 2009-07-06 : 02:41:14

i want to create a pimary key with non cluster index ,so how could we do that

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-06 : 03:00:06

Primary key is automatically treated as Clustered. So u can't override this.

May i know why u want do like that??

But u can try this way

Create a Unique Key and set a index. It is treated as Non-Clustered Index!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sql_buddy
Starting Member

41 Posts

Posted - 2009-07-06 : 03:30:50

i got the script

ALTER TABLE [tablename] ADD CONSTRAINT [constraint_name] PRIMARY KEY NONCLUSTERED
(
[field_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-06 : 03:40:00

quote:
Originally posted by senthil_nagore

Primary key is automatically treated as Clustered. So u can't override this.

Primary key is by default clustered if there's not an existing clustered index on the table, but that default can be overridden by specifying NONCLUSTERED when creating the pk.

--
Gail Shaw
SQL Server MVP

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-06 : 03:52:00

quote:
Originally posted by senthil_nagore

Primary key is automatically treated as Clustered. So u can't override this.

To explicitly specify CLUSTERED or NONCLUSTERED when creating
primary keys and other indexes to ensure it's a conscious decision to do so.

-------------------------
Your time is a valuable resource.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources