The limit of my understanding how a database stores a primary key is a btree based on the clustered key with each node storing the rest of the columns.
I have not updated this understanding since university last century and would love to know how SQL Server stores a composite primary key compared to a single primary key.
Can anyone help point me to some detail on this?
A composite key is exactly the same as a ordinary, single column, key, but is longer and composed of multiple values. Consider you have a B-Tree on a single column, say A. The non-leaf pages contains slots with values of A column, and pointers to leaf-pages. The leaf pages contain slots with the column A values (the key), followed by all the other columns of the row. A composite key is exactly the same, but the values in the slots will be the composite values, in the order they are declared in the key.
There is a great description of the internals of a SQL Server data page at Anatomy of a Page. Also, the Chapter 6 from Kelan Delaney's old SQL 7.0 book is available online on Technet: Tables. Is still a great resource, the basics still apply to SQL 2008 R2 (things that are changed are mostly around max types and compression settings, but the bulk of info is still valid).