Search results matching tags 'T-SQL' and 'SQL'

Bit-Twiddling in SQL

Mike C — Sun, 04 Apr 2010 03:40:00 GMT

Someone posted a question to the SQL Server forum the other day asking how to count runs of zero bits in an integer using SQL. Basically the poster wanted to know how to efficiently determine the longest contiguous string of zero-bits (known as a run of bits) in any given 32-bit integer. Here are a couple of examples to demonstrate the idea:

Decimal = Binary = Zero Run
999,999,999 decimal = 00111011 10011010 11001001 11111111 binary = 2 contiguous zero bits
666,666,666 decimal = 00100111 10111100 10000110 10101010 binary = 4 contiguous zero bits

My first reaction was that SQL is not my first choice of a go-to language for bit twiddling hacks. These types of calculations are generally most efficient in C-style compiled procedural languages with plenty of bit manipulation instructions that map almost directly one-for-one to low-level machine language instructions. In all fairness, SQL does have some bit-level operators (&, |, etc. operators), but the performance isn’t as optimized as a language like C#.

At any rate, a few different ideas were tossed around, like the simplistic loop-and-count procedural method. Using this method you just keep a running total of the longest zero-bit run and keep looping over the bits, adjusting your running total, comparing to your largest run of zero bits, and shifting your integer one bit right each time. But this being SQL, I decided to attack the problem from a set-based perspective. To start with I built a 1,000,000 row table to hold random integers:

CREATE TABLE TempNum
(
    Num BIGINT NOT NULL
);
GO

WITH GenerateRandom
AS
(
    SELECT 1 AS n,
        ABS(CONVERT(BIGINT, CONVERT(BINARY, CHECKSUM(NEWID()))) % 4294967296) AS Random

    UNION ALL

    SELECT n + 1,
        ABS(CONVERT(BIGINT, CONVERT(BINARY, CHECKSUM(NEWID()))) % 4294967296)
    FROM GenerateRandom
    WHERE n < 1000000
)
INSERT INTO TempNum
(
    Num
)
SELECT Random
FROM GenerateRandom
OPTION (MAXRECURSION 0);
GO

Note that I used SQL Server’s BIGINT data type (64-bit integer) since I wanted to deal only in unsigned 32-bit integers. For my initial crack at a solution I split the 32-bit integer up into individual bits and treated the whole thing like a classic Gaps and Islands problem. Essentially the 1 bits are islands, the 0's are gaps, and the length of the longest gap is the correct answer.

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

WITH Powers
AS
(
    SELECT 0 AS id,
        CAST(1 AS BIGINT) AS pwr

    UNION ALL

    SELECT p.id + 1,
        POWER(CAST(2 AS BIGINT), p.id + 1)
    FROM Powers p
    WHERE p.id < 33
),
Islands
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY tn.Num ORDER BY id) AS RowNum,
        tn.Num,
        p.id,
        p.pwr
    FROM Powers p
    CROSS JOIN TempNum tn
    WHERE p.pwr & ((tn.Num * 2 ) | CAST(8589934593 AS BIGINT)) <> 0
)
SELECT c.Num AS Num,
    MAX(n.id - c.id - 1) AS ZeroBitRun
INTO #Temp
FROM Islands AS c
INNER JOIN Islands AS n
    ON n.RowNum = c.RowNum + 1
        AND n.Num = c.Num
WHERE n.id - c.id > 1
GROUP BY c.Num,
    n.Num;
GO

One interesting aside on this solution - to ensure that leading and trailing zeroes were counted for every 32-bit number I had to shift the number left one bit (multiply by 2) and do a bitwise OR (| operator) with 8589934593, setting both the lowest bit (bit 0) and the highest bit (bit 33) to 1. Basically this means you’re now dealing with a 34-bit integer, with the highest and lowest bits counted as islands. This ensures that for the 32 bits in between leading and trailing gaps are correctly identified. On my PC this query took an average of just over 360 seconds (6 minutes) to identify the largest run of zero bits in 1,000,000 random numbers. Another solution posted to the group used logarithms combined with bit-shifting in a recursive CTE. This one completed 1,000,000 iterations in about 17.5 minutes.

