SQL Server Magazine

Now, look at the output that Table 4 shows. You'll see that the binary representation of uniqueidentifier values isn’t the same when compared with the way those values are formatted for both presentation and entry purposes. If you prefer to store sequential GUIDs as binary strings that maintain correct comparison behavior, you need to rearrange the different bytes by their comparison precedence. To figure out comparison precedence of the bytes in the binary string, use the following query to generate the output that Table 5 shows:

WITH BinIDs AS
(
  SELECT CAST(id AS BINARY(16)) AS bin_id,
    ROW_NUMBER() OVER(ORDER BY id DESC) AS rownum
  FROM #T
)
SELECT rownum, CHARINDEX(0x11, bin_id) AS byte
FROM BinIDs
ORDER BY rownum;

The rownum column represents the sort precedence of the byte number specified in the byte column. For example, rownum 1, byte 11 means that the eleventh byte is highest in sort precedence, or in other words, is the most significant for sorting and comparison purposes. The common table expression (CTE) query calculates row numbers based on the order of ID DESC. Remember that each ID value has a different byte that isn't zeroed. The outer query uses CHARINDEX to identify the position of the nonzero byte within the binary string. By using the information obtained in the previous query, you can use code like the following to rearrange the bytes—from left to right, most significant to least significant—so that the binary strings get correct sorting and comparison behavior.

WITH BinIDs AS
(
  SELECT CAST(id AS BINARY(16)) AS bin_id, dt, val
  FROM dbo.T1
)
SELECT
    SUBSTRING(bin_id, 11, 6)
  + SUBSTRING(bin_id,  9, 2)
  + SUBSTRING(bin_id,  7, 2)
  + SUBSTRING(bin_id,  5, 2)
  + SUBSTRING(bin_id,  1, 4) AS good_bin_id,
  dt, val
FROM BinIDs
ORDER BY good_bin_id;

You can also add a computed column (bin_id) to table T1 that generates the binary representation of the IDs and even creates a UNIQUE constraint on the IDs, as the following code shows:

ALTER TABLE dbo.T1
  ADD bin_id AS 
      SUBSTRING(CAST(id AS BINARY(16)), 11, 6)
    + SUBSTRING(CAST(id AS BINARY(16)),  9, 2)
    + SUBSTRING(CAST(id AS BINARY(16)),  7, 2)
    + SUBSTRING(CAST(id AS BINARY(16)),  5, 2)
    + SUBSTRING(CAST(id AS BINARY(16)),  1, 4) UNIQUE;

Now you can rely on bin_id as a sequential GUID holder and expect the output in a format that's more readable. For example, the following query returns the rows from table T1 sorted by bin_id:

SELECT bin_id, dt, val FROM dbo.T1 ORDER BY bin_id;

Table 6 shows the query's results.

Generate NEWSEQUENTIALID() Before the INSERT Statement
As I mentioned previously, you can specify the NEWSEQUENTIALID() function only in a DEFAULT column constraint, meaning that you can't get a new value before you INSERT a row. If you use the NEWID() function, use a SET or SELECT statement to invoke the function and generate the value before the INSERT statement. If your application needs to generate a new key value before the actual INSERT statement—for example, to store the key on a different computer from the one where the database resides—you can still do so. You can create a sequencing table (call it NewSeqIDs) with an identity column as the primary key (call it new_identity) and a column with a DEFAULT constraint that invokes the NEWSEQUENTIALID() function (call it new_seqid). You can also create a column (call new_bin_seqid) that will compute the GUID as a binary string (that compares and sorts correctly) if you prefer the binary representation. Every time the application needs a new key, you insert a row in the sequence table and get the newly generated value by querying the row in which new_identity is equal to SCOPE_IDENTITY(). The value is then available to you before you actually use it in an INSERT statement against the target data table. As an aside, if you don’t need to enforce uniqueness of primary keys outside the scope of the instance, you can use the generated identity values as your global instancewide sequential keys. Identity values, which are stored as BIGINT, are more compact than GUIDs (8 bytes versus 16 bytes), but GUIDs have the advantage of being globally unique across instances and computers (assuming a NIC exists in a computer).

Use a stored procedure (call it usp_get_new_seqid) to invoke the INSERT statement against the NewSeqIDs table. The stored procedure returns three output parameters: the new identity value, new sequential ID, and binary form of the new sequential ID. Run the code in Listing 3 to create the NewSeqIDs table and the usp_get_new_seqid stored procedure.

Notice in Listing 3 that the stored procedure’s code defines a savepoint called S, issues the INSERT statement, then rolls back the procedure’s activity that took place since the savepoint (essentially the INSERT statement). The rollback to the savepoint helps to avoid the need to clear the sequence table from time to time to keep it small. Note that new identity values or new sequential IDs are still generated although you roll back the insertion of the row. Subsequent invocations of the stored procedure will generate higher values. Also, neither the identity resource nor the internal hardware timestamp resource used by NEWSEQUENTIALID() gets locked by an external transaction. Thus, this sequencing mechanism is asynchronous and won't block or cause queuing problems when multiple, different transactions invoke the stored procedure.

To test the stored procedure, run the code in Listing 4, and you'll get the output that appears in Table 7 with a newly generated identity value and sequential ID (both as UNIQUEIDENTIFIER and as BINARY). Note that I set NULL default values for the three parameters in the stored procedure. If want to see only one of the return values, you can provide a variable to that relevant output parameter; you don’t have to provide a variable for each parameter.

To demonstrate the application activity that generates new sequential IDs (say, the binary form) and then use these IDs in INSERT statements, run the code in Listing 5. The code creates a table called T2 and issues several INSERT statements after invoking the usp_get_new_seqid procedure to get binary sequential IDs. Next, run the following query to return the rows from table T2 sorted by the binary sequential IDs (ID column). Your output should look similar to the results listed in Table 8.

SELECT * FROM dbo.T2 ORDER BY id;

Powerful New Sequential IDs
For many applications, generating primary keys is a crucial, integral part of the system. Remember that how you design the primary keys and the mechanism you use to generate them can have many implications, such as integrity, concurrency, and performance. Use the NEWSEQUENTIALID() function to generate GUIDs that are always greater that any previously generated GUID on the same machine. In upcoming columns, I’ll continue to explore other similarly useful new T-SQL 2005 features that you might have overlooked.

End of Article

Prev. page     1 [2]     next page -->