CMMI,.NET,JAVA,Networking,Oracle,SQL Server, Testing Questions And Answers

Home| Forums | Join Google Group | Join Yahoo Group | Jobs

Earn Money
Trading Forex Online
  Paramount Airways
  Free Data Recovery
Cargo
Job Portal
  HSBC Investment
Management
Cheap Web Hosting
  Make Trip
  Cheap Air Travel
Leisure Hotel
  Free Air Travel
  Mutual Fund Informations
Cheapest Cellular Plan
Free Sexy Indians
  Call Center Software
  Hot Indian

Programming Help | Homework Help | Counseling
Astrology Advice | Tarot Advice | Parenting
Dating Advice | Love Advice | Divorce Advice
Legal Advice | Debt Advice | Career Advice
UNIQUE, PRIMARY KEY, FOREIGN KEY

Whar is an index? What are the types of indexes? How many clustered indexes can be
created on a table? I create a separate index on each column of a table. what are the
advantages and disadvantages of this approach?
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a
clustered index on a table, all the rows in the table are stored in the order of the clustered index
key. So, there can be only one clustered index per table. Non-clustered indexes have their own
storage separate from the table data storage. Non-clustered indexes are stored as B-tree
structures (so do clustered indexes), with the leaf level nodes having the index key and it's row
locater. The row located could be the RID or the Clustered index key, depending up on the
absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query
optimizer can choose from all the existing indexes to come up with an efficient execution plan. At
the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become
slow, as every time data changes in the table, all the indexes need to be updated. Another
disadvantage is that, indexes need disk space, the more indexes you have, more disk space is
used.