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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".