---

The identity trinity: tbl_Changeset, tbl_VCIdentityMap and Constants

---

First of all, please note that this post applies to TFS2013. Earlier or later versions may have a different structure, leaving this deprecated or invalid for you.

Now, I’m posting this just out of awareness, because I had to look around a lot before I figured out how some of the tables of the project collection database are related. Usually we’d see these kinds of relationships denoted by foreign keys and whatnot, and I don’t exactly know the reason this isn’t the case here. Must’ve a motive.

Not many people will find use in this, but it may be very important if you’re customizing TFS or migrating it. Or just for any shadowy reason you might have.

Either way, I’ll be straight forward:

Though there are no foreign keys to depict that, the OwnerId and CommitterId fields from the tbl_changeSet table are connected to IdentityId from the tbl_VCIdentityMap table. To get the DisplayName and such, you have to join TeamFoundationId from tbl_VCIdentityMap table to its equivalent from Constants table.

Also, be sure to include PartitionId in the join clause. Note that this doesn’t just apply to the tbl_changeSet table, and may be useful elsewhere.

I figured this out by checking some of the stored procedures in the tfs database, like prc_QueryChangeSet. They are encrypted but it’s no big deal, you can check them out. There are lots of sql scripts around the web to do this. To make things easy, there are tools that can decrypt it to you, such as ApexSQL Complete, or Optillect SQL Decryptor.

---

Own it up and don’t committer suicide!

---

If you noticed, there are two possible ids that can lead to an identity. OwnerId and CommitterId. Sorry for the terrible pun!

OwnerId points to the person responsible for the content of the changeset. CommitterId points to the one who checked it in. They are USUALLY the same person, though this may not be the case. Team Foundation Server allows executing operation on behalf of other users, so the committer could be someone else. Having two fields allows this kind of functionality so you always know who clicked the button, and whose workspace was being sent to the glorious fields of source control history.

Generally speaking you’ll at most times want OwnerId, not CommitterId. I’ve mistakenly assigned work items to a service account before because I didn’t know that. An automated bot would check code from other people in, create a work item and erroneously assign it to itself.

---

Why do I need this knowledge?

---

I must say… As you delve deep inside the guts of TFS customization, this kind of knowledge can be useful for optimizations, specific queries to figure out specific issues, and I’d even say reports. But don’t use the tfs project collection database directly for that last one. If possible use/customize the cube and move from there.

Either way it’s good to know how things work. By inspecting the code and database I’ve come across a lot of useful answers to the most troublesome questions. I hope I’ll be able to clarify some of them on these posts.

At last but not least, if you’re reading this page, thank you.
I’m very open to comments, questions, and critiques. Expect me to answer to all of them.