I would have to agree with most of the commenters on this post. I am currently using SQL server 2005 for most of my development, and yes I do create logical data models and identify what the unique entity keys should be, and yes I use surrogate keys as my primary keys in all my tables and add a unique constraint on the entity key. And until someone shows me a SQL Server 2005 benchmark test that proves that joining two tables using three varchar(10) fields is faster, I'll most likely continue this practice.

For a significant number of database records in one or more tables being joined together, surrogate keys are vital for performance. The same applies, I'd argue, with a standard SQL Server Analysis Services data source.