• If the tables are part of a live system, I'd always want to see at least the current missing index stats and index usage stats from SQL itself (cardinality can also be very useful to include in the analysis). I can look at DDL and try to divine the access patterns, based on what looks obvious, but sometimes that's not right. I admit, even if/when the developers insist they know what the patterns are, I still prefer to confirm it with SQL's own, known-to-be-accurate stats.

    Given that those stats are not available yet, my general analysis is as follows.

    "Customer" table is fine. CustomerId, like OrderId, is a natural "master" table where identity often is the best clustering key. [See, unlike Celko, I don't actually hate identities just for the sake of hating them.]

    "CreditCard" table. I very strongly prefer the idea of a separate master table for credit cards, as Jeff suggests, especially since the CreditCard master table needs extreme security on it. I'd key that table by an int identity (4 billion values (using negative numbers) seems enough to me). You would then create a separate CustomerCreditCard to link the two, clustered on ( CustomerId, CreditCardId ), no identity needed or wanted (the horror!).

    If you decide to stick with this one table, it should be named CustomerCreditCard to indicate that it is an intersection table, not a master table. And cluster it as noted above. Btw, an int value won't hold a full cc number, so the CardNumber column is somewhat confusing.

    "CustomerProduct" table. This appears to be a product master table, but is named as if it were an intersection table. The table name should just be Product, and, again as Jeff noted, it should be clustered by a unique ProductNumber.

    "CustomerProductScore" table. I don't see a reason for this to be a separate table with only the columns that are shown. Why can't this be stored in the "CustomerProduct"/"Product"? Are there multiple values? If so, what key values distinguishes each Score value?

    Btw, this points out a very major issue with slapping "magic" identity columns on every table just to, allegedly, give you non-fragmenting clustering (falsely claimed to be the holy grail of table/key design). Without an identity, you can easily see that you don't have any key value(s) to distinguish multiple rows. That makes you realize that (a) the detail column(s) should go back into its main table, since they are 1:1 values or (b) you need to identity and use another distinguishing key value for this relationship/table. By delaying adding an identity unless/until it's actually [/i]required[/i], you must first identify natural keys, which points out possible data relationship issues.

    "TestDataDump" table. First, table must be renamed. Not sure what the new name should be, but that name is absolutely meaningless.

    Most likely this table should be clustered first on PurchaseDate, then perhaps by identity, but not enough here to tell. Index stats are required here. The product number should be stored, not the product name. If you need historical product names, as of a point-in-time, create a separate table to retain those.

    "#TestProcessedData" table. Interesting, as typically you'd want to first cluster this on date/datetime, but I don't see such a column in the table. I'll have to come back to this later, as I don't have time now.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.