系统兼容性视图(Transact-sql)System Compatibility Views (Transact-SQL)

适用于:Applies to: 是 SQL ServerSQL Server (所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是 SQL ServerSQL Server (所有支持的版本)yesSQL ServerSQL Server (all supported versions)

SQL ServerSQL Server 早期版本中的许多系统表现在都作为一组视图实现。Many of the system tables from earlier releases of SQL ServerSQL Server are now implemented as a set of views. 这些视图称为兼容性视图,仅用于向后兼容。These views are known as compatibility views, and they are meant for backward compatibility only. 兼容性视图公开的元数据在 SQL Server 2000 (8.x)SQL Server 2000 (8.x) 中也提供。The compatibility views expose the same metadata that was available in SQL Server 2000 (8.x)SQL Server 2000 (8.x). 但是,兼容性视图不公开与在 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 及更高版本中引入的功能有关的任何元数据。However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later. 因此,当您使用新功能(例如 Service BrokerService Broker 或分区)时,必须切换到使用目录视图。Therefore, when you use new features, such as Service BrokerService Broker or partitioning, you must switch to using the catalog views.

升级到目录视图的另一个原因是,存储用户 ID 和类型 ID 的兼容性视图列可能返回 NULL 或触发算术溢出。Another reason for upgrading to the catalog views is that compatibility view columns that store user IDs and type IDs may return NULL or trigger arithmetic overflows. 这是因为您可以创建超过 32,767 个用户、组和角色,以及超过 32,767 种数据类型。This is because you can create more than 32,767 users, groups, and roles, and 32,767 data types. 例如,如果您要创建32768用户,然后运行以下查询: SELECT * FROM sys.sysusersFor example, if you were to create 32,768 users, and then run the following query: SELECT * FROM sys.sysusers. 如果 ARITHABORT 设置为 ON,则查询会失败,并出现算术溢出错误。If ARITHABORT is set to ON, the query fails with an arithmetic overflow error. 如果 ARITHABORT 设置为 OFF,则uid列返回 NULL。If ARITHABORT is set to OFF, the uid column returns NULL.

若要避免这些问题,建议您使用新增的目录视图,这些视图可以处理增加的用户 ID 和类型 ID 数目。To avoid these problems, we recommend that you use the new catalog views that can handle the increased number of user IDs and type IDs. 下表列出了会出现此溢出的列。The following table lists the columns that are subject to this overflow.

列名称Column name 兼容性视图Compatibility view SQL Server 2005 视图SQL Server 2005 view
xusertypexusertype syscolumnssyscolumns sys.columnssys.columns
usertypeusertype syscolumnssyscolumns sys.columnssys.columns
memberuidmemberuid sysmemberssysmembers sys.database_role_memberssys.database_role_members
groupuidgroupuid sysmemberssysmembers sys.database_role_memberssys.database_role_members
uiduid sysobjectssysobjects sys.objectssys.objects
uiduid sysprotectssysprotects sys.database_permissionssys.database_permissions

sys.server_permissionssys.server_permissions
grantorgrantor sysprotectssysprotects sys.database_permissionssys.database_permissions

sys.server_permissionssys.server_permissions
xusertypexusertype systypessystypes sys.typessys.types
uiduid systypessystypes sys.typessys.types
uiduid sysuserssysusers sys.database_principalssys.database_principals
altuidaltuid sysuserssysusers sys.database_principalssys.database_principals
gidgid sysuserssysusers sys.database_principalssys.database_principals
uiduid syscacheobjectssyscacheobjects sys.dm_exec_plan_attributessys.dm_exec_plan_attributes
uiduid sysprocessessysprocesses sys.dm_exec_requestssys.dm_exec_requests

在用户数据库中引用时,在 SQL Server 2000 (如sys.syslanguagessys.syscacheobjects)中被公布为弃用的系统表现在将绑定到sys架构中的后向兼容性视图。When referenced in a user database, system tables which were announced as deprecated in SQL Server 2000 (such as syslanguages or syscacheobjects), are now bound to the back-compatibility view in the sys schema. 因为多个版本均已不推荐使用 SQL Server 2000 系统表,此更改不被视为重大更改。Since the SQL Server 2000 system tables have been deprecated for multiple versions, this change is not considered a breaking change.

示例:如果用户在用户数据库中创建名为sys.syslanguages的用户表,则在 SQL Server 2008 中, SELECT * from dbo.syslanguages; 该数据库中的语句将返回用户表中的值。Example: If a user creates a user-table called syslanguages in a user-database, in SQL Server 2008, the statement SELECT * from dbo.syslanguages; in that database would return the values from the user table. 从 SQL Server 2012 开始,此做法将从系统视图sys.sys语言返回数据。Beginning in SQL Server 2012, this practice will return data from the system view sys.syslanguages.

另请参阅See Also

Transact-sql)的目录视图 ( Catalog Views (Transact-SQL)
将系统表映射到系统视图 (Transact-sql)Mapping System Tables to System Views (Transact-SQL)