Multiple data types in sys.types

• irobertson

  Hall of Fame

  Points: 3695

  More actions

  April 28, 2010 at 7:43 am

  #232122

  Hi,

  Not a problem as such, as I can work round it, but I'm curious as to why sys.types would contain different types with the same system_type_id and also how the user_type_id gets chosen for sys.columns

  e.g.

  select * from sys.types where system_type_id =231

  /*

  namesystem_type_iduser_type_id

  nvarchar231231

  sysname231256

  */

  select c.* from sys.columns c

  join sys.tables t

  on c.object_id = t.object_id

  where t.name = 'control_Staging_Columns'

  /*

  namecolumn_idsystem_type_iduser_type_id

  Table_Name1231256

  Column_Name2231256

  Data_Type3231256

  Is_Telephone4104104

  Is_Email 5104104

  Is_Postcode6104104

  Is_Name 7104104

  */

  select c.name as c_name

  , c.user_type_id as c_user_type_id

  , ty.name as ty_name

  , ty.user_type_id as ty_useer_type_id

  , c.*

  , t.name as t_name

  from sys.columns c

  join sys.tables t

  on c.object_id = t.object_id

  join sys.types ty

  on c.system_type_id = ty.system_type_id

  where t.name = 'control_Staging_Columns'

  /*

  c_namec_user_type_idty_namety_user_type_id

  Is_Telephone104bit104

  Is_Email104bit104

  Is_Postcode104bit104

  Is_Name104bit104

  Table_Name256nvarchar231

  Column_Name256nvarchar231

  Data_Type256nvarchar231

  Table_Name256sysname256

  Column_Name256sysname256

  Data_Type256sysname256

  */

  Yet I've seen other examples of nvarchar fields that have a sys.columns user_type_id of 231!

  I'd be interested in finding out why this happens...

  Thanks, Iain

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 28, 2010 at 7:52 am

  #1158929

  sysname is an 'alias' for nvarchar of a particular length. so you can declare a variable of type sysname (handy when working with the system tables) and it's actually an nvarchar behind the scenes. Same thing when you create a type (not one from CLR, one with a base type)

  It could well be that the table you mention, when it was created, the data types were specified as sysname, not nvarchar.

  eg

  CREATE TABLE Test (

  Name sysname,

  Othername nvarchar(255)

  )

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass