Fragmentation on the database - close friend of a DBA
On day-to-day basis a DBA might come across with the issues on the fragmentation on the database, it may not be possible to design the database that will be totally efficient and optimum to perform without any issues. Due to the fact of data inserts, modifications and deletions ((DML tasks) the fragmentation will be obvious and by default SQL Server has internal mechanism to tackle the fragmentation with 'self-optimized' process. This is a vast subject to discuss and SQL Server product team has contributed lot of information within the BOL and their blogs (thanks to them).
In particular I do follow Paul Randall's blogs that clears the doubts of fragmentation issues on the database. He also covers the high availability series within the SQL Server which is my favourite subject to deal. Since last year Paul has been contributing wealthy of information about fragmentation through his blogs and for the advantage of newbie in SQL Server and Jr. DBAs follow the below references to clear your doubts.
Fragmentation (part 1): What are records?
Fragmentation (part 2): What are pages?
Fragmentation (part 3): What are extents?
When can allocation order scans be used?
How can you tell if an index is being used?
Above all don't forget to visit SQL Server 2000 Index Defragmentation Best Practices link to keep up the performance of your database.
Also look at the modified version of defragment indexes stored procedure by Tara Kizer.