Indexes in SQL Server 2005

Question

Indexes in SQL Server 2005

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

jritson Ten Centuries Points: 1007 More actions · Answer 1

I also assumed that the question was mistyped, that 2008 was intended, rather than the answers being wrong. Maybe the question should be updated to read 2008 rather than 2005?

bkubicek SSChampion Points: 10948 More actions · Answer 2

Sql 2005 only has a max of 250 indexs for a table. The link points to sql server 2008. Where the max indexes for a table is 1000.

f_ernestog Ten Centuries Points: 1217 More actions · Answer 3

Good question, but the answer is for SQL Server 2008, not 2005.

Also, the link that is offered as the documentation doesn't says the maximum number of indexes allowed, just redirects you to the "Create Index" T-SQL documentation, and there it doesn't says that amount.

Frank.

jonny-805344 SSC Veteran Points: 226 More actions · Answer 4

Please fix the question or the answer options. In 2008 are 1000 indexes or in 2005 are 250.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 5

f_ernestog (1/19/2010)
the link that is offered as the documentation doesn't says the maximum number of indexes allowed, just redirects you to the "Create Index" T-SQL documentation, and there it doesn't says that amount.

Check carefully, and you will find the following:

CLUSTERED
A table or view is allowed one clustered index at a time.
NONCLUSTERED
Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.

But there are also XML, full-text, and spatial indexes (as Hugo mentioned earlier). The question doesn't say that we must consider only clustered and nonclustered indexes.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 6

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 SSCarpal Tunnel Points: 4405 More actions · Answer 7

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 SSC Guru Points: 64790 More actions · Answer 8

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. 🙂

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

SanjayAttray SSChampion Points: 13157 More actions · Answer 9

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 SSChampion Points: 13157 More actions · Answer 10

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 SSC Guru Points: 281334 More actions · Answer 11

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[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 13

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 Old Hand Points: 304 More actions · Answer 14

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 SSC Guru Points: 281334 More actions · Answer 15

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[/url]
Learn Extended Events