>>
exactly how are the dataadapter and sql server working under the covers to make this happen?
<<

I'm not sure about all the details of this.  

But a central point from the application - ADO.NET - end, I think, is that when a commandbuilder autogenerates an INSERT statement for a database table on which the Primary Key is AutoIncrement it does not include any Primary Key information from the datatable.  For UPDATE and DELETE commands, the Primary Key is essential, because the record which is to be updated or deleted has to be uniquely identified.  If a record to be inserted is not going to have its Primary Key generated automatically by the database, again it will be essential (and must be unique) or else the insert will violate the rules that a Primary Key must not be null and must be unique.  This is why, for instance, if you use the configuration wizards or a commandbuilder in relation to a database table which does not return any Primary Key information it may well generate the SELECT and INSERT commands but it will tell you it cannot generate the UPDATE and DELETE commands.

So, so far as the dataadapter is concerned, it is - in effect - simply saying to the server "Here's the details for a new record, generate the primary key yourself".  There are - as I mentioned in your question

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23245288.html

- techniques for the dataadapter to add "and then please tell me what it is".

At the server end, when it receives a message in that form and you've "set the Primary key col to Identity = Yes" it simply does as it's asked.

What I'm less sure about is what happens when - rather than the dataadapter using an autogenerated INSERT command - you code your own and include primary key information which would be inconsistent with the server autogenerating the Identity Primary Key.  But is what I've said above sufficient for your present purposes?

Roger