I couldn't help but think that when you get down to it, a solution to this problem should play to SQL’s strengths. I decided that what I really need for an efficient SQL solution to this problem is a lookup table. Granted, a lookup table of 4+ billion rows (one row for each and every 32 bit number) would take a long time to build and probably wouldn't lend itself to IO efficiencies in SQL Server. So I opted for a scaled down version and built a lookup table of 65,536 rows with one row representing every possible 16 bit number. There are a lot of clever ways to grab bit-level pattern information (Google "de Bruijn sequence", for instance), but to be honest this lookup table is a one-time build and static population so I decided to keep it simple.

CREATE TABLE #Nybbles
(
    Num int primary key not null,
    String varchar(4) not null,
    Leading tinyint not null,
    Trailing tinyint not null
);
GO

INSERT INTO #Nybbles
(
    Num,
    String,
    Leading,
    Trailing
)
VALUES (0, '0000', 4, 4), (1, '0001', 3, 0), (2, '0010', 2, 1), (3, '0011', 2, 0),
    (4, '0100', 1, 2), (5, '0101', 1, 0), (6, '0110', 1, 1), (7, '0111', 1, 0),
    (8, '1000', 0, 3), (9, '1001', 0, 0), (10, '1010', 0, 1), (11, '1011', 0, 0),
    (12, '1100', 0, 2), (13, '1101', 0, 0), (14, '1110', 0, 1), (15, '1111', 0, 0);
GO

CREATE TABLE #BitMask
(
    Length tinyint,
    Mask varchar(16)
);
GO

WITH GetMasks
AS
(
    SELECT 0 AS Length,
        CAST('' AS varchar(16)) AS Mask

    UNION ALL

    SELECT Length + 1,
        CAST(REPLICATE('0', Length + 1) AS varchar(16))
    FROM GetMasks
    WHERE Length < 16
)
INSERT INTO #BitMask
(
    Length,
    Mask
)
SELECT Length,
    Mask
FROM GetMasks;
GO

CREATE TABLE BitPattern
(
    Num int not null primary key,
    Trailing tinyint,
    Leading tinyint,
    Seq tinyint
);
GO

WITH GetBin
AS
(
    SELECT n1.Num * 4096 + n2.Num * 256 + n3.Num * 16 + n4.Num AS Num,
        n1.String + n2.String + n3.String + n4.String AS String
    FROM #Nybbles n1
    CROSS JOIN #Nybbles n2
    CROSS JOIN #Nybbles n3
    CROSS JOIN #Nybbles n4
)
INSERT INTO BitPattern
(
    Num,
    Trailing,
    Leading,
    Seq
)
SELECT g.Num,
    (
        SELECT MAX(b1.Length)
        FROM #BitMask b1
        WHERE RIGHT(g.String, b1.Length) = b1.Mask
    ) AS Trailing,
    (
        SELECT MAX(b2.Length)
        FROM #BitMask b2
        WHERE LEFT(g.String, b2.Length) = b2.Mask
    ) AS Leading,
    COALESCE
    (
        (
            SELECT MAX(b3.Length)
            FROM #BitMask b3
            WHERE CHARINDEX(b3.Mask, g.String) > 0
        ),
    0) AS Seq
FROM GetBin g
ORDER BY Num;
GO

DROP TABLE #BitMask;
DROP TABLE #Nybbles;
GO

The first part of this script puts all 16 combinations of 4-bit nybbles (nybble = half a byte) and their equivalent binary formatted strings (0 = '0000', 14 = '1110') into a temp table called #Nybbles.

There’s also a #BitMask temp table with bitmasks representing zero-bit runs. The bitmasks are just strings of consecutive '0' characters of the necessary length (length 1 = '0', length 5 = '00000').

The BitPatterns table is the actual 16-bit number lookup table. This table is populated by combining every 16-bit combination of nybbles from the #Nybbles temp table. This table has 4 columns:

Num is the 16-bit number
Trailing is the number of zero bits trailing (on the right-hand side) in the number
Leading is the number of zero bits leading (on the left-hand side) in the number
Seq is the longest sequence of zero bits within the number

The total run time to build this lookup table was around 30 seconds on my computer. Keep in mind that’s a one-time cost since you never have to build (or modify) the table again.

With the information in this lookup table the query that locates the longest run of zero bits in any given 32-bit number is relatively simple:

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

WITH CTE

(

SELECT tn.Num,

(

SELECT

CASE WHEN l.Seq > h.Seq THEN

(

CASE WHEN l.Seq > h.Trailing + l.Leading THEN l.Seq

ELSE h.Trailing + l.Leading

END

)

ELSE

(

CASE WHEN h.Seq > h.Trailing + l.Leading THEN h.Seq

ELSE h.Trailing + l.Leading

END

)

END

) AS Seq,

CASE WHEN l.Trailing = 16 THEN h.Trailing + l.Trailing

ELSE l.Trailing

END AS Trailing,

CASE WHEN h.Leading = 16 THEN h.Leading + l.Leading

ELSE h.Leading

END AS Leading

FROM TempNum tn WITH (NOLOCK)

INNER JOIN BitPattern l WITH (NOLOCK)

ON l.Num = (tn.Num & 65535)

INNER JOIN BitPattern h WITH (NOLOCK)

ON h.Num = (tn.Num / 65536)

)

SELECT Num,

Seq,

Leading,

Trailing

INTO #Temp

FROM CTE;

Basically you join the BitPattern lookup table on the high 16 bits and again on the low 16 bits. The first CASE expression in the Seq subquery performs a 3-way maximum calculation. It returns the largest of:

the low word zero-bit run (l.Seq),
the high word zero-bit run (h.Seq), or
the count of high word trailing zero-bits + the count of low word leading zero-bits (h.Trailing + l.Leading)

The other two CASE expressions return the total number of trailing and leading zero-bits in the 32-bit number. The CASE expression is needed to handle the case when the high or low word is all zero-bits.

This particular solution took an average of about 6 seconds to calculate the longest zero-bit run for 1,000,000 numbers.

To my earlier point, I created a C# solution (which itself could have been optimized) that performed the exact same calculation for 1,000,000 random 32 bit integers in 2 seconds flat. So I guess there are two main points here: (1) Make sure you choose the right tool/language for the job, and (2) Whatever tool/language you choose try to play to its strengths.

Calculating Holidays in SQL

Mike C — Thu, 25 Feb 2010 21:51:00 GMT

Ask about nearly any kind of SQL-based date calculation in the newsgroups, and you'll likely get responses that include use of an auxiliary calendar table. It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server. For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals. I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.

What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table. When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays. There are two types of holidays that I'll address:

Fixed holidays that fall on the same day every year
Floating holidays for which the date can change from year to year

Fixed holidays include holidays that always fall on the same day each year. Some examples of fixed holidays include Christmas (always December 25 in the West), Canada Day (always July 1), U.S. Independence Day (always July 4). These are fairly easy to detect and set in your auxiliary calendar table. Just look for the static month + day combinations in your table and update it accordingly.

Floating holidays are far more interesting and difficult to calculate. Take Easter. Easter actually has two definitions. The popular definition is:

"Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"

This is not the exact ecclesiastical definition though. The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined through precalculated lookup tables. The ecclesiastical rules are:

Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox
This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)
The vernal equinox is fixed as March 21

I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details can be found on Wikipedia if you're interested. The main point is that Easter calculation is fairly complex. The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime). Notice the large number of calculations required to make this function work:

CREATE FUNCTION dbo.udf_CalculateEaster
(
@Year INT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Date DATETIME,
    @c INT,
    @n INT,
    @i INT,
    @k INT,
    @j INT,
    @l INT,
    @m INT,
    @d INT;

    SELECT @n = @Year - 19 * (@Year / 19),
    @c = @Year / 100,
    @k = (@c - 17) / 25,
    @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,
    @i = @i - 30 * (@i / 30),
    @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),
    @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,
    @j = @j - 7 * (@j / 7),
    @l = @i - @j,
    @m = 3 + (@l + 40) / 44,
    @d = @l + 28 - 31 * (@m / 4),
    @Date = CAST
    (
      CAST(@Year AS CHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @m AS VARCHAR(2)
        ), 2
      ) +
      RIGHT
      (
        '0' +CAST
        (
          @d AS VARCHAR(2)
        ), 2
      ) AS DATETIME
    );
    RETURN @Date;
END;
GO

If you want the date for Easter 2010, just run a query like the following:

SELECT dbo.udf_CalculateEaster (2010);
GO

