Eugene Elutin (10/2/2012)
[
...
Is this statement true? If so is it better to use Guid or BIGINT?
It depends, but usually BIGINT is better.
Another question, related to keys... If a table uses Id as PK should it be clustered if BIGINT identity? Or if, say a customers table, has a CustomerCode that's used for searches should that be the column for a clustered index (even if when inserting the codes aren't sequential generating page moves in the database files)?
...
It depends, but in this case, most likely your PK should be clustered and you will create non-clustered index on CustomerCode.
Almost certainly the CustomerCode will make a vastly better clustering key. In fact, since you already have a unique code per customer, you almost certainly don't need an identity column on that table.
Often customer codes are stored as character, but if they are entirely numeric, you could store them as int to save space and reduce the overhead of storage and joins.
In general, a nonclustered index is perfect for identity values. Use the clustered index for a more appropriate column. Of course specific joins and other things can change this.
An identity column should NOT be the default choice for a clustering key, period. You should always put some thought into the proper clustering key, not just base it on an overly-simplistic rule.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.