SQL Server – Transact SQL – What is return sys.fn_varbintohexsubstring(1,@pbinin,1,0)?

Background

Was spending a bit of time staring at Activity Monitor and noticed that “return sys.fn_varbintohexsubstring(1,@pbinin,1,0)” was one of the queries mentioned.

 

What is sys.fn_varbintohexsubstring(1,@pbinin,1,0)?

Well it converts binary data to string.

We are using cell level encryption and as so our data is saved as binaries. To decrypt the data we issue DecryptByKey and convert/cast the returned data to our original data type.

Sample Code


   cast(DecryptByKey(address) as varchar(255))

References

  1. Itzik Ben-Gan – SQL Server 2008’s T-SQL Features – Small new features provide big benefits
    Feb 17, 2009
    SQL Server 2008 – Features
  2. Raul Garcia – Recommendations for using Cell Level Encryption in Azure SQL Database
    May 12th, 2015
    Link

 

 

One thought on “SQL Server – Transact SQL – What is return sys.fn_varbintohexsubstring(1,@pbinin,1,0)?

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s