mw112009 (6/15/2010)
3. The query says that the fragmentation rate is 75 on index 1 ( I mean even after doing a rebuild why do a I get a fragmentation rate of 75 only on one particular index ? )
Because the index is too small for rebuild to have much if any effect.
4. I also mentioned that the table had less than 100 rows.
And i mentioned that it is not worth worrying about fragmentation for indexes under about 1000 pages
5. So then why is SQl server returning wrong information ?
It's not, it's perfectly correct, the fragmentation of that index is 75%
6. How else can we rebuild the indexes so that we get a fragmentation rate of 0 for all indexes. Is there another command out there that I don't know ( besides the Alter Index... Rebuild command )
There's no guarantee that any index rebuild of any form will leave you with 0% fragmentation. Don't worry about small amounts of fragmentation and don't worry about fragmentation on very small indexes. I'm willing to bet this index is 4 pages in size, well under the size where you should start worrying.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability