I am looking in AdventureWorksDW2008R2 database. I looked at its fact tables, e.g., FactInternetSales I was expecting that it will have its primary key or at least I have a unique index on (ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ) All the columns it has foreign keys on. But to my surprise it has primary key on [SalesOrderNumber] , [SalesOrderLineNumber]) 2. Similarly I see FACTSalesQuota has its primary key on SalesQuotaKey which is a surrogate key instead of DateKey, EmployeeKey I have read many artcles on internet, while Kimbals book recommends a primary key all the dim tables in its star schema, everyone does not follow this approach, why?? If in start schema there are many dim tables, primary key can be very long. If we do not have primary key on all dim keys, are there any performance implications because SQL Server optimzer does some optimizations based on start schema.

Hi Prem, you are right that columns(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ) should create the primary key(Surrogate keys). Surrogate key is the primary key of the dimension.but normally due to DML operations on dimensions, (manily insert) it's not recommend to create the physical PK and FK relationships between column in Datawarehouse database. now take the example of AdventureWoks database it's depends upon the design which column(s) you want to create as the primary key. design point of each dimension should have surrogate key on it. Thanks, Zaim Raza.