tags:

views:

313

answers:

3

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

A: 

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.

Quassnoi
A: 

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

TFD
+1  A: 

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.

Aleris