Most publications that discuss encryption, including my own book, demonstrate the use of the EncryptByKey function as follows:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
   DECRYPTION BY CERTIFICATE SampleCertificate
   WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Perform encryption on plain text value and capture in a variable
DECLARE @EncVal varbinary(max);
SET @EncVal = EncryptByKey(Key_GUID('SampleSymKey'), 'TestValue');

-- Use the encrypted value that is captured in the variable
INSERT INTO dbo.SampleEncTable (PlainVal, EncVal)
VALUES ('TestValue', @EncVal);

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO

Execute a query that utilizes the DecryptByKeyAutoCert and you can see that the EncryptByKey function performs very well:

SELECT
    CONVERT(varchar,DECRYPTBYKEYAUTOCERT(
                Cert_ID('SampleCertificate'),
                N'MyStr0ngP@ssw0rD',
                EncVal)
     ) as DecryptedVal
FROM
    dbo.SampleEncTable;
GO

The challenge with these examples are that, while they effectively demonstrate the functionality of these cryptographic functions, they represent only the encryption and decryption of a singular value.

If you were faced with a mass encryption effort, such as the conversion of a legacy database containing plain text sensitive data to a new database in which the converted sensitive data is encrypted, the utilization of a cursor to perform the above sample for each record is very tempting and excruciatingly expensive. Below is a sample of such an attempt:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
   DECRYPTION BY CERTIFICATE SampleCertificate
   WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Creating a cursor (oooh, you desperate coder, you. ;D)
DECLARE @LegVal varchar(9);
DECLARE EncCur CURSOR
    FOR SELECT
            LegacyVal
        FROM
            dbo.SampleLegacyTable;

-- Open Cursor (screeeeeeech)
OPEN EncCur;
FETCH NEXT FROM EncCur INTO @LegVal;

-- Loop through cursor (mommy, I want off this ride!)
WHILE @@FETCH_STATUS = 0
BEGIN       

    -- Perform encryption on plain text value and capture in a variable
    DECLARE @EncVal varbinary(max);
    SET @EncVal = EncryptByKey(Key_GUID('SampleSymKey'), @LegVal);

    -- Use the encrypted value that is captured in the variable
    INSERT INTO dbo.SampleEncTable (PlainVal, EncVal)
    VALUES ('TestValue', @EncVal);       

    FETCH NEXT FROM EncCur INTO @LegVal;
END

-- Close Cursor (bang!)
CLOSE EncCur;
DEALLOCATE EncCur;   

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO   

Cursors are a procedural programming concept which deals with data in a row-by-row method. One row is retrieved and processed at a time. T-SQL is a set based, or declarative, programming language and works best when set based commands are used to process data. An example of using the EncryptByKey function in a set based fashion, and without the dreaded cursor, the statement would appear something like the following:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
   DECRYPTION BY CERTIFICATE SampleCertificate
   WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Encrypt legacy data as a set (zing!)
INSERT INTO dbo.SampleEncTable
    (
        PlainVal,
        EncVal
    )
    SELECT
        LegacyVal,
        EncryptByKey(Key_GUID('SampleSymKey'), LegacyVal)
    FROM
        dbo.SampleLegacyTable;

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO   

With the use of the above example, your legacy data has found its new home in a highly efficient manner. The sensitive data has been encrypted and is ready to be disclosed is a safe manner.