In SQL Server, both INFORMATION_SCHEMA and sys are schema-based views that provide metadata about database objects. However, there are some differences between the two:
- Content: The
INFORMATION_SCHEMAviews provide a more standardized way of accessing metadata, while thesysviews provide more detailed information about the database objects. - Compatibility: The
INFORMATION_SCHEMAviews are part of the SQL standard, so they are more compatible with other database management systems. On the other hand, thesysviews are specific to SQL Server. - Customization: The
INFORMATION_SCHEMAviews are read-only, so they cannot be modified. Thesysviews can be customized using user-defined views, stored procedures, and functions. - Performance: The
sysviews are generally faster than theINFORMATION_SCHEMAviews, as they use internal system tables that are optimized for performance.
In summary, the INFORMATION_SCHEMA views provide a more standardized way of accessing metadata, while the sys views provide more detailed and customizable information specific to SQL Server. Both can be useful in different scenarios, depending on the requirements of the task at hand.
Latest posts by Jami Raj (see all)
- How to become a devops freelancer - July 15, 2023
- DevOps Support Services Market in India - July 15, 2023
- 5 Key Considerations Before Embarking on An App Development Project - July 14, 2023
Mr. Raj:-
You have an interesting take on saying that queries against the sys schema is likely a bit faster than corresponding queries against the INFORMATION_SCHEMA.
Can you please direct us to other resources that speak to specific queries that were tested and how the correspondent benchmark tests?
Nice well-written and concise post.