17.03.2009

Creating Primary Key on Guid Column

An easy way of designing primary key is creating  it on  a guid column so it does not a matter in replication time or i can generate my guid from an application or  inside of SQL. In my opinion guid columns are most dangerous  type of a database when primary key strategy  depends on this column.  As we know primary key means unique clustered index and clustered indexes affects data location on file system. Let’s think B-Tree which is the algorithm of clustered index as sorted linked list and think guid as an integer number. Guid is like a box of chocolate you don’t even know what you get.Let’s generate our guids  first we get 2 then 7 and then 3. Can you imagine it, when 3 is generated it pushes  7 to another location on the file system so this is fragmentation. Instead of using simple Guid columns use sequential guid that  the feature is shipped with SQL Server 2005. The code below is a simple measurement  of how guid column and a sequential guid affect fragmentation.

 

   1: use BlogDb


   2: go


   3: if exists(select * from sys.tables where name = 'TagGuid')


   4:     drop table TagGuid


   5: go


   6: create table TagGuid


   7: (


   8:     TagId    uniqueidentifier not null  primary key    ,


   9:     Name    varchar(255) not null


  10: )    


  11: go


  12:  


  13: if exists(select * from sys.tables where name = 'TagSeqGuid')


  14:     drop table TagSeqGuid


  15: go


  16: create table TagSeqGuid


  17: (


  18:     TagId    uniqueidentifier default newsequentialid() not null  primary key    ,


  19:     Name    varchar(255) not null


  20: )


  21: go


  22:  


  23:  


  24:  


  25: declare @i int = 10000    


  26:  


  27: while @i > 0


  28: begin


  29:     set @i -=1


  30:     insert into TagGuid(TagId,Name) values(newid(),cast(@i as varchar(5)))


  31:     insert into TagSeqGuid(Name) values(cast(@i as varchar(5)))


  32: end


  33:  


  34: go




Fragmentation Results



image





Hiç yorum yok: