My Favourite Data Type (T-SQL Tuesday #133)

Tom

2021-03-09

2 minutes

I’m mostly indifferent to data types – it’s like asking what is your favorite spatula. They all each have their purpose. I admit I like legacy data types because they provide job security but they are not really my favourite.

After a bit of thinking, I’ll have to go with the… *drumroll*… sql_variant.

Huh, sql_variant? But why? I’ve met DB developers who weren’t even aware of its existence.

First of all, it can hold the majority of other data types, so it’s superior by definition. It’s at the top of the data precedence list of the system data types. It’s got a cool underscore in its name. No implicit conversion errors when converting from the sql_variant, because you have to use explicit conversion (hey, it’s a feature!).

But the real reason is the SQL_VARIANT_PROPERTY function. With that, I can get

BaseType
Collation
MaxLength
Precision
Scale
TotalBytes of different data types.

I had an indexed view that multiplied two decimal numbers – what was the resulting size of that column? We can use sql_variant to figure this out.

DECLARE
    @number1 decimal (8,4) = 1234.4321
    , @number2 decimal (14,6) = 12345678.654321
    , @myFavourite sql_variant

SELECT @myFavourite = @number1 * @number2

SELECT
      SQL_VARIANT_PROPERTY(@myFavourite, 'BaseType')   AS BaseType
    , SQL_VARIANT_PROPERTY(@myFavourite, 'Collation')  AS [Collation]
    , SQL_VARIANT_PROPERTY(@myFavourite, 'MaxLength')  AS MaxLength
    , SQL_VARIANT_PROPERTY(@myFavourite, 'Precision')  AS [Precision]
    , SQL_VARIANT_PROPERTY(@myFavourite, 'Scale')      AS Scale
    , SQL_VARIANT_PROPERTY(@myFavourite, 'TotalBytes') AS TotalBytes

I’ll leave the result as an exercise for the reader.

I’ve seen it used only once in production in an EAV table (Entity, Attribute, Value) where the sql_variant column was holding the values and a different column described the required data type. But maybe because it’s so rare for me is the reason I like it.

For the least favourite type – datetime, hands down. It’s everywhere (even in non-legacy databases) even though it’s replaced by the superior <code>datetime2</code>. There is some millisecond rounding and it can eat up more bytes than a more specific date type – but none of this is serious enough that it warrants a rewrite.

So it’s this minor inefficiency that will always haunt the database and that’s why I hate it.

TSQL