Sort Order with the Uniqueidentifier Data Type

I just upgraded from SQL Server 6.5 to SQL Server 2000 and have begun to experiment with the uniqueidentifier data type. I applied a clustered index to a table called MyTable on a column defined as uniqueidentifier and ran the statement

SELECT * FROM MyTable

However, the data isn't sorted in the order of my clustered index column as I expected. Did I do something wrong?

The SQL script that Listing 4, page 17, shows creates a test environment you can use to explore this problem. In SQL Server 6.5, issuing a simple SELECT from a table would return data sorted in the order of the clustered index—even if you didn't use an ORDER BY clause. But in SQL Server 2000 and 7.0, you must include an ORDER BY clause for SQL Server to return data sorted in the order of the clustered index.

In this example, you're trying to sort by the RowGUID column, which is defined with a uniqueidentifier data type. However, you still might not get the results you expect, even when you include the appropriate ORDER BY clause. For example, run the following query against the test environment you created earlier, and you'll see that the data doesn't seem to come back sorted by the RowGUID column:

SELECT * FROM NoOrderByClauseOnGUID ORDER BY RowGUID

In fact, the data is sorted; it's just not sorted the way you expect. SQL Server displays globally unique identifier (GUID) data as a series of alphanumeric strings, but the uniqueidentifier data type is equivalent to the binary(16) data type. The GUID value you see is simply the hexadecimal representation of the binary value. So the data is sorted by this hex representation, not alphabetically.