The result is 2010-04-04.

Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (U.S. Thanksgiving), "the 3rd Monday in January" (U.S. Martin Luther King Day), or even "the *last* Monday in May" (U.S. Memorial Day). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).

To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:

@n = integer that represents which instance of the weekday you're looking for. This would be the 4 in "4th Thursday in November".
@weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.) I used character abbreviations instead of numbers just to make it a little easier.
@year = integer year for which to calculate
@month = integer month for which to calculate (1 = January, 2 = February, etc.)

As you can see below, the Nth weekday calculation is pretty simple.

CREATE FUNCTION dbo.udf_nthWeekDay
(
@n       INT,
@weekDay CHAR(3),
@year    INT,
@month   INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @date    DATETIME,
    @dow         INT,
    @offset      INT,
    @wd          INT;

SELECT @wd = CASE @weekDay
      WHEN 'SUN' THEN 1
      WHEN 'MON' THEN 2
      WHEN 'TUE' THEN 3
      WHEN 'WED' THEN 4
      WHEN 'THU' THEN 5
      WHEN 'FRI' THEN 6
      WHEN 'SAT' THEN 7
    END,
    @date = CAST
    (
      CAST(@year AS VARCHAR(4)) +
      RIGHT
      (
        '0' + CAST
        (
          @month AS VARCHAR(2)
        ), 2
      ) +
      '01' AS DATETIME
    ),
    @dow = DATEPART(dw, @date),
    @offset = @wd - @dow,
    @date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date);
RETURN @date;
END;
GO

To get the date for Thanksgiving 2024 you could call the function like this:

SELECT dbo.udf_nthWeekDay
(
    4,
    'THU',
    2024,
    11
);

The result is 2024-11-28. Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier. You have no idea up front how many Mondays there are in any given May. As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4. You could try to figure out the number of Mondays in May of the given year, but that's way too much work.

There's one thing we know about the last Monday in May with absolute certainty: after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June. So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:

SELECT DATEADD
(
day,
-7,
dbo.udf_nthWeekDay
(
    1,
    'MON',
    2012,
    6
)
);

By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: 2012-05-28.

You can use the functions to calculate holidays for your auxiliary calendar table. I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.

Parent-Child Build Scripts with SQLCMD

Mike C — Sun, 10 Jan 2010 18:33:00 GMT

On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on. The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

/*
    Create All Items
*/


:r Database\Create.Database.sql
:r Schemas\Create.All.Schemas.sql
:r Types\Create.All.Types.sql
:r Tables\Create.All.Tables.sql

Each :r command kicks off the next level of child packages in turn.

SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE $(database);
GO

The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE Test;
GO

One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO"). If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

Mission Accomplished: NJSQL Saturday Event

Mike C — Sun, 25 Oct 2009 00:03:00 GMT

Thanks to all those who made the NJSQL Saturday event held in Iselin, NJ today a success!

Those lucky enough to attend were given a sneak preview of one of the best young presenters out there--MVP Jacob Sebastian--who will also be speaking at PASS. I have to hand it to Jacob: he gave one of the best presentations on a SQL Server topic I think I've seen yet. Between yours truly, Jacob Sebastian, and Robert Pearl, attendees learned best practices for defensive coding and error handling (XACT_ABORT anybody?), learned the concepts behind SQL Server's new spatial data types and saw how to make use them in conjunction with Web-based mapping apps (where's that A Train run again?), and learned a bit about auditing and the internals of the transaction log and CDC.

Thanks to NJSQL leader and event coordinator Melissa Demsak, attendees had plenty to eat and everyone got a chance to win several door prizes including t-shirts, books, and copies of Windows 7 Pro and Ultimate (Steve Ballmer's signature edition, no less)!

For those who asked for it, I'll be cleaning up my sample code and sample geospatial database to shrink it down and pass it along (with the other presentation materials) to Melissa for posting. Currently the sample database is about 99 MB in size, but it contains several tables that aren't really necessary for the demo code to run. Today I presented on the SQL Server geospatial "what" and "why"; at the NJSQL UG meeting in November I'll be diving into the "how". Check back at http://www.njsql.org for details.

Thanks again to all our sponsors and the NJSQL and NYC SQL Server UG for making this event happen!

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").