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
/*
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
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