![]() ![]() Not such a big deal here, but when you're talking about inserting something into the middle of a table with millions of rows, it starts becoming an issue. Rows 7 and 8 have to be moved down to make room. If we add a new row with an ID of 8, it's no problem: the row just gets tacked on to the end.īut now suppose we want to insert a row with an ID of 5: Pretty simple so far: the rows are stored in order according to the value of the ID column. For example, consider the following data: This makes finding a single row as simple as doing a quick lookup in the index, but it can make adding new rows to the table very slow if their primary key doesn't fall at the end of the list. To get the best performance, most databases store rows in what's known as a clustered index, meaning that the rows in a table are actually stored on disk in a sorted order, usually based on a primary key value. At first glance, they seem to provide a good answer to the problem. GUIDs have the advantage of allowing developers to create new key values on the fly without having to check in with the server, and without having to worry that the value might already be used by someone else. Standards for creating GUIDs are described in RFC 4122, but most GUID-creation algorithms in common use today are either essentially a very long random number, or else combine a random-appearing component with some kind of identifying information for the local system, such as a network MAC address. A GUID (globally unique identifier), also known as a UUID, is a 128-bit value that carries a reasonable guarantee of being unique across all of space and time. One tempting alternative is to use GUIDs as key values. ![]() Likewise, replication scenarios also make it problematic to rely on a single authoritative source for key value creation - the entire point is to minimize the role of a single authority. With the increasing use of Object-Relational Mapping (ORM) frameworks such as NHibernate and the ADO.NET Entity Framework, relying on the server to generate key values adds a lot of complication that most people would prefer to avoid. ![]() However, there are also some situations where it's not ideal. This is a simple, clean approach that's suitable for many applications. Historically, a very common model for database design has used sequential integers to identify a row of data, usually generated by the server itself when the new row is inserted. This article attempts to adapt the approach into a flexible system that's can be used with other common database systems such as Oracle, PostgreSQL, and MySQL, and also addresses some of the eccentricities of the. While that basic model has been used by a variety of libraries and frameworks (including NHibernate), most implementations seem to be specific to Microsoft SQL Server. This article outlines an approach for using GUID values as primary keys/clustered indexes that avoids most of the normal disadvantages, adapting the COMB model for sequential GUIDs developed by Jimmy Nilsson in his article The Cost of GUIDs as Primary Keys. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |