Okay, so this is a really cool new little feature. A lot of concern with using GUIDs a keys was that they were reasonably random, and they made indexes wildly fragment as they were created. So this little feature allow us to make sure that guids created for a given table are always increasing (so we have less fragmentation, though the indexes can get balanced heavily to one end)
You cannot use them in a query to define them, but you can in a table:
select *from sys.tablesorder by newsequentialid()
Msg 302, Level 16, State 0, Line 1The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.go
You can only use them in a DEFAULT (which you would have known if you hadn't skipped the
create table test( testGuid uniqueidentifier default (newsequentialid()), testId int identity(1,1))goinsert into test default valuesgo 10 --this is a handy little device that was added to 2005 SSMS
Now, we will use the new ranking functions to see that the new GUIDs are in fact ordered:
select testGuid, row_number() over (order by testGuid) as sortByTestGuid, testId, row_number() over (order by testId) as sortByTestIdfrom test
testGuid sortByTestGuid testId sortByTestId------------------------------------ -------------------- ----------- ---------------13C2A79A-B4F5-D911-B7C6-000625ACB4F5 1 1 1030CFDA0-B4F5-D911-B7C6-000625ACB4F5 2 2 2040CFDA0-B4F5-D911-B7C6-000625ACB4F5 3 3 3050CFDA0-B4F5-D911-B7C6-000625ACB4F5 4 4 4060CFDA0-B4F5-D911-B7C6-000625ACB4F5 5 5 5070CFDA0-B4F5-D911-B7C6-000625ACB4F5 6 6 6080CFDA0-B4F5-D911-B7C6-000625ACB4F5 7 7 7090CFDA0-B4F5-D911-B7C6-000625ACB4F5 8 8 80A0CFDA0-B4F5-D911-B7C6-000625ACB4F5 9 9 90B0CFDA0-B4F5-D911-B7C6-000625ACB4F5 10 10 10
Dig those new GUID values. Very interesting. My brain is far too simple to keep up with most of the details of guids, but what really makes me wonder is how they will keep them globally unique AND increasing if they are forcing the domain of guids to always grow. Magic, I will bet, the crafty devils...
One last thing, don't expect the results to look sorted right. Look at the first two rows. 13C2... sorts before 0300... You aren't sorting them by the textual representation, but rather the binary. Run this query and keep inserting data and you will see why:
select testGuid, cast(testGuid as binary(16)), row_number() over (order by testGuid) as sortByTestGuid, row_number() over (order by cast(testGuid as varchar(40))) as sortByTestGuid_Text, testId, row_number() over (order by testId) as sortByTestIdfrom testgo
No comments:
Post a Comment