I am having a problem creating a PK constraint on an existing table. It seems as though whenever I create a PK constraint, SQL Server wants to make it a clustered index. From the BOL, I thought I could do this:

ALTER TABLE Exchange
ADD CONSTRAINT PK_ExchangeID PRIMARY KEY (ExchangeID) NONCLUSTERED

But SQL Server won't let me do this. It always wants to make it a CLUSTERED index. Any ideas?

>>>Let me start by saying I take no responsibilty for this post and I will deny posting it if confronted. ;-) (I just had to throw that in there because you have been participating in that other thread about responsibilties of people with TLA after their name.)
>>
>>But you are part of the inner sanctum. I would not pick on you. < g >
>
>Me part of an Inner sanctum (a.k.a. the "IN" crowd)? ROFLMAO! That would be a first.
>
>>
>>>Okay. IMO, the clustered index would probably be a good idea on the Financial table but not on the Company table. Clustered indexes are used to decrease the number of pages returned. Having a clustered index on the PK doesn't help with this objective. Because you are basing your query on other fields, the valid rows could exist throughout the table.
>>
>>I thought the other advantage of clustered indexes is that when SQL Server traverses the B-tree, when it gets to the leaf, it is already at the data. This is maybe where I am getting confused. I thought that since I am using a surrogate key (I can't remember if I mentioned this), an integer generated sequentially, that SQL Server would find the leaf more quickly.
>
>If you were bringing things up by PK that may be true. But you are pulling information based on the bit fields. Since you can't have an index on a bit field, SQL Server must do a table scan. While it may do an index scan on the PK, if clustered, it is essentially doing a table scan so there is no benefit. Unless you are doing queries on ranges of Customers and using the PK for this, I don't think you need a clustered index.
>
>*snip*