• ScottPletcher (10/2/2012)


    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.

    1. I haven't seen OP stating that CustomerCode is unique and cannot be reused.

    2. Sinlge searching pattern alone doesn't mean that this key should be made to clustered index, may be but not always. If CustomerCode is of alpha-numeric nature, making this clustered will lead to higher fragmentation.

    3. Until there is a good reason, identity column is a good choice for a clustered index, as it is narrow, unique, static and always increasing (which helps to avoid fragmentation)

    If CustomerCode is really used for searching one single customer record, the performance wise, having it as clustered or non-clustered index will not be much different.

    I do usually use a bit more than this when determine candidate for clustered index:

    1. Range searches

    2. Use in joins

    3. Use in ORDER BY and GROUP BY

    4. Returning large resultsets.

    Also clustered index narrowness plays some role in the effectiveness of index as well.

    I guess there are two camps here: Natural Keys vs Surrogate Keys :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]