SQL Server：我应该使用information_schema表还是sys表吗？

Question

SQL Server：我应该使用information_schema表还是sys表吗？

sqlsql-serversql-server-2008stored-proceduresmetadata

51

在SQL Server中，有两种元数据模式：

INFORMATION_SCHEMA
SYS

我听说INFORMATION_SCHEMA表是基于ANSI标准的。在开发存储过程等操作时，使用INFORMATION_SCHEMA表会比使用sys表更加明智吗？

- juur

资源数据库是一个只读数据库，其中包含了SQL Server附带的所有系统对象。例如sys.objects等SQL Server系统对象在物理上持久化在资源数据库中，但在每个数据库的sys模式中逻辑上出现。 - Channa

4个回答

31

我总是尽量使用Information_schema视图来查询数据库，而不是直接查询sys模式。

这些视图符合ISO标准，因此理论上您应该能够轻松地将任何查询迁移到不同的关系型数据库管理系统（RDBMS）中。

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

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

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

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

- codingbadger

19

是的，只要您需要或希望在不同数据库之间进行“可移植性”，我同意这种方法。如果您只使用 SQL Server 进行工作，我通常会使用“sys.”目录视图，因为它们更全面、更简洁和更好组织（在我看来） - 尽管我完全知道这些查询在 DB2 或 MySQL 上无法正常运行... - marc_s

@Marc_s - 是的，我真的应该强调一下。如果你确信它不需要迁移到其他地方，那么使用sys模式就可以了。好的评论。 - codingbadger

10

INFORMATION_SCHEMA 更适合需要与多种数据库进行接口的外部代码。一旦开始在数据库中编程，可移植性就有点无从谈起了。如果你正在编写存储过程，那说明你已经致力于特定的数据库平台（无论是好是坏）。如果你已经致力于 SQL Server，则务必使用 sys 视图。

- Peter Radocchia

1

我不会重复其他答案，但会从性能的角度来补充一下。如Martin Smith在他的回答中提到的那样，information_schema视图并不是获取此信息最有效的来源，因为它们必须公开标准列，这些列必须从多个底层源收集。从这个角度来看，sys视图可能更有效率，因此如果您有高性能要求，并且不必担心可移植性问题，您应该选择sys视图。

例如，下面的第一个查询使用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';

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

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

- Tombala

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

除非您正在编写一个肯定需要可移植性的应用程序，或者您只需要基本信息，否则我建议您一开始就使用专有的SQL Server系统视图。

Information_Schema 视图仅显示与SQL-92标准兼容的对象。这意味着甚至对于基本构造，例如索引（这些未在标准中定义，并留作实现细节），也没有信息模式视图。更不要说任何SQL Server专有功能了。

此外，它并不是人们可能认为的可移植性万灵药。实现确实会因系统而异。Oracle根本不会“开箱即用”实现它，而MySql文档说：

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

即使对于烤面包和黄油般的SQL结构，例如外键约束，Information_Schema视图与sys. 视图相比，在处理效率上也可以大大降低，因为它们不会公开允许高效查询的对象ID。

例如，请参见问题SQL query slow-down from 1 second to 11 minutes - why?和执行计划。

INFORMATION_SCHEMA

Plan

sys

Plan