Share via

  • Article

Compatibility Views (Transact-SQL)

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

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. This is because you can create more than 32,767 users, groups, and roles, and 32,767 data types. For example, if you were to create 32,768 users, and then run the following query: SELECT * FROM sys.sysusers. If ARITHABORT is set to ON, the query fails with an arithmetic overflow error. If ARITHABORT is set to OFF, the uid column returns NULL.

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 view

xusertype

syscolumns

sys.columns

usertype

syscolumns

sys.columns

memberuid

sysmembers

sys.database_role_members

groupuid

sysmembers

sys.database_role_members

uid

sysobjects

sys.objects

uid

sysprotects

  • sys.database_permissions

  • sys.server_permissions

grantor

sysprotects

  • sys.database_permissions

  • sys.server_permissions

xusertype

systypes

sys.types

uid

systypes

sys.types

uid

sysusers

sys.database_principals

altuid

sysusers

sys.database_principals

gid

sysusers

sys.database_principals

uid

syscacheobjects

sys.dm_exec_plan_attributes

uid

sysprocesses

sys.dm_exec_requests