Primary Keys in SQL Server

Throughout my travels in the IT industry I have seen many database designs and it never fails to amaze me how many systems simply ignore the fundamentals of good design. I am not talking about poor designs from an academic point of view; these sub-standard designs don’t work because they are a performance, programming, scalability and maintenance nightmare.