0

Is there a way to disable clustered index ? As I see in MSDN below where it says "If CLUSTERED is not specified, a nonclustered index is created."

Ref: http://msdn.microsoft.com/en-IN/library/ms188783.aspx

Because I read that, by default a clustered index is created in primary key column. Even if its disabled by some means, a non clustered index will be created by default according to MSDN above ? If so which column ?

I am new to SQL please help.

Jasmine
  • 5,186
  • 16
  • 62
  • 114
  • 3
    I think what above help means is if you don't write CLUSTERED keyword in your CREATE INDEX DDL then it will create non clustered index by default. – JackLock Mar 01 '13 at 21:32
  • @JackLock: That is possible, thank you :) – Jasmine Mar 01 '13 at 21:35
  • 1
    No problem. I know, BOL could be a bit confusing sometimes. – JackLock Mar 01 '13 at 21:37
  • @JackLock: :) Yes. Most times I find it confusing. Another confusing thing/mistake which I just found in MSDN is http://stackoverflow.com/questions/15166776/msdn-conflicting-statement-which-protocol-support-which-security-in-wcf-partic#comment21360415_15166776 Certainly we lose interest on what to trust on then. I have found such observations earlier as well which was communicated to MS MSDN authors. I hope it could do better. I am a beginner, definitely many like me will go disappoint. – Jasmine Mar 01 '13 at 21:43

1 Answers1

1

You can disable a clustered index.

ALTER INDEX IX_Index ON dbo.Table
DISABLE ;

http://technet.microsoft.com/en-us/library/ms188388.aspx

Drew Leffelman
  • 516
  • 2
  • 8
  • Thanks. So if I disable it, according to MSDN a non-clustered index will be created ? – Jasmine Mar 01 '13 at 21:32
  • 3
    I think you are misreading that. Like @JackLock said above, if you create an index, without the CLUSTERED statement, then the index is non-clustered. – Drew Leffelman Mar 01 '13 at 21:34
  • 1
    Technically correct but probably not a good idea. `CREATE TABLE T(X INT CONSTRAINT IX_Index PRIMARY KEY);ALTER INDEX IX_Index ON T DISABLE;SELECT * FROM T` produces `The query processor is unable to produce a plan because the index 'IX_Index' on table or view 'T' is disabled.` – Martin Smith Mar 01 '13 at 21:55