Sql-server – Is Guid the best identity datatype for Databases


It is connected to BI and merging of data from different data sources and would make that process more smooth.

And is there an optimal migration strategy from a database without Guids to a version with Guids without information losses?

Best Solution

Keep in mind that GUID's (or 'unique_identifier') for PK's is a bad choice, as many PK's have a clustered index (so all rows are stored on disk in the indexed order). As GUID's are random, it's not certain a new row will be appended at the end of the index, but could be inserted in the middle of the index. This causes disk trashing as the rows have to be moved.

IF you consider guid's, at least use sqlserver 2005 or up and NEWSEQUENTIALID() for the PK value, to get sequential guid's which are always bigger than the last one, so are always appended at the end of the index. If you're not using sqlserver (but for example postgresql or you're using oracle and use CHAR(32) or other type), consider COMB's (see: http://www.informit.com/articles/article.aspx?p=25862 )