Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes in SQL Server 2005


Indexes in SQL Server 2005

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 2592
Just out of curiosity, has anyone ever run up against the 250 index limit in SQL 2005. I can't imagine ever needing that many in our environment.

Perhaps if you have a very wide read-only table I can see it, but even that's a stretch.

The Redneck DBA
Tony Davis
Tony Davis
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: Administrators
Points: 591 Visits: 1148
Apologies to all -- I've now made a correction to the answers so that it applies correctly to SQL Server 2005.

Best,

Tony.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11585
Tony Davis (1/19/2010)
Apologies to all -- I've now made a correction to the answers so that it applies correctly to SQL Server 2005.

Best,

Tony.


No, Tony. You haven't. Smile

SQL Server 2005 allows more than 250 indexes on a single table. Books Online says that you can have a maximum of 1 clustered, 249 nonclustered, and 249 XML indexes per table; I was not able to find the maximum number of full-text indexes per table. (I'm still hoping someone else finds this and posts a link).

So the actual maximum is (499 + (maximum FT indexes per table)).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
jagadeesanpv (1/18/2010)
I think raj is correct, for 2005 = 250 and 2008 = 1000

Please check this URL for 2005
http://msdn.microsoft.com/en-us/library/ms188783(SQL.90).aspx



Here is an another link for comparing all versions.

http://www.sql-server-helper.com/sql-server-2005/maximum-capacity-specifications.aspx

SQL DBA.
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Jason Shadonix (1/19/2010)
Just out of curiosity, has anyone ever run up against the 250 index limit in SQL 2005. I can't imagine ever needing that many in our environment.

Perhaps if you have a very wide read-only table I can see it, but even that's a stretch.


Even I want to ask the same question. Had anybody seen these many indexes on a table ? Other question would be if we have these many indexes on a single table then the performance of read / write transaction would be slow or good ?
How about managing space for all these indexes would take if its on a large table ?

Any one please ?

SQL DBA.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
The question has been updated and does read better now. Unfortunately, Hugo's point is valid - thus making the QOD answers less than accurate.

Maybe a rewording of the question so that it explicitly eliminates FT indexes would be good in making this question better.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24261 Visits: 37981
How many XML indexes can you have on a table with no XML columns?

To be honest, when asked about number of indexes, unless specifically asked about FTI and XML indexes, I don't even think about them.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
Hugo Kornelis (1/19/2010)
I was not able to find the maximum number of full-text indexes per table. (I'm still hoping someone else finds this and posts a link).

Here it is: http://msdn.microsoft.com/en-us/library/ms187317.aspx
CREATE FULLTEXT INDEX (Transact-SQL)
Only one full-text index is allowed per table or indexed view, and each full-text index applies to a single table or indexed view.


Hugo Kornelis (1/19/2010)
So the actual maximum is (499 + (maximum FT indexes per table)).

And it's 500 :-)
suresh.theyyath
suresh.theyyath
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 135
Dear all,

First of all the question was intended to subject Sql 2005 and as there is a mistake the required answer was not supplied to the valueble members, I personally admit my mistake in posting it. But I really surprised to see the same had repeated by our respected administers here too. I request them to keep the reputation high since new comers like me may go wrong while learning things in a right manner. Further all posts from valued members are good enough to keep me in right way to understand my mistake as well as it boosted my spirit too. My hearty thanks to all. I hope the following link can give you further details of the question posted by me.


http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/

Once again thank you all!!

Regards
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
suresh.theyyath (1/20/2010)
Dear all,

First of all the question was intended to subject Sql 2005 and as there is a mistake the required answer was not supplied to the valueble members, I personally admit my mistake in posting it. But I really surprised to see the same had repeated by our respected administers here too. I request them to keep the reputation high since new comers like me may go wrong while learning things in a right manner. Further all posts from valued members are good enough to keep me in right way to understand my mistake as well as it boosted my spirit too. My hearty thanks to all. I hope the following link can give you further details of the question posted by me.


http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/

Once again thank you all!!

Regards

Thank you for taking the time to put together a question for the community:-)



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search