Skip to main content

Return to Question

Trim fluff, reword question for improved clarity
Source Link
zcoop98
zcoop98
  • 3.3k
  • 2
  • 26
  • 38

I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1one index and definingacross multiple columns over having 1 index perversus creating multiple indexes, one per column you want to index.?

Are there certain reasons why one should be used over the other?

For example:

Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc)

Versus:

Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc) Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc) Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc)

I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index.

Are there certain reasons why one should be used over the other?

For example

Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc)

Versus

Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc) Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc) Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc)

What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?

Are there reasons why one should be used over the other?

For example:

Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc)

Versus:

Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc) Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc) Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc)
Question Protected by Pரதீப்
Post Undeleted by Shog9
Post Reopened by Shog9
Post Unlocked by Shog9
Post Locked by CommunityBot
Post Closed as "off topic" by casperOne
Source Link
GateKiller
GateKiller
  • 76.3k
  • 75
  • 176
  • 204

Multiple Indexes vs Multi-Column Indexes

I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index.

Are there certain reasons why one should be used over the other?

For example

Create NonClustered Index IX_IndexName On TableName (Column1 Asc, Column2 Asc, Column3 Asc)

Versus

Create NonClustered Index IX_IndexName1 On TableName (Column1 Asc) Create NonClustered Index IX_IndexName2 On TableName (Column2 Asc) Create NonClustered Index IX_IndexName3 On TableName (Column3 Asc)