Unpivot sys.assembly_modules System View with Collation Issue


The system view sys.assembly_modules has this structure:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-assembly-modules-transact-sql?view=sql-server-2017

Column_name
object_id
assembly_id
assembly_class
assembly_method
null_on_null_input
execute_as_principal_id

We can check this view with sp_help:
USE master;
GO
EXEC sp_help ‘sys.assembly_modules’;
GO

/*
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
object_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_class nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
assembly_method nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
null_on_null_input bit no 1 yes (n/a) (n/a) NULL
execute_as_principal_id int no 4 10 0 yes (n/a) (n/a) NULL
*/
Both assembly_class and assembly_method have collation of Latin1_General_BIN, which is not a default collation for most servers I have seen.

The following unpivot code will generate error:

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

Msg 8167, Level 16, State 1, Line 19
The type of column “assembly_method” conflicts with the type of other columns specified in the UNPIVOT list.

But you can add the Latin1_General_BIN collation to the cast assembly_id column.

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) Collate Latin1_General_BIN AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

But another unpivot syntax (CROSS APPLY )can avoid this error without explicit adding collation:

select Label,Value from (SELECT
Cast([assembly_id] as nvarchar(128)) [assembly_id],
[assembly_method]
FROM [master].[sys].[assembly_modules]) t
Cross apply (Values(‘assembly_id’ ,[assembly_id])
,(‘assembly_method’, [assembly_method]) ) d(Label,Value)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3745569-45f3-4f0d-8f23-cbbbc2030fec/unpivot-data-type-error?forum=transactsql

–******************* Convert_Implict in play for the result Collation

Declare @Sample Table(
ColA int
–varchar(10) Collate SQL_Latin1_General_CP1_CI_AS
, ColB varchar(10) Collate Latin1_General_Bin);
Insert @Sample(ColA, ColB) Values (‘1’, ‘x’);
Insert @Sample(ColA, ColB) Values (‘2’, ‘y’);

select Label,Value from (

SELECT
Cast (ColA as varchar(10) ) as ColA, ColB
FROM @Sample) t
Cross apply (Values(‘ColA’ ,[ColA])
,(‘ColB’, [ColB]) ) d(Label,Value);
I found some related information to this issue.

I checked the execution plan of this query, there is an implicit_convert for colA to varchar(10).

Based on Collation Precedence document

https://docs.microsoft.com/en-us/sql/t-sql/statements/collation-precedence-transact-sql?view=sql-server-2017

, the result column (value column after unpivot ) collation should be Latin1_General_Bin in this sample.

I put the result to another table and check the column collation of the column value and it is confirmed that this is the case.
The following table summarizes the rules.

Operand coercion label Explicit X Implicit X Coercible-default No-collation
Explicit Y Generates Error Result is Explicit Y Result is Explicit Y Result is Explicit Y
Implicit Y Result is Explicit X Result is No-collation Result is Implicit Y Result is No-collation
Coercible-default Result is Explicit X Result is Implicit X Result is Coercible-default Result is No-collation
No-collation Result is Explicit X Result is No-collation Result is No-collation Result is No-collation

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s