High Low Guid instead of @@IDENTITY
-
Would anyone be interested in an article demonstrating a component that does High-Low identifiers as an alternative to Identity columns in SQL? For those not familiar, the idea is that you assign a unique id to a row of data before it is ever saved (in code). This has several benefits, like (off the top of my head, I'm sure there are more): * Row Ids are unique, even across tables * Replication is greatly simplified * Inserts are fast (because we don't need a select query to get back the @@IDENTITY) * SELECTs are also fast, since recent SQL databases have native support for Guids. The High-Low term refers to the specific technique used to do this in a way that guarantees uniqueness.
-
Would anyone be interested in an article demonstrating a component that does High-Low identifiers as an alternative to Identity columns in SQL? For those not familiar, the idea is that you assign a unique id to a row of data before it is ever saved (in code). This has several benefits, like (off the top of my head, I'm sure there are more): * Row Ids are unique, even across tables * Replication is greatly simplified * Inserts are fast (because we don't need a select query to get back the @@IDENTITY) * SELECTs are also fast, since recent SQL databases have native support for Guids. The High-Low term refers to the specific technique used to do this in a way that guarantees uniqueness.
I have used this method many times in the past. It has only one draw back. GUID's are not inherently sortable on SQL. This can hurt paging schemes (i.e. Give me the first 10 then the next 10) and random picking (i.e. random drug testing). Solving these issues is more informative then simply choosing a GUID for your primary key. By the way, joins & indexes are a little slower with GUIDs then with INTs. Nothing measurable until you get into large tables but, they are slower.