April 28, 2010 at 7:43 am
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
/*
name system_type_id user_type_id
nvarchar 231 231
sysname 231 256
*/
select c.* from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where t.name = 'control_Staging_Columns'
/*
name column_id system_type_id user_type_id
Table_Name 1 231 256
Column_Name 2 231 256
Data_Type 3 231 256
Is_Telephone 4 104 104
Is_Email 5 104 104
Is_Postcode 6 104 104
Is_Name 7 104 104
*/
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_name c_user_type_id ty_name ty_user_type_id
Is_Telephone 104 bit 104
Is_Email 104 bit 104
Is_Postcode 104 bit 104
Is_Name 104 bit 104
Table_Name 256 nvarchar 231
Column_Name 256 nvarchar 231
Data_Type 256 nvarchar 231
Table_Name 256 sysname 256
Column_Name 256 sysname 256
Data_Type 256 sysname 256
*/
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
April 28, 2010 at 7:52 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply