This question has been in the back of my mind for as long as I’ve been working with Sitecore. Is there an internal difference in the way items are stored to and retrieved from these databases? I put some time into investigating:
Versions
The main difference is the way versions are stored. While the master database will contain every version of each item, only the latest version of an item is published to the web database. This is described in more detail here.
An exception to this applies to Sitecore 8.0 and upwards where all versions that are used in a content test will also be published to web. This is done in the newly introduced publishVersion pipeline. More details in this post.
Database schema
The next thing to investigate is the database schema. Are the tables the same? Are the same indexes set?
To find out, I installed several vanilla SC versions and had SQL management studio script the database and all objects for me. The scripts could then be compared using a diff tool reveiling the following:
| Version | Differences |
| 7.2 Rev. 151021 | ‘uniqueidentifier’ column of table ‘ClientData’ is the last column instead of the fist. Apart from that: no differences. |
| 8.1 rev. 160519 | No differences at all. |
| 8.2 Rev. 160729 | No differences at all. |
The result surprised me a little because I did expect some differences around indexes, but both database schemas are actually exactly the same.
What’s with core?
The core database schema on the other hand differs significantly from the other databases as it also contains the asp.net membership provider tables.