Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing?

Here is how I proved it is the same data:

select
    fil.name as [FileName],
    fg.name as GroupName
from sys.database_files fil
inner join sys.filegroups fg
on fil.data_space_id = fg.data_space_id

select 
    fil.name as [FileName],
    fg.groupname as GroupName
from sys.sysfiles fil
inner join sys.sysfilegroups fg
on fil.groupid = fg.groupid
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Almost always, backward compatibility. Often for end user compatibility but also for internal purposes.

I'd swear I was reading an article earlier this week that mentioned one of the old views, possibly sysfiles, having been 99% removed during SQL2005 development but the final 1% proved to difficult/risky to eliminate.

As a general rule, target the most recent versions of any system tables/views in your scripts, they're more likely to still be there in subsequent releases.

share|improve this answer
add comment

Other than the naming scheme (and consulting the docs - e.g. sys.sysfilegroups has a big yellow warning) there isn't much you can do to tell. You would hope for some flag in sys.all_objects or sys.all_views but nothing differentiates them.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.