-- Just declare a table variable with the constant value of interest as a computed column
-- The computed column has a GUID name to make it easy to find
DECLARE @A TABLE (dummy INT NULL, [AB85729E-F946-4165-9F71-9162A5CFCCEB] AS 0.5);

-- Show the type assigned to the column
SELECT  table_name = (SELECT T.name FROM tempdb.sys.tables T WHERE T.[object_id] = C.[object_id]),
        [type_name] = (SELECT Y.name FROM tempdb.sys.types Y WHERE Y.system_type_id = C.system_type_id),
        C.[precision],
        C.scale,
        C.is_nullable,
        max_byte_length = C.max_length
FROM    tempdb.sys.columns C
WHERE   C.name = N'AB85729E-F946-4165-9F71-9162A5CFCCEB';

DECLARE @a sql_variant
SET @a = ROUND(0.5,0);
SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale]

SELECT @a

SELECT  S.quoted_identifier,
        S.arithabort,
        S.ansi_null_dflt_on,
        S.ansi_defaults,
        S.ansi_warnings,
        S.ansi_padding,
        S.ansi_nulls,
        S.concat_null_yields_null
FROM    sys.dm_exec_sessions S
WHERE   session_id = @@SPID;

SET QUOTED_IDENTIFIER OFF
SET ARITHABORT  OFF
SET ANSI_NULL_DFLT_ON  OFF
SET ANSI_DEFAULTS  OFF
SET ANSI_WARNINGS  OFF
SET ANSI_PADDING  OFF
SET ANSI_NULLS  OFF
SET CONCAT_NULL_YIELDS_NULL OFF

SELECT  S.quoted_identifier,
        S.arithabort,
        S.ansi_null_dflt_on,
        S.ansi_defaults,
        S.ansi_warnings,
        S.ansi_padding,
        S.ansi_nulls,
        S.concat_null_yields_null
FROM    sys.dm_exec_sessions S
WHERE   session_id = @@SPID;


DECLARE @a sql_variant
SET @a = ROUND(0.5,0);
SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale]
,CAST(@a as varbinary) Binary
SELECT @a