• Toreador (11/30/2011)


    KWymore (11/30/2011)


    I am curious how many out there are utilizing sql_variant and under which circumstances?

    I've used it for generic audit tables, where we store something like table name, column name, old value, new value - the old/new values are sql_variant as they could be any data type.

    Of course you'd never do any comparisons between different data types in these circumstances.

    Toreador, thanks! I, too, was wondering where one would use sql_variant - now it seems obvious.

    That said, I appreciate David Data's comment:

    David Data (12/1/2011)


    I see that even if you set

    @v1 sql_variant = cast ('15.00' as float(53)),

    @v2 sql_variant = cast ('16.00' as decimal(18,4)),

    which makes

    v1 v2

    1516.0000

    SQL Server still says v1 > v2.

    This behaviour is very counter-intuitive - to say nothing of being arithmetically incorrect!

    It would be better if it produced the result one would expect - or if it won't, if it refused to do such comparisons. I can imagine many data errors being caused by people not noticing this behaviour and believing the wrong results they get.

    I really don't understand why someone would want to do a comparison that is based on the data type. I can only guess that they chose to set it up this way to avoid errors that might occur in implicit conversions. The lesson I learn from this is to not do comparisons on sql_variant.