What is the difference between type and xtype in system views in SQL Server?

Question

There are several columns in many of the system views that have the 'x' prefix, does anyone know what this is suppose to signify?

Answer 1

xtype for UNIQUE CONTRAINT and PRIMARY KEY will be UQ and PK, respectively, while type if both of them is K.

That's the only difference I'm aware of.

Answer 2

IIRC it was sybase compatibility

SQL Server started life as a fork of sybase, and it had just the 'type' column

When Microsoft extended the types they added a new column 'xtype'

You can see soem types don't have a 'type' with

select name, xtype, type from master..systypes

So in general I think you should use the 'xtype' column, and ignore the 'type' column

Answer 3

Most likely is only for backward compatibility purposes.

• type is an older column kept for compatibility with previous versions
• xtype is more complete and is the one that should be used

See sys.sysobjects (Transact-SQL) for a list of differences.