When a new version of SQL Server 2005 is released, it’s only natural that a subset of the new features gets a lot of exposure, while other features get little or no exposure. However, some of the overlooked features are very interesting and have important practical uses. For example, NEWSEQUENTIALID() is a new function in SQL Server 2005 that I almost overlooked until SQL Server MVP Steve Kass pointed it out to me. This new function lets you generate sequential globally unique identifiers (GUIDs).

The NEWSEQUENTIALID() function generates a 16-byte value of data type uniqueidentifier that's always greater than any GUID generated previously on the same computer by this function. Despite the function's name, the values that it generates are not really sequential; they're just greater than the previous value. This function provides an alternative to the NEWID() function, which generates random GUIDs and provides no guarantee that it will generate sequential values. GUIDs are used for different purposes, such as providing unique keys across tables, databases, instances, and servers. If you need to generate GUIDs that are always greater than any previously generated one on the same machine, use the NEWSEQUENTIALID() function to do so.

NEWSEQUENTIALID() Shortcomings
NEWSEQUENTIALID() returns values that include the MAC address of the NIC, if one is detected in the computer. Usage of the MAC address guarantees that the generated value will be globally unique (across servers). If you use the NEWSEQUENTIALID() function on a computer that doesn’t have a NIC installed, the values generated aren't guaranteed to be unique across servers, rather unique only for that computer. Also, the value generated by NEWSEQUENTIALID() isn't as secure as the one generated by NEWID() because a hacker can readily identify the NIC's MAC address within the generated value and anticipate what the next generated value will be. NEWSEQUENTIALID() invokes internally a Windows API called UuidCreateSequential(), which generates a value based on the NIC's MAC address and an internal hardware timestamp. If you want the gory details about UuidCreateSequential() and the structure of the values it generates, read the Microsoft article "UuidCreateSequential" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rpc/rpc/uuidcreatesequential.asp.

Using NEWSEQUENTIALID()
You can use the NEWSEQUENTIALID() function only in a DEFAULT constraint of a column in a table. You can't invoke it independently by using SELECT or SET statements. If you enter the statement

SELECT NEWSEQUENTIALID();

you'll get the error message: Msg 302, Level 16, State 0, Line 1. Instead, you can use the NEWSEQUENTIALID() function in a DEFAULT expression for a column of data type uniqueidentifier in a CREATE TABLE or ALTER TABLE statement. You can't combine this function with other operators to form a complex scalar expression.

To understand how to use this function, run the code in Listing 1 to create and populate the T1 table. The code creates a DEFAULT constraint on the column ID and invokes the NEWSEQUENTIALID() function. Notice the six INSERT statements and the delays between the INSERTs to allow gaps between the GUID values. It should take about one minute to run this code.

After running the code in Listing 1, query the table by using the following statement. You should get output similar to what Table 1 shows (but with different GUIDs and date time—DT—values):

SELECT * FROM dbo.T1 ORDER BY id;

Notice in the preceding statement that I want the data sorted by ID; observe in Table 1 that the sorted data reflects the entry order indicated by the DT column. You might find it hard to identify which bytes within the GUIDs are most significant (for comparison purposes) and which ones are least significant. The uniqueidentifier representation doesn't organize the bytes in order by their precedence.

Displaying Uniqueidentifier Values in Binary Format
You might want to keep the GUIDs in a binary string, organized from left to right in most-significant to least-significant order. This way, it will be easier for you to tell which value is greater than another. However, even the binary representation of uniqueidentifier values doesn't reflect the true comparison or sorting behavior. To see this type of behavior, run the following query to convert the ID values to binary strings and sort the rows by the binary representation of the IDs

SELECT CAST(id AS BINARY(16)) AS bin_id, dt, val
FROM dbo.T1
ORDER BY bin_id;

Now, look at the output in Table 2. If you sorted the values by their binary representation, the assignment order of the values isn't reflected in the output. Instead, you'll see that the rows that were inserted at 20:13 were sorted before rows that were inserted at 20:12. Note that when you attempt to run the preceding statement, you'll get different GUIDs and DT values than the ones that appear in Table 2 because your T1 table was populated with different values than mine by the NEWSEQUENTIALID and GETDATE functions. The converted binary values of the GUIDs don't appear in the correct sort order; this might or might not be apparent in your case. The different segments within a uniqueidentifier value aren't organized by comparison precedence from left to right.

To figure out comparison precedence of the different bytes, you can populate a table with 16 uniqueidentifier values, where all bytes are zeroed save one. In each value, set only one of the bytes to a nonzero value (e.g., 0x11). Run the code in Listing 2 to create a temporary table called #T and populate it with valid data. Next, run the following query to sort the values from #T table by ID in descending order, and you'll get the output that Table 3 shows.

SELECT id FROM #T ORDER BY id DESC;

You can learn about the comparison precedence of the different bytes in the formatted uniqueidentifier by looking at the order of the values shown in the results. To learn about the comparison precedence of the bytes in the binary representation of the values (which is different than in the formatted values), enter the following statement to convert the values to binary ones, and sort them by the binary strings in descending order:

SELECT id, CAST(id AS BINARY(16)) AS bin_id FROM #T ORDER BY id DESC;