-1

What is the SQL for having a primary key consisting of 4 columns but only include 3 of these columns in a clustered index?

Is it possible to do this in SQL Server Management Studio as well?

MikeAlike234
  • 759
  • 2
  • 12
  • 29

1 Answers1

5

You'll need to define two separate things:

  1. a non-clustered primary key on your four columns

    ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY NONCLUSTERED (Col1, Col2, Col3, Col4)

  2. a separate clustering index on three of your four columns:

    CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(Col1, Col3, Col4)  
-- adapt this to use those three columns you want

And yes - of course you can execute those T-SQL scripts in your SQL Server Management Studio :-)

Update: to do this in the GUI using clicky-mousy-GUI-tools, you need to do these steps:

  1. in Object Explorer, on your table, you need to first define the clustered index by going to Indexes > New Index > Clustered Index - give it a name and add the columns you want

  2. after you've done this, then you can define your primary key in the usual table designer view, and since there already is a clustered index, it will become a nonclustered primary key

I haven't found any other way - if you create the primary key first, it becomes a clustered primary key, and I found no way to change that after it's been created

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks, I meant in the gui – MikeAlike234 Jun 04 '14 at 16:07
  • 4
    @MikeAlike234 Maybe you should learn to not use the GUI – Kermit Jun 04 '14 at 16:14
  • 1
    Of Course, I what to know both ways – MikeAlike234 Jun 04 '14 at 16:23
  • Of course the clustered index essentially contains the rest of the columns too, so having it be "skinnier" is kind of misleading... – Aaron Bertrand Jun 04 '14 at 16:31
  • Thanks good point, but it should be faster when it has one field less to worry about sorting. – MikeAlike234 Jun 04 '14 at 16:49
  • 1
    @MikeAlike234 Do you think this "should be faster" is worth maintaining a completely separate and almost wholly redundant index with the additional column? – Aaron Bertrand Jun 04 '14 at 17:05
  • You must have misunderstood, the table is supposed to have only one index and that index should be as small and fast as possible to update and insert rows into. – MikeAlike234 Jun 04 '14 at 17:25
  • @MikeAlike234 From your question, sounds like you want a primary key with four columns *and* a clustered index with three columns. The answer you accepted also proposes creating a primary key with four columns *and* a clustered index with three columns. That's two indexes (a primary key is an index, whether it's clustered or not). – Aaron Bertrand Jun 04 '14 at 17:30
  • So it's not possible to have only one index if not all keys are included in that index? – MikeAlike234 Jun 04 '14 at 17:37
  • 4
    @MikeAlike234 If you need to enforce uniqueness of a column, that column needs to be a key column in *an* index. (PK and unique constraints are implemented under the covers as indexes.) So I'm not sure how you could get away with having a column be a key but not be in an index. – Aaron Bertrand Jun 04 '14 at 17:42
  • Ok. Good to know. Putting points on the comments, what is it good for, except to show that there is much nerds in here? – MikeAlike234 Jun 04 '14 at 18:44
  • @MikeAlike234 It's mostly just to show that they weren't the only person with that opinion - easier to +1 a comment than say "I agree with what so-and-so said" especially if there is nothing further to add. We don't need a bunch of me toos cluttering the comments. – Aaron Bertrand Jun 05 '14 at 01:01
  • What purpose does it serve to say I agree? Does it help answering the question? Should I use a solution over Another just because it has more votes? Doesen't serve any purpose at all. – MikeAlike234 Jun 05 '14 at 09:46
  • @MikeAlike234: [There's actually a place](http://meta.stackoverflow.com/) for meta-questions like that, if you are really interested. – Andriy M Jun 05 '14 at 12:24
  • I'm not, I'm just amazed what pointless things some people think give meaning to their lives. – MikeAlike234 Jun 05 '14 at 16:17