SQL Server：我应该在 sys 表上使用 information_schema 表吗？

SQL Server: should I use information_schema tables over sys tables?

提问by juur

In SQL Server there is two schemas for metadata:

在 SQL Server 中，元数据有两种架构：

• INFORMATION_SCHEMA
• SYS

• INFORMATION_SCHEMA
• 系统

I have heard that INFORMATION_SCHEMAtables are based on ANSI standard. When developing e.g. stored procedures, should it be wise to use INFORMATION_SCHEMAtables over systables?

我听说INFORMATION_SCHEMA表是基于 ANSI 标准的。在开发例如存储过程时，INFORMATION_SCHEMA在sys表上使用表是否明智？

采纳答案by codingbadger

I would always try to use the Information_schemaviews over querying the sysschema directly.

我总是会尝试使用Information_schema视图sys直接查询架构。

The Views are ISO compliant so in theory you should be able to easily migrate any queries across different RDBMS.

视图符合 ISO 标准，因此理论上您应该能够轻松地跨不同 RDBMS 迁移任何查询。

However, there have been some cases where the information that I need is just not available in a view.

但是，在某些情况下，我需要的信息在视图中不可用。

I've provided some links with further information on the views and querying a SQL Server Catalog.

我提供了一些链接，其中包含有关视图和查询 SQL Server 目录的更多信息。

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

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

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

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

回答by Martin Smith

Unless you are writing an application which you know for a fact will need to be portable or you only want quite basic information I would just default to using the proprietary SQL Server system views to begin with.

除非您正在编写一个您知道事实需要可移植的应用程序，或者您只需要非常基本的信息，否则我将默认使用专有的 SQL Server 系统视图开始。

The Information_Schemaviews only show objects that are compatible with the SQL-92 standard. This means there is no information schema view for even quite basic constructs such as indexes (These are not defined in the standard and are left as implementation details.) Let alone any SQL Server proprietary features.

该Information_Schema意见只能说明是与SQL-92标准兼容的对象。这意味着即使是非常基本的结构（例如索引）也没有信息架构视图（标准中没有定义这些结构，而是作为实现细节保留。）更不用说任何 SQL Server 专有功能了。

Additionally it is not quite the panacea for portability that one may assume. Implementations do still differ between systems. Oracle does not implement it "out of the box" at all and the MySql docssay:

此外，它并不是人们可能认为的便携性的灵丹妙药。系统之间的实现仍然不同。Oracle 根本没有“开箱即用”实现它，MySql 文档说：

Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such column is the ENGINE column in the INFORMATION_SCHEMA.TABLES table.

SQL Server 2000（也遵循该标准）的用户可能会注意到非常相似。但是，MySQL 省略了许多与我们的实现无关的列，并添加了特定于 MySQL 的列。INFORMATION_SCHEMA.TABLES 表中的 ENGINE 列就是这样的一列。

Even for bread and butter SQL constructs such as foreign key constraints the Information_Schemaviews can be dramatically less efficient to work with than the sys.views as they do not expose object ids that would allow efficient querying.

即使对于诸如外键约束之类的基本 SQL 构造，Information_Schema视图的使用效率也可能比sys.视图低得多，因为它们不公开允许高效查询的对象 ID。

e.g. See the question SQL query slow-down from 1 second to 11 minutes - why?and execution plans.

例如，请参阅问题SQL 查询速度从 1 秒减慢到 11 分钟 - 为什么？和执行计划。

INFORMATION_SCHEMA

INFORMATION_SCHEMA

sys

系统

回答by Peter Radocchia

INFORMATION_SCHEMAis more suitable for external code that may need to interface with a variety of databases. Once you start programming inthe database, portability kind of goes out the window. If you are writing stored procedures, that tells me you have committed to a particular database platform (for better or for worse). If you have committed to SQL Server, then by all means, use the sysviews.

INFORMATION_SCHEMA更适合可能需要与各种数据库接口的外部代码。一旦您开始在数据库中编程，可移植性就会消失。如果您正在编写存储过程，则表明您已承诺使用特定的数据库平台（无论好坏）。如果您已承诺使用 SQL Server，那么一定要使用sys视图。

回答by Tombala

I won't repeat some of the other answers but add a performance perspective. information_schema views, as Martin Smith mentions in his answer, are not the most efficient source of this information since they have to expose standard columns that have to be collected from multiple underlying sources. sys views can be more efficient from that perspective, so if you have high performance requirements, and don't have to worry about portability, you should probably go with sys views.

我不会重复其他一些答案，而是添加一个性能角度。information_schema 视图，正如 Martin Smith 在他的回答中提到的，不是此信息的最有效来源，因为它们必须公开必须从多个底层来源收集的标准列。从这个角度来看，sys 视图可以更高效，因此如果您有很高的性能要求，并且不必担心可移植性，您可能应该使用 sys 视图。

For example, the first query below uses information_schema.tables to check if a table exists. The second one uses sys.tables to do the same thing.

例如，下面的第一个查询使用 information_schema.tables 来检查表是否存在。第二个使用 sys.tables 来做同样的事情。

if exists (select * from information_schema.tables where table_schema = 'dbo' and table_name = 'MyTable')
    print '75% cost';

if exists (select * from sys.tables where object_id = object_id('dbo.MyTable'))
    print '25% cost';

When you view the IO for these, the first query has 4 logical reads to sysschobjs and sysclsobjs, while the second one has none. Also the first one does two non-clustered index seeks and a key lookup while the second one only does a single clustered index seek. First one costs ~3x more than the second one according to query plans. If you have to do this lots of times in a large system, say for deployment time, this could add up and cause performance problems. But this really only applies to heavily loaded systems. Most IT line of business systems don't have these levels of performance issues.

当您查看这些 IO 时，第一个查询对 sysschobjs 和 sysclsobjs 有 4 个逻辑读取，而第二个没有。此外，第一个执行两次非聚集索引查找和键查找，而第二个仅执行单个聚集索引查找。根据查询计划，第一个成本比第二个成本高约 3 倍。如果您必须在大型系统中多次执行此操作，例如部署时间，这可能会累积并导致性能问题。但这实际上只适用于重载系统。大多数 IT 业务线系统没有这些级别的性能问题。

Again, the overall cost of these are very small individually when compared to other queries in most systems but if your system has a lot of this type of activity, it could add up.

同样，与大多数系统中的其他查询相比，这些的总体成本非常小，但如果您的系统有很多此类活动，则可能会加起来。