Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Index improve performance in select statement, but what is the difference between this

create clustered index ix_index1 on table1(col1)
create clustered index ix_index2 on table1(col2)

and this

create clustered index ix_index1 on table1(col1, col2)

and this

create nonclustered index ix_index1 on table1(col1)
create nonclustered index ix_index2 on table1(col2)

and this

create nonclustered index ix_index1 on table1(col1, col2)
share

migration rejected from stackoverflow.com Jul 13 at 12:23

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too broad by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Mikael Eriksson, Marian Jul 13 at 12:23

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
The word in doesn't make sense in this question. Can you rephrase it? –  Gordon Linoff Jul 4 at 3:15
1  
I have a video here that may help you understand the basics of how each type of index works. –  Jon Seigel Jul 4 at 13:20
    
As an aside, your first example tries to create two clustered indexes on one table. This is not allowed. –  Michael Green Jul 5 at 12:37
comments disabled on deleted / locked posts

1 Answer 1

Lets answer your question as you asked

create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2)

You are creating a clustered index(CI) but as soon as you create CI on col1 you cannnot do it on col2 as on a table you can have only one CI. A CI is physically a table arranged in particular order. Since a table cannot be arranged according to two order you can only have one CI on a table. Assuming Col1 satisfies all requirements to be candidate for Clustered index uniqueness and no null.

create clustered index ix_index1 on table1(col1, col2)

You are creating clustered index on two col(col1 and col2 ). This can be because single col does not have unique record but combination of col1 and col2 have unique records. A kind of composite clustered index.

create nonclustered index ix_index1 on table1(col1)

create nonclustered index ix_index2 on table1(col2)

You are creating non clustered index on col1 and then on col2. You can have multiple non clustered index.But only one clustered index on a table.

create nonclustered index ix_index1 on table1(col1, col2)

You arecreating Non clustered index on two columns combined.

This was specific reply to your questions. I would like you to read MSDN resource on indexe ssuggested by Ron. you can also refer to below link by Brent Ozar

http://www.brentozar.com/sql/index-all-about-sql-server-indexes/

share
add comment