2

I want to store a string data to SQL server, but data should be stored in encrypted format. So for this I have used SQL server encryption and to store this value I have used varbinary column. But I am confused about the size of the column because when I encrypt the data (a string value), size of the encrypted data increases. For example,

OPEN SYMMETRIC KEY [mykey] DECRYPTION
BY CERTIFICATE[MyCertificate]
SELECT LEN(EncryptByKey (KEY_GUID('mykey'),CONVERT(nvarchar(8),'10/10/10')))

Results: 68

OPEN SYMMETRIC KEY [mykey] DECRYPTION
BY CERTIFICATE[MyCertificate]
select CONVERT(nvarchar(8),DECRYPTBYKEY(EncryptByKey (KEY_GUID('mykey'),CONVERT(nvarchar(8),'10/10/10'))))

Resuls: 8

I can set the size of varbinary column to 100 and proceed but there will be waste of some space.

vishwajeetrkale
  • 461
  • 2
  • 7
  • 16

1 Answers1

2

No, there will not be any waste. You can even use VARBINARY(MAX). That's what "VAR" means: variable. Only the necessary space will be allocated.

And besides, worrying about bytes is pointless: modern machines have plenty of storage space to spare.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
  • ok, thank you, but there should be some calculation to decide size of this varbinary column becuase i have other data columns of differrent size and i need to encrypt those too, so just keeping varbinary(some number) for any size(i mean less than size of varbinary column) of data, is this OK? – vishwajeetrkale Jul 14 '17 at 07:24
  • Okay, it is true that there should be such a calculation, somewhere, somehow. But I do not know of any function that provides it. My answer is to say that by defining a binary column of the maximum size you should not need to worry about the precise size that it needs to have. – Mike Nakis Jul 14 '17 at 07:28