I learnt something about T-SQL today that I never knew and I figure I’m not the only one so it might be worth sharing what I learnt; namely, I discovered the SQL_VARIANT_PROPERTY function.
It all started when going over a piece of code that I recently inherited, here is an abbreviated version of that code in order to demonstrate the problem:
DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8);
SET @dest = @src;
SELECT @dest;
If you execute that then the result is a truncated value:
This is not good. We have lost data and yet there is no indication that a problem has occurred – the truncation is silent. Happily if you are using database projects in Visual Studio and have turned on code analysis then you will get a warning about this that goes something along the lines of:
"SR0014 : Microsoft.Rules.Data : Data loss might occur when casting from VarChar(10) to VarChar(8)."
It may be the case of course that you are happy with the truncation in which case you are well advised to turn this into an explicit rather than implicit cast which you can do using the LEFT function:
DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8);
SET @dest = LEFT(@src,8);
This will get rid of the code analysis warning in Visual Studio and also indicate to anyone reading the code that the truncation is intentional. I still did not like this code though – I did not like the fact that we had to enter the length of 8 in two different places, it is not DRY. I would rather be able to say “truncate the value to the length of the @dest field” so that if anyone ever shortened the length of @dest we wouldn’t return to an implicit cast. That desire prompted me to raise a request on Connect: New function that returns the maximum possible length of a variable or column.
SQL Server Program Manager Umachander Jayachandran replied informing me that such a function already existed in the shape of
SQL_VARIANT_PROPERTY(expression,property)
which can return one of a series of properties about a scalar value. I adapted my code like so:
DECLARE @src varchar(10) = 'some value';
DECLARE @dest varchar(8) = ''; --note that we need to initialise @dest in order to use SQL_VARIANT_PROPERTY on it
SET @dest = LEFT(@src,CAST(SQL_VARIANT_PROPERTY(@dest,'maxlength') AS int));
SELECT @dest;
We are now truncating the value without having to specify the length which twice I happen to think is pretty cool (but maybe that’s just me).
In case you’re interested the complete list of properties that can be queried for using SQL_VARIANT_PROPERTY is:
- BaseType
- Precision
- Scale
- TotalBytes
- Collation
- MaxLength
In a way SQL_VARIANT_PROPERTY() is the closest we have in our archaic T-SQL world to .Net’s System.Type. System.Type is, as some of you may know, the basis for reflection and type inference in .Net development; let’s keep hoping that more type inference alike functionality comes to T-SQL in the not too distant future.
@jamiet