Paul Randal talks about the fate of non-clustered indexes when the table structure changes (in both SQL Server 2000 and 2005). There are some interesting changes in behavior between the two versions. Basically, SQL Server 2005 does not automatically rebuild the non-clustered indexes quite as often as SQL Server 2000 does.
Speaking of indexes, here are a couple of good DMV queries:
-- Lists fragmentation information for a specific table by index SELECT a.database_id, a.object_id, a.index_id, b.name, a.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('YourTableName'), NULL, NULL, NULL) AS a INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.database_id = db_id() ORDER BY a.object_id
-- Get most frequently updated indexes SELECT object_name(p.object_id) AS objname, * FROM sys.dm_db_index_usage_stats AS p ORDER BY user_updates DESC