{"id":33341,"date":"2023-04-11T04:58:52","date_gmt":"2023-04-11T04:58:52","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=33341"},"modified":"2023-04-11T04:58:54","modified_gmt":"2023-04-11T04:58:54","slug":"difference-between-information_schema-and-sys-schema-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/difference-between-information_schema-and-sys-schema-in-sql-server\/","title":{"rendered":"Difference between INFORMATION_SCHEMA and SYS schema in SQL SERVER"},"content":{"rendered":"\n<p>In SQL Server, both <code>INFORMATION_SCHEMA<\/code> and <code>sys<\/code> are schema-based views that provide metadata about database objects. However, there are some differences between the two:<\/p>\n\n\n\n<p><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Content: The <code>INFORMATION_SCHEMA<\/code> views provide a more standardized way of accessing metadata, while the <code>sys<\/code> views provide more detailed information about the database objects.<\/li>\n\n\n\n<li>Compatibility: The <code>INFORMATION_SCHEMA<\/code> views are part of the SQL standard, so they are more compatible with other database management systems. On the other hand, the <code>sys<\/code> views are specific to SQL Server.<\/li>\n\n\n\n<li>Customization: The <code>INFORMATION_SCHEMA<\/code> views are read-only, so they cannot be modified. The <code>sys<\/code> views can be customized using user-defined views, stored procedures, and functions.<\/li>\n\n\n\n<li>Performance: The <code>sys<\/code> views are generally faster than the <code>INFORMATION_SCHEMA<\/code> views, as they use internal system tables that are optimized for performance.<\/li>\n<\/ol>\n\n\n\n<p>In summary, the <code>INFORMATION_SCHEMA<\/code> views provide a more standardized way of accessing metadata, while the <code>sys<\/code> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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: In summary, the INFORMATION_SCHEMA views provide&#8230; <\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-33341","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=33341"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33341\/revisions"}],"predecessor-version":[{"id":33342,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33341\/revisions\/33342"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=33341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=33341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=33341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}