My favorite data type

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 favorite.

After a bit of thinking, I’ll have to go with… *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 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 and TotalBytes of different data types.

I had an indexed view that multiplied two decimal numbers – what was the resulting size of that column? sql_variant to the rescue.

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

select @myFavourite = @number1 * @number2

	  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 this as an exercise for the reader.

I’ve seen it used only once in production in an EAV table (Entity, Attribute, Value) where sql_variant column was holding the values and 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, no questions. It’s everywhere (even in non-legacy databases) even though it’s replaced by superior datetime2.

There is some millisecond rounding and it can eat up more bytes than more specific date type – but none of this is serious enough that it warrants rewrite. So it’s this minor inefficiency that will always haunt the database and that’s why I hate it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.