How does Sitecore read item data ?

Where is content stored ?

All the item content/data is stored inside Shared, Unversioned, and Versioned field tables depending on field sharing type.
Items table has metadata about an item (ID, name, parentID, template).
Sitecore unions item metadata with actual fields content to construct an item instance.

How an item is read ?

'Sitecore.Data.SqlServer.SqlServerDataProvider.LoadItemDefinitions' method is responsible fetching data.
It raises a SQL query that unions aforementioned tables by configurable ItemID condition:

Item-related tables

SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
FROM (
SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '' as [Language], [Name], '' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]   FROM [Items] {A}
UNION ALL
SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '' as [Language], NULL as [Name], '', NULL, NULL, [Id]   FROM [Items] {B} 
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], '' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL   FROM [SharedFields] {C}
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL   FROM [UnversionedFields] {C}
UNION ALL
SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL FROM [VersionedFields] {C})
as temp
WHERE [ItemId] IN (ItemID condition {D} ) ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC

Description

Black part {D} sets a condition to fetch items.
Examples:

• ItemID = GUID in case single item fetch
• ParentID=GUID in case loading item children
• TemplateID in GUIDs in case prefetching items by template on CMS start

Red statement {A} selects data about currently read item

Yellow part {B} reads child IDs for the item
Blue part {C} gets item fields itself
Green part orders read rows by itemID, and orders them by Order column:

Rows order

Notes

1. A whole item is read at once ( all versions in all languages ) from database when requesting one item version. It is highly NOT recommended to have many item versions in database ( Details in 6.2.3 chapter of CMS Performance Tuning Guide )
2. Children IDs are read during item fetch as well
3. The Sitecore performance is much determined by performance of the SQL query

In the next chapter we will take a closer look on how to improve the performance of the query.