My interest in writing this article was started by an MSDN article titled SQL Server 2005: The CLR Enters the Relational Stage. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL. (UPDATE: An alert reader found an even better approach!)
First please take a second to read the MSDN article. Pay special attention to how much C# code is required for the solution and how flexible it is.
Note: I also want to mention here that I started out trying to replicate their solution. I tried to install the MSI but that failed with an invalid characters message. I tried to recreate the CLR code but since he mostly posted snippets and had three versions I finally gave up trying to get that to work. One thing I really wanted to do was compare performance on identical data. Unfortunately I wasn't able to do that. My example will use the AdventureWorks database
Let's start at the TOP
Along the way to making this solution work we'll discuss a few new features of SQL Server 2005. The first is that the TOP clause can now take a variable:
DECLARE @Rows INT
SET @Rows = 10
SELECT TOP ( @Rows ) *
FROM Sales.SalesOrderHeaderThis will return the top 10 rows from SalesOrderHeader. You can also replace @Rows with anything that evaluates to a number. The following query looks odd but runs just fine:
SELECT TOP (
SELECT COUNT(*)
FROM Sales.Customer
) *
FROM Sales.SalesOrderHeaderThere are 19,185 rows in the Customer table and this query returns the top 19,185 rows from SalesOrderHeader. You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.
My Function
Next we need a function to return the TOP X rows from SalesOrderHeader based on the total sales amount. That function looks like this:
CREATE FUNCTION dbo.fn_GetTopOrders(@custid AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY TotalDue DESC
GONotice that it accepts the number of orders to return as a parameter. Also notice that I'm using SELECT * all over the place. I really encourage you to explicitly list out the columns when you write this for real. If you call this with a CustomerID and a number of rows it will return that many rows ordered by the total amount of the order in descending order. Also notice that there is an ORDER BY clause in this function. We'll talk about that in a minute.
Apply the APPLY Clause
The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY
CustomerID ASC, TotalDue DESCwhich results in this...
CustomerID SalesOrderID TotalDue
----------- ------------ ---------------------
1 45283 37643.1378
1 46042 34722.9906
1 44501 26128.8674
2 46976 10184.0774
2 47997 5469.5941
2 57044 4537.8484
3 53616 92196.9738
3 47439 78578.9054
3 48378 56574.3871
4 47658 132199.8023
. . .The APPLY clause acts like a JOIN without the ON clause comes in two flavors: CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side (Customers) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side (Customers) if the table-valued-function returns rows.
Notice that I'm just passing in the CustomerID to the function. It returns the TOP 3 rows based on the amount of the order. Since I'm using CROSS APPLY a customer without orders won't appear in the list. I can also pass in a number other than 3 to easily return a different number of orders per customer. Even better I can pass in a different number of orders for each customer. So I could list the top 5 orders for one type of customer but the top 10 for another type of customer. How cool is that?!?
And it gets even better. Remember the function has an ORDER BY in it. It will always return the top orders based on the amount. However you can change the ORDER BY clause in the query that calls the function to display those rows in whatever order you want. You could easily display the top 3 orders in ascending order instead.
Performance and Conclusion
Unfortunately I wasn't able to compare the performance of my solution to MSDN's. The query plan from my solution doesn't look that bad. (And did I mention that it showed me a full query plan for the query with the UDF rather than just a UDF step? Sweet!) If anyone gets their solution running send me the code and I'll update the article.
I also think their solution returns the result as a comma separated value. Now that would be a great use for the CLR. You could easily wrap that around the results of this function for a fast, effecient procedure. And it happens to be the next article I'm working on. :) If you want to see a great article on using the CLR inside SQL Server I'd start with Using CLR Integration in SQL Server 2005. The code is from an earlier beta but it gives you a great idea of when to use CLR code.
In summary I think that 5-10 lines of SQL is much simpler than 50+ lines of C# (or VB) for this type of solution. The article provides a lot of good information on the CLR inside SQL Server. I just don't think this is the best place to use it. One of my biggest fears is that people will use the CLR incorrectly and cause performance problems. This is the first real case I've seen of this. Is this really how Microsoft wants us to use the CLR?
UPDATE
I was curious when I posted this how long it would take to find an even better solution. It didn't take long. Arnold Fribble posted it in the forums. The syntax needed a little tweaking but it appears you can do this in a single query. And how did I forget the PARTITION BY clause of the ROW_NUMBER function. Doh! Here's the cleaned up query:
SELECT
CustomerID,
SalesOrderID,
TotalDue
FROM (
SELECT
O.CustomerID,
O.SalesOrderID,
O.TotalDue,
ROW_NUMBER = ROW_NUMBER() OVER (
PARTITION BY O.CustomerID
ORDER BY O.TotalDue DESC)
FROM
Sales.SalesOrderHeader AS O
) AS d
WHERE
d.ROW_NUMBER <= 3
Friday, August 24, 2007
Tuesday, August 21, 2007
Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts
SQL Server 2005 introducted the OUTPUT clause which we can use to capture values from the inserted and deleted virtual tables. Previously this data was only available through triggers. We can use this in an INSERT ... SELECT statement to capture all the inserted identity values. Previously this required some type of loop or temporarily altering the target table.
We'll start with two tables: a product table and table of products to insert. The scenario is a vendor that sends you a complete list of all their products and you only need to insert the rows that don't already exist. However you need to insert those new rows into multiple tables. The following script will create the tables in tempdb based on data in AdventureWorks.
(54 row(s) affected)That is almost what we want. We have the identity values in the result set but we don't have a way to work with the data and we don't have a way to tie it back to the original source row. We'll need to add two things. First we'll need to store this result set in a table variable. I'll also remove a few columns to make it easier to read and add an update statement to save the identity value. That script looks like this:
(54 row(s) affected)The OUTPUT clause can also be used with UPDATE and DELETE statements and return values from either the inserted or deleted table. In its simplest form the OUTPUT clause greatly simplifies importing data into SQL Server.
We'll start with two tables: a product table and table of products to insert. The scenario is a vendor that sends you a complete list of all their products and you only need to insert the rows that don't already exist. However you need to insert those new rows into multiple tables. The following script will create the tables in tempdb based on data in AdventureWorks.
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
DROP TABLE [dbo].[Product]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].ProductsToInsert') AND type in (N'U'))
DROP TABLE [dbo].ProductsToInsert
GO
CREATE TABLE Product (
ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL,
ProductNumber NVARCHAR(25) NOT NULL,
ListPrice MONEY NOT NULL)
GO
CREATE UNIQUE INDEX IX_Product_ProductNumber ON Product ( ProductNumber )
GO
CREATE TABLE ProductsToInsert (
RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL,
ProductNumber NVARCHAR(25) NOT NULL,
ListPrice MONEY NOT NULL,
InsertedIdentityValue INT NULL)
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT TOP 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID
GO
INSERT ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
GOThe Product table has an identity column as its primary key. Product number is a natural key on the table. The ProductsToInsert table has the ProductNumber column and a column for whatever identity value is inserted when we put the row into the Product table. You'll notice I only put 450 of the products in the Product table to start with but all 504 in the ProductsToInsert table.
A simple script to insert the new products looks like this:
use tempdb
GO
INSERT Product ([Name], ProductNumber, ListPrice)
SELECT
[Name], ProductNumber, ListPrice
FROM
ProductsToInsert I
WHERE
NOT EXISTS (SELECT 1
FROM Product
WHERE ProductNumber = I.ProductNumber)That inserts the 54 products that weren't in the table previously. We can use the OUTPUT clause to return back the rows that were inserted. That looks like this:
INSERT Product ([Name], ProductNumber, ListPrice)
OUTPUT inserted.ProductID,
inserted.[Name],
inserted.ProductNumber,
inserted.ListPrice
SELECT
[Name], ProductNumber, ListPrice
FROM
ProductsToInsert I
WHERE
NOT EXISTS (SELECT 1 FROM Product
WHERE ProductNumber = I.ProductNumber)When that statement is run it returns the following recordset back to the client:
ProductID Name ProductNumber ListPrice
----------- ----------------------------------- --------------------- -------------
451 LL Bottom Bracket BB-7421 53.99
452 ML Bottom Bracket BB-8107 101.24
453 HL Bottom Bracket BB-9108 121.49
. . .
504 HL Touring Handlebars HB-T928 91.57
(54 row(s) affected)That is almost what we want. We have the identity values in the result set but we don't have a way to work with the data and we don't have a way to tie it back to the original source row. We'll need to add two things. First we'll need to store this result set in a table variable. I'll also remove a few columns to make it easier to read and add an update statement to save the identity value. That script looks like this:
DECLARE @InsertedRows TABLE (ProductID INT, ProductNumber NVARCHAR(25) )
INSERT Product ([Name], ProductNumber, ListPrice)
OUTPUT inserted.ProductID,
inserted.ProductNumber
INTO @InsertedRows
SELECT
[Name], ProductNumber, ListPrice
FROM
ProductsToInsert AS I
WHERE
NOT EXISTS (SELECT 1 FROM Product
WHERE ProductNumber = I.ProductNumber)
UPDATE ProductsToInsert
SET InsertedIdentityValue = T.ProductID
FROM ProductsToInsert I
JOIN @InsertedRows T ON T.ProductNumber = I.ProductNumber
SELECT RowID, ProductNumber, InsertedIdentityValue
FROM ProductsToInsert
WHERE InsertedIdentityValue IS NOT NULLWe declare a table variable to store the results of the OUTPUT clause. We use the OUTPUT INTO syntax to store the results into the table variable. Next we use the table variable to update the source table with the inserted identity columns. After that a simple SELECT statement returns the new values which we can easily use in other statements:
RowID ProductNumber InsertedIdentityValue
----------- ------------------------- ---------------------
451 HB-T721 503
452 HB-T928 504
453 FB-9873 502
. . .
504 BK-R19B-52 470
(54 row(s) affected)The OUTPUT clause can also be used with UPDATE and DELETE statements and return values from either the inserted or deleted table. In its simplest form the OUTPUT clause greatly simplifies importing data into SQL Server.
SQL Server 2005 Encryption – Encryption and data length limitations
SQL Server 2005 encryption solution has some limitations and one of them that has raised a few questions is the limited amount of plaintext that can be encrypted. I hope this article helps answer this question.
What is the limit on the data length that can be encrypted?
The answer to this question is a little bit more complex than a simple number: the limit on the input (plaintext) really depends on the output (ciphertext), and it depends on the key you are using and on whether you are using optional features of the encryption builtins.
You can use the following formula to predict the length of the ciphertext that will result from calling EncryptByKey based on the plaintext length, the key algorithm used and whether the optional authenticator parameter is being used or not. Note: this formula is valid only for the DES and AES key families, it is not valid for RC4, and I would recommend to not even use RC4 at all)
@CipherLen = FLOOR( (8 +@PTLen + (@UsesAuth * @HASHLEN) ) / @BLOCK) + 1 ) * @BLOCK + 16 + @BLOCK + 4
Where:
@CipherLen: The ciphertext length in bytes
@PTLen: The plaintext length in bytes
@UsesAuth: 1 if using the optional authenticator parameter, 0 otherwise
@HASHLEN: 20 bytes for SHA1 (The authenticator parameter adds a SHA1 hash to the plaintext)
@BLOCK: The length in bytes per block. 8 for the DES family, 16 for AES
Now let’s explain every part of the formula to make it easier to understand:
FLOOR( (
8 Encrypted internal header
+@PTLen Plaintext length
+ (@UsesAuth * @HASHLEN) If the authenticator parameter is used, the plaintext will include a hash derived from it.
) / @BLOCK) + 1 ) * @BLOCK This portion of the formula as a whole predicts the length of the ciphertext, including padding. Note: If the plaintext length fits exactly in one block, there will be an additional block of padding. This part of the formula reflects this behavior
+ 16 Key GUID (not encrypted). This information is used during decryption to identify the key that needs to be used
+ @BLOCK Initialization vector (IV)
+ 4 Internal header that describes the BLOB version number
Once we have reviewed this formula, we can discuss the limitations on the encryption builtins: The output (ciphertext) is limited to up to 8000 bytes; what does this mean? This means that the plaintext limit is a little bit below the 8000 bytes (i.e. for AES encryption, using the authenticator parameter it should be around 7920 bytes ). If you try to encrypt a larger plaintext, the builtin will fail (return null) as it won’t be able to fit all the output in the available buffer.
For certificates, the way to calculate the maximum length of plaintext we can encrypt, as well as the ciphertext length, is easier because it is based on key modulus.
@Cipher_len = key_modulus (regardless of the plaintext length)
@Max_Plaintext_len = key_modulus – 11
By default, the self-signed certificates created by SQL Server 2005 have a 1024 bit modulus = 128 bytes, therefore the cipher texts are always 128 bytes and the maximum plaintext that can be encrypted is 117 bytes. The same formula applies for any certificate you may want to export into the system (the limit is 3456 bits for the private keys modulus).
For more information on this one, I recommend you to go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptencrypt.asp and look at the remarks section for CryptEncrypt.
Should I always use this formula to calculate the size of my encrypted columns?
I strongly recommend against considering the result of the formula as a hardcoded length for your encrypted columns. I would suggest to estimate what would be the expected maximum ciphertext length (either by creating a sample using EncryptByKey directly on your system or by using the formula) and add a little bit of extra space. My personal suggestion would be to reserve at least 1 extra block (8 bytes for DES family, 16 bytes for AES), but it would really depend on the specific scenario.
The formula I shared here is a way to predict the length of the plaintext, but there is no guarantee that this formula will work for future algorithms or even for the existing algorithms in future versions of the product. As you have noticed, SQL Server encryption includes a few fields that are not part of the ciphertext itself, and in future versions of the product there may be a need to include new fields.
But I really need to encrypt BLOBs larger than 8000 bytes! Is there anything I can do?
Using the native solution, there is nothing you can do. Your application will need to slice the input before encrypting it.
I have created a small sample code that creates a UDF (user defined function) that is essentially a wrapper around EncryptByKey. It basically partitions the data in different pieces, encrypts them, and then pastes them together in a single BLOB that can be decrypted by its counterpart wrapper UDF for DecryptByKey. I created two different set of UDFs, one for symmetric keys and one for certificates.
The main idea behind this demo is to split the BLOB we want to encrypt in smaller segments that we can encrypt and then concatenate their ciphertexts in such a way that we can recover the individual segments, decrypt them, and paste them again in a single LOB (the original plaintext).
Demo
/***********************************************************************
* This posting is provided "AS IS" with no warranties,
* and confers no rights.
*
* Authors: Raul Garcia,
* Tanmoy Dutta,
* Pankaj Kamat,
* Laurentiu Cristofor
* Date: 11/07/2005
* Description:
*
* Create a scalar functions that allow encryption and decryption
* of large objects (> 8000 bytes).
*
* (c) 2005 Microsoft Corporation. All rights reserved.
*
**********************************************************************/
CREATE DATABASE demo_LobEncryption
go
USE demo_LobEncryption
go
--------------------------------------------------------------------
-- Create a new Encryption function – EncryptLob.
--
-- This is a wrapper around the EncryptByKey builtin
-- In order to keep this demo simple, it doesn't support
-- all the options of that builtin
--
-- PARAMETERS
-- @key_guid: Symmetric key GUID,
-- equivalent to EncryptByKey’s first argument.
-- This key needs to be already opened
-- before calling the function
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return the encrypted data
-- as described in NOTES
-- NULL is returned if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then will be concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.EncryptLob(
@key_guid uniqueidentifier,
@lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)
-- Limit for the PT block
SET @PtLimit = 7800
SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null
---- Simple validation of the input parameters
if(
@lob is not null
AND encryptbykey( @key_guid, 0x00 ) is not null
-- Can we encrypt with the key specified by the GUID
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit
-- Encrypt the PT block
SET @Aux = encryptbykey(@key_guid, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null
-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux
-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data
-- with the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END
END
return @Cipher
END
go
-------------------------------------------------------------------
-- Create a new Decryption function - DecryptLob.
--
-- This is a wrapper around the decryptByKey builtin.
-- It is the counterpart of the EncryptLob function.
--
-- PARAMETERS
-- @Cipher: A ciphertext that was generated by
-- EncryptLob.
-- The decryption key needs to be already
-- opened before calling the function
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
-- NULL is returned if there is any error
--
-- NOTES:
-- The ciphertext will be split in blocks that can be
-- decrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLob( @Cipher varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)
SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null
-- No op for null or empty data
if( @Cipher is not null
AND @Total > 0
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Read the next cipher block length
SELECT @AuxLen = substring( @Cipher, @CurPos, 2 )
SET @CurPos = @CurPos + 2
-- Length field validation.
-- Any unexpected length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null
-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen
-- If there is any discrepancy,
-- it is either a data truncation error
-- (cipher was truncated)
-- or a data corruption error.
-- We will fail the whole operation.
if( datalength( @Aux ) <> @AuxLen )
return null
-- Decrypt the current cipher block
SET @Aux = decryptbykey( @Aux )
-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null
-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END
return @LobPt
END
go
---------------------- Test
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go
OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go
--------------- Quick Demo
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 15 -- PT will be a little bit less than 2MB
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLob( key_guid('key1'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLob( @y ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create a new Encryption function - EncryptLobByCert.
--
-- This is a wrapper around EncryptByCert builtin
-- In order to keep this demo simple, it doesn't support
-- all the options EncryptByCert supports.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return a byte stream that
-- with the encrypted data. The byte stream
-- format is described in NOTES
-- NULL if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
-- Remember that asymmetric key encryption/decryption is orders
-- of magnitude more expensive than symmetric key
-- encryption/decryption.
-- We recommend avoiding the use of asymmetric key encryption for
-- large amounts of data, but these functions may be used to
-- protect data < 1000 bytes that are larger than what
-- the SQL builtins are currently supporting
-- (for example, data larger than 117 bytes)
CREATE FUNCTION dbo.EncryptLobByCert( @cert_id int, @lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)
SET @Aux = encryptbycert( @cert_id, 0x00 )
if( @Aux is null ) -- Return null if we cannot encrypt by the specified cert
return null
-- Calculate the limit for the PT block
SET @PtLimit = datalength( @Aux ) - 11
SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null
---- Simple validation of the input parameters
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit
-- Encrypt the PT block
SET @Aux = encryptbycert( @cert_id, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null
-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux
-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data with
-- the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END
return @Cipher
END
go
---------------------------------------------------------------------
-- Create a new Decryption function - DecryptLobByCert.
--
-- This is a wrapper around DecryptByCert builtin.
-- It is the counterpart of EncryptLobByCert function.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @Cipher: A ciphertext that was generated by
-- EncryptLobByCert.
-- @Password: Password for the private key.
-- If encrypted by DBMK, you should use null
-- for this parameter.
--
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
--
-- NOTES
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLobByCert(
@cert_id int,
@Cipher varbinary(max),
@Password nvarchar(4000) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)
SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null
-- No op for null or empty data
if( @Cipher is not null
AND @Total > 0
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Read the next cipher block length
SELECT @AuxLen = substring( @Cipher, @CurPos, 2 )
SET @CurPos = @CurPos + 2
-- Length field validation. Any unexpected
-- length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null
-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen
-- If there is any discrepancy,
-- it is either a data truncation error
-- (cipher was truncated)
-- or a data corruption error.
-- We will fail the whole operation.
if( datalength( @Aux ) <> @AuxLen )
return null
-- Decrypt the current cipher block
SET @Aux = decryptbycert( @cert_id, @Aux, @Password )
-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null
-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END
return @LobPt
END
go
------------ Test
CREATE CERTIFICATE certTest ENCRYPTION BY PASSWORD = 'c3R+ p@zz\/\/0Rd!' WITH SUBJECT = 'Demo - LOB encryption'
go
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little bit more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest'), @y, N'c3R+ p@zz\/\/0Rd!' ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
------ Test2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D8|\/||< p455\/\/oRx!'
go
CREATE CERTIFICATE certTest2 WITH SUBJECT = 'Demo - LOB encryption DBMK protected'
go
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest2'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
-- Use null for the password parameter
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest2'), @y, null ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
----------------------- CLEANUP
use master
go
DROP DATABASE demo_LobEncryption
go
----------------------- END OF DEMO -----------------------
What is the limit on the data length that can be encrypted?
The answer to this question is a little bit more complex than a simple number: the limit on the input (plaintext) really depends on the output (ciphertext), and it depends on the key you are using and on whether you are using optional features of the encryption builtins.
You can use the following formula to predict the length of the ciphertext that will result from calling EncryptByKey based on the plaintext length, the key algorithm used and whether the optional authenticator parameter is being used or not. Note: this formula is valid only for the DES and AES key families, it is not valid for RC4, and I would recommend to not even use RC4 at all)
@CipherLen = FLOOR( (8 +@PTLen + (@UsesAuth * @HASHLEN) ) / @BLOCK) + 1 ) * @BLOCK + 16 + @BLOCK + 4
Where:
@CipherLen: The ciphertext length in bytes
@PTLen: The plaintext length in bytes
@UsesAuth: 1 if using the optional authenticator parameter, 0 otherwise
@HASHLEN: 20 bytes for SHA1 (The authenticator parameter adds a SHA1 hash to the plaintext)
@BLOCK: The length in bytes per block. 8 for the DES family, 16 for AES
Now let’s explain every part of the formula to make it easier to understand:
FLOOR( (
8 Encrypted internal header
+@PTLen Plaintext length
+ (@UsesAuth * @HASHLEN) If the authenticator parameter is used, the plaintext will include a hash derived from it.
) / @BLOCK) + 1 ) * @BLOCK This portion of the formula as a whole predicts the length of the ciphertext, including padding. Note: If the plaintext length fits exactly in one block, there will be an additional block of padding. This part of the formula reflects this behavior
+ 16 Key GUID (not encrypted). This information is used during decryption to identify the key that needs to be used
+ @BLOCK Initialization vector (IV)
+ 4 Internal header that describes the BLOB version number
Once we have reviewed this formula, we can discuss the limitations on the encryption builtins: The output (ciphertext) is limited to up to 8000 bytes; what does this mean? This means that the plaintext limit is a little bit below the 8000 bytes (i.e. for AES encryption, using the authenticator parameter it should be around 7920 bytes ). If you try to encrypt a larger plaintext, the builtin will fail (return null) as it won’t be able to fit all the output in the available buffer.
For certificates, the way to calculate the maximum length of plaintext we can encrypt, as well as the ciphertext length, is easier because it is based on key modulus.
@Cipher_len = key_modulus (regardless of the plaintext length)
@Max_Plaintext_len = key_modulus – 11
By default, the self-signed certificates created by SQL Server 2005 have a 1024 bit modulus = 128 bytes, therefore the cipher texts are always 128 bytes and the maximum plaintext that can be encrypted is 117 bytes. The same formula applies for any certificate you may want to export into the system (the limit is 3456 bits for the private keys modulus).
For more information on this one, I recommend you to go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptencrypt.asp and look at the remarks section for CryptEncrypt.
Should I always use this formula to calculate the size of my encrypted columns?
I strongly recommend against considering the result of the formula as a hardcoded length for your encrypted columns. I would suggest to estimate what would be the expected maximum ciphertext length (either by creating a sample using EncryptByKey directly on your system or by using the formula) and add a little bit of extra space. My personal suggestion would be to reserve at least 1 extra block (8 bytes for DES family, 16 bytes for AES), but it would really depend on the specific scenario.
The formula I shared here is a way to predict the length of the plaintext, but there is no guarantee that this formula will work for future algorithms or even for the existing algorithms in future versions of the product. As you have noticed, SQL Server encryption includes a few fields that are not part of the ciphertext itself, and in future versions of the product there may be a need to include new fields.
But I really need to encrypt BLOBs larger than 8000 bytes! Is there anything I can do?
Using the native solution, there is nothing you can do. Your application will need to slice the input before encrypting it.
I have created a small sample code that creates a UDF (user defined function) that is essentially a wrapper around EncryptByKey. It basically partitions the data in different pieces, encrypts them, and then pastes them together in a single BLOB that can be decrypted by its counterpart wrapper UDF for DecryptByKey. I created two different set of UDFs, one for symmetric keys and one for certificates.
The main idea behind this demo is to split the BLOB we want to encrypt in smaller segments that we can encrypt and then concatenate their ciphertexts in such a way that we can recover the individual segments, decrypt them, and paste them again in a single LOB (the original plaintext).
Demo
/***********************************************************************
* This posting is provided "AS IS" with no warranties,
* and confers no rights.
*
* Authors: Raul Garcia,
* Tanmoy Dutta,
* Pankaj Kamat,
* Laurentiu Cristofor
* Date: 11/07/2005
* Description:
*
* Create a scalar functions that allow encryption and decryption
* of large objects (> 8000 bytes).
*
* (c) 2005 Microsoft Corporation. All rights reserved.
*
**********************************************************************/
CREATE DATABASE demo_LobEncryption
go
USE demo_LobEncryption
go
--------------------------------------------------------------------
-- Create a new Encryption function – EncryptLob.
--
-- This is a wrapper around the EncryptByKey builtin
-- In order to keep this demo simple, it doesn't support
-- all the options of that builtin
--
-- PARAMETERS
-- @key_guid: Symmetric key GUID,
-- equivalent to EncryptByKey’s first argument.
-- This key needs to be already opened
-- before calling the function
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return the encrypted data
-- as described in NOTES
-- NULL is returned if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then will be concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.EncryptLob(
@key_guid uniqueidentifier,
@lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)
-- Limit for the PT block
SET @PtLimit = 7800
SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null
---- Simple validation of the input parameters
if(
@lob is not null
AND encryptbykey( @key_guid, 0x00 ) is not null
-- Can we encrypt with the key specified by the GUID
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit
-- Encrypt the PT block
SET @Aux = encryptbykey(@key_guid, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null
-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux
-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data
-- with the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END
END
return @Cipher
END
go
-------------------------------------------------------------------
-- Create a new Decryption function - DecryptLob.
--
-- This is a wrapper around the decryptByKey builtin.
-- It is the counterpart of the EncryptLob function.
--
-- PARAMETERS
-- @Cipher: A ciphertext that was generated by
-- EncryptLob.
-- The decryption key needs to be already
-- opened before calling the function
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
-- NULL is returned if there is any error
--
-- NOTES:
-- The ciphertext will be split in blocks that can be
-- decrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLob( @Cipher varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)
SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null
-- No op for null or empty data
if( @Cipher is not null
AND @Total > 0
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Read the next cipher block length
SELECT @AuxLen = substring( @Cipher, @CurPos, 2 )
SET @CurPos = @CurPos + 2
-- Length field validation.
-- Any unexpected length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null
-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen
-- If there is any discrepancy,
-- it is either a data truncation error
-- (cipher was truncated)
-- or a data corruption error.
-- We will fail the whole operation.
if( datalength( @Aux ) <> @AuxLen )
return null
-- Decrypt the current cipher block
SET @Aux = decryptbykey( @Aux )
-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null
-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END
return @LobPt
END
go
---------------------- Test
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go
OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go
--------------- Quick Demo
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 15 -- PT will be a little bit less than 2MB
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLob( key_guid('key1'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLob( @y ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create a new Encryption function - EncryptLobByCert.
--
-- This is a wrapper around EncryptByCert builtin
-- In order to keep this demo simple, it doesn't support
-- all the options EncryptByCert supports.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return a byte stream that
-- with the encrypted data. The byte stream
-- format is described in NOTES
-- NULL if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
-- Remember that asymmetric key encryption/decryption is orders
-- of magnitude more expensive than symmetric key
-- encryption/decryption.
-- We recommend avoiding the use of asymmetric key encryption for
-- large amounts of data, but these functions may be used to
-- protect data < 1000 bytes that are larger than what
-- the SQL builtins are currently supporting
-- (for example, data larger than 117 bytes)
CREATE FUNCTION dbo.EncryptLobByCert( @cert_id int, @lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)
SET @Aux = encryptbycert( @cert_id, 0x00 )
if( @Aux is null ) -- Return null if we cannot encrypt by the specified cert
return null
-- Calculate the limit for the PT block
SET @PtLimit = datalength( @Aux ) - 11
SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null
---- Simple validation of the input parameters
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit
-- Encrypt the PT block
SET @Aux = encryptbycert( @cert_id, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null
-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux
-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data with
-- the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END
return @Cipher
END
go
---------------------------------------------------------------------
-- Create a new Decryption function - DecryptLobByCert.
--
-- This is a wrapper around DecryptByCert builtin.
-- It is the counterpart of EncryptLobByCert function.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @Cipher: A ciphertext that was generated by
-- EncryptLobByCert.
-- @Password: Password for the private key.
-- If encrypted by DBMK, you should use null
-- for this parameter.
--
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
--
-- NOTES
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLobByCert(
@cert_id int,
@Cipher varbinary(max),
@Password nvarchar(4000) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)
SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null
-- No op for null or empty data
if( @Cipher is not null
AND @Total > 0
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Read the next cipher block length
SELECT @AuxLen = substring( @Cipher, @CurPos, 2 )
SET @CurPos = @CurPos + 2
-- Length field validation. Any unexpected
-- length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null
-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen
-- If there is any discrepancy,
-- it is either a data truncation error
-- (cipher was truncated)
-- or a data corruption error.
-- We will fail the whole operation.
if( datalength( @Aux ) <> @AuxLen )
return null
-- Decrypt the current cipher block
SET @Aux = decryptbycert( @cert_id, @Aux, @Password )
-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null
-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END
return @LobPt
END
go
------------ Test
CREATE CERTIFICATE certTest ENCRYPTION BY PASSWORD = 'c3R+ p@zz\/\/0Rd!' WITH SUBJECT = 'Demo - LOB encryption'
go
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little bit more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest'), @y, N'c3R+ p@zz\/\/0Rd!' ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
------ Test2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D8|\/||< p455\/\/oRx!'
go
CREATE CERTIFICATE certTest2 WITH SUBJECT = 'Demo - LOB encryption DBMK protected'
go
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest2'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
-- Use null for the password parameter
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest2'), @y, null ))
if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go
----------------------- CLEANUP
use master
go
DROP DATABASE demo_LobEncryption
go
----------------------- END OF DEMO -----------------------
Saturday, August 18, 2007
Uniqueidentifiers
Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.
First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:
Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.
To get a GUID in SQL Server (7.0+), you call the NEWID() function.
The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.
Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')
So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. You can get it before the insert, however, by using the above code.
The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. GUIDs are also used heavily by SQL Server replication to keep track of rows when they're spread out among multiple SQL Servers.
The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes).
Not only that, but they're just plain hard to read. Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.
First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:
Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.
To get a GUID in SQL Server (7.0+), you call the NEWID() function.
The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.
Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')
So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. You can get it before the insert, however, by using the above code.
The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. GUIDs are also used heavily by SQL Server replication to keep track of rows when they're spread out among multiple SQL Servers.
The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes).
Not only that, but they're just plain hard to read. Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.
Subscribe to:
Posts (Atom)
