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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server?

I also want to determine the maximum table size supported and consumed and the type of storage engine SQL Server is using.

Please explain both, 2005 and 2008 professional and express editions.

share|improve this question
up vote 4 down vote accepted
  • SQL Server has B-Tree indexes only. Other types are hardly used day to day.
  • All maximum capacity specs for 2008 are here: there is a link to SQL Server 2005. Note SQL Server does not have "limits" apart of disk space for size of the DB or a table.
  • Express differs in other ways: see editions
  • Like Sybase, Oracle, Postgres, SQL Server is a single engine. As an approximation, InnoDB is MySQL's closest in concept

Note: if you come from MySQL, do not make assumptions about SQL Server. It (like other RDBMS) are more complex and mature then MySQL in many respects

share|improve this answer

There is no such thing as SQL Server professional edition. There is Express, Workgroup, Standard, Enterprise and Data Center (SQL 2008 R2 and up).

Express is the only edition which has a size limit. SQL 2005 had a 4 Gig database size limit, SQL 2008 R2 changed that to 10 Gigs. For all the other editions the sizes are based on the amount of disk space you have.

The indexes (both clustered and non-clustered) are B-Tree indexes. XML indexes are also B-Tree indexes under the covers. Full text and spacial are totally different (I'm not actually sure what they are using).

There are plenty of databases will multi-billion row tables and multi-TB database sizes.

Also keep in mind that SQL Server 2008 and SQL Server 2008 R2 are actually two totally different versions even though the marketing names appear to be the same.

share|improve this answer
I guess Full-text search is not supported in 2005 Express? – RPK Sep 14 '11 at 13:37
Nope full text appears to require a paid for license. There are lots of feature differences between the versions. The link labeled "editions" that gbn posted breaks down what each edition has. – mrdenny Sep 14 '11 at 23:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.