THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SQL_VARIANT_PROPERTY function

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:

image

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

Published Wednesday, August 03, 2011 9:50 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Yikes, I'd rather just type 8 twice, especially if it's something that isn't likely to change. :-)

Just kidding Jamie, nice post.

August 3, 2011 6:39 PM
 

jamiet said:

I suspect most people would - they're not as anally retentive about it as I am :)

August 4, 2011 1:37 AM
 

John Neville said:

Thanks for this post :-)  I'd never seen reference to this function before either.

I did some tests with it, specifically trying to re-write some sprocs which select from INFORMATION_SCHEMA.COLUMNS into variables.  I thought that using SQL_VARIANT_PROPERTY would improve the code, or make it more readable.

The current code is (simplified):

DECLARE @len_StoreNumber int, @len_TillNumber int, @len_TransactionNumber int;

SELECT

@len_StoreNumber = MAX(GetLen.StoreNumberLen),

@len_TillNumber = MAX(GetLen.TillNumberLen),

@len_TransactionNumber = MAX(GetLen.TransactionNumberLen)

FROM (

SELECT

CASE

WHEN cols.COLUMN_NAME = 'StoreNumber' THEN cols.CHARACTER_MAXIMUM_LENGTH

END AS StoreNumberLen,

CASE

WHEN cols.COLUMN_NAME = 'TillNumber' THEN cols.CHARACTER_MAXIMUM_LENGTH

END AS TillNumberLen,

CASE

WHEN cols.COLUMN_NAME = 'TransactionNumber' THEN cols.CHARACTER_MAXIMUM_LENGTH

END AS TransactionNumberLen

FROM

INFORMATION_SCHEMA.COLUMNS AS cols

WHERE

cols.TABLE_SCHEMA = 'RawImport' AND cols.TABLE_NAME = 'CouponTransactions') AS GetLen;

The execution plan for this shows index seeks on [sysschobjs] and [syscolpars], and it ran in 00:00:00.030.

Whereas re-writing it as:

DECLARE @varprop_StoreNumber int, @varprop_TillNumber int, @varprop_TransactionNumber int;

SELECT

@varprop_StoreNumber = Cast(SQL_VARIANT_PROPERTY(trans.StoreNumber, 'MaxLength') AS int),

@varprop_TillNumber = Cast(SQL_VARIANT_PROPERTY(trans.TillNumber, 'MaxLength') AS int),

@varprop_TransactionNumber = Cast(SQL_VARIANT_PROPERTY(trans.TransactionNumber, 'MaxLength') AS int)

FROM

RawImport.CouponTransactions AS trans;

PRINT 'StoreNumber length is: ' + Cast(@varprop_StoreNumber AS varchar(5)) + char(13)

+ 'TillNumber length is: ' + Cast(@varprop_TillNumber AS varchar(5)) + char(13)

+ 'TransactionNumber length is: ' + Cast(@varprop_TransactionNumber AS varchar(5));

...ran in 00:00:26.714 and spent 99% of the time performing a Clustered Index Scan.  In other words it scans the whole table (in this instance 7.8m rows) in order to work out the MaxLength of the columns.

Selecting TOP 1 avoids this, but i'm still inclined to use the INFORMATION_SCHEMA for these sprocs.  For now anyway.

I'm sure when I've had more of a play with SQL_VARIANT_PROPERTY i'll be more comfortable with it.

Thanks anyway for a great post :-)

August 9, 2011 6:44 AM
 

jamiet said:

Interesting, thanks John. Maybe SQL_VARIANT_PROPERTY would be best served with variables rather than columns. it seems utterly stupid that it does a scan though.

August 9, 2011 6:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement