We help IT Professionals succeed at work.

sys.indexes vs sysindexes

anushahanna
anushahanna used Ask the Experts™
on
what specific disadvantages of sysindexes has sys.indexes overcome in the newer versions?

there is a lot less records in sys.indexes.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Use sys.indexes for SQL Server 2005.
sysindexes is provided for backwards compatibility

refer this article for getting the entire list of new system views that correspond to old system tables
http://msdn.microsoft.com/en-us/library/ms187997.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107385

Author

Commented:
om_prakash_p, why is that sys.indexes has very few records compared to its predecessor?

is there any msdn help on the new architecture of sysindexes ?
AneeshDatabase Consultant
Top Expert 2009
Commented:

For the changes they made on the sql server 2005/2008, like the Partitions/ filtered indexes , you need to show more information like the partitionwise rownumber, for that they cant simply use the sysindexes table, that's why they provided few other dmvs which gives the detailed info
sys.indexes
sys.partitions
sys.allocation_units
sys.dm_db_partition_stats

http://msdn.microsoft.com/en-us/library/ms187997.aspx

Author

Commented:
OK. Thanks for that nice explanation, Aneesh.