out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?

I sometimes have to unpivot tables, that means, converting all columns into rows. Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second is the column value. . In the example below I found out manually by looking at the column types in the original table, that the best column to store the value is nvarchar(256). **Questions:** How could I find this *"the best value to use as the values column type is nvarchar(256)"* out dynamically? I know I could use sql_variant for the values column type ? In this case can I ignore the collate database_default? That would be an expensive overkill - and I want to avoid that: > sql_variant can have a maximum length of 8016 bytes. This includes > both the base type information and the base type value. The maximum > length of the actual base type value is 8,000 bytes. --============================================================================= -- Unpivot the table - SUCCESSFUL --============================================================================= IF OBJECT_ID('TEMPDB.DBO.#UNPIVOT') IS NOT NULL DROP TABLE #UNPIVOT CREATE TABLE #UNPIVOT( [ColumnNames] [nvarchar](128) NULL, [AllCol] [nvarchar](256) NULL ) INSERT INTO #UNPIVOT([ColumnNames],[AllCol] ) SELECT ColumnNames ,AllCol --INTO TABLEBACKUPS.DBO.UNPIVOT_ FROM ( select [host_platform] = CAST (host_platform collate database_default as [nvarchar](256)), [host_distribution] = CAST (host_distribution collate database_default as [nvarchar](256)), [host_release] = CAST (host_release collate database_default as [nvarchar](256)), [host_service_pack_level] = CAST ([host_service_pack_level] collate database_default as [nvarchar](256)), [host_sku] = CAST ([host_sku] as [nvarchar](256)), [os_language_version] = CAST ([os_language_version] as [nvarchar](256)), [host_architecture] = CAST ([host_architecture] collate database_default as [nvarchar](256)) from sys.dm_os_host_info ) Radhe UNPIVOT ( AllCol FOR ColumnNames IN ( host_platform ,host_distribution ,host_release ,host_service_pack_level ,host_sku ,os_language_version ,host_architecture ) ) unpvt SELECT [ColumnNames] ,[AllCol] FROM #UNPIVOT