Sitecore database lesson 1 - It’s not all about items, it’s all about data

All Sitecore developers are agree on the fact that Sitecore is all about items. But does it really all about items? 

Sitecore create and maintain everything in the form of items like content tree items, layouts, templates, media library items. But where these items itself are stored behind the seen.

Sitecore prefer SQL server as its backend where every item is stored in database tables.

So one can say that - it’s not all about items, it’s all about data.

Sitecore database is one of the least explored area in Sitecore world. Even you will not get enough Sitecore database material on its official website. Knowledge of SQL server and its trick can make Sitecore developers life easy.

This series covers all possible database operations that leads and architect have to do. 

Sitecore Databases

Sitecore create 5 databases when you install Sitecore instance.

• Analytics
• Core
• Master
• Sessions
• Web

During installation Sitecore create these databases in SQL Server with instance name appended 

with word “Sitecore_” and type of database like Core, Master etc. as

<InstanceName>Sitecore_<Core | Master | Web | Analytics | Sessions>

Sitecore Databases

Master database

Master is content authoring database in Sitecore.

There are 22 tables and 1 view in Master database.  You can use below query to get all tables and view of the database.

SELECT * FROM information_schema.tables WHERE table_name != 'sysdiagrams' ORDER BY Table_Type, Table_Name

Here are the tables and view list

Sr. No.	TABLE_CATALOG	TABLE_NAME	TABLE_TYPE
1	SitecoreLessonsSitecore_Master	AccessControl	BASE TABLE
2	SitecoreLessonsSitecore_Master	Archive	BASE TABLE
3	SitecoreLessonsSitecore_Master	ArchivedFields	BASE TABLE
4	SitecoreLessonsSitecore_Master	ArchivedItems	BASE TABLE
5	SitecoreLessonsSitecore_Master	ArchivedVersions	BASE TABLE
6	SitecoreLessonsSitecore_Master	Blobs	BASE TABLE
7	SitecoreLessonsSitecore_Master	ClientData	BASE TABLE
8	SitecoreLessonsSitecore_Master	Descendants	BASE TABLE
9	SitecoreLessonsSitecore_Master	EventQueue	BASE TABLE
10	SitecoreLessonsSitecore_Master	History	BASE TABLE
11	SitecoreLessonsSitecore_Master	IDTable	BASE TABLE
12	SitecoreLessonsSitecore_Master	Items	BASE TABLE
13	SitecoreLessonsSitecore_Master	Links	BASE TABLE
14	SitecoreLessonsSitecore_Master	Notifications	BASE TABLE
15	SitecoreLessonsSitecore_Master	Properties	BASE TABLE
16	SitecoreLessonsSitecore_Master	PublishQueue	BASE TABLE
17	SitecoreLessonsSitecore_Master	Shadows	BASE TABLE
18	SitecoreLessonsSitecore_Master	SharedFields	BASE TABLE
19	SitecoreLessonsSitecore_Master	Tasks	BASE TABLE
20	SitecoreLessonsSitecore_Master	UnversionedFields	BASE TABLE
21	SitecoreLessonsSitecore_Master	VersionedFields	BASE TABLE
22	SitecoreLessonsSitecore_Master	WorkflowHistory	BASE TABLE
23	SitecoreLessonsSitecore_Master	Fields	VIEW

Here is Master database schema representation

Master Database Schema

As you notice there are many tables which don’t have any primary key like History, Tasks, Links etc.  Below image also confirms that no primary key is available in History table.

No Primary Key

There are only 4 tables which have primary key like Accescontrol, Archive, ArchivedVersions and Notifications. You can notice it from below image 

Primary Key

But why there are no relationship among tables?

Why De-normalization

You will not find any relationship among tables. Simple reason is just to improve the performance. If we introduce normalization, it starts to effect performance due to data integrity check, constraints, relationships etc. So you can guess how intelligently Sitecore database are designed by using concept de-normalization.

Master database don’t have any synonyms, stored procedures, functions, triggers, assemblies, types, Rules, Defaults, Sequences as shown below

Master Database Details

Web Database

Web database is content delivery database. Web database is exactly replica of Master database and its structure is same as master database. So all the points that I covered for Master Database are also applies on Web database.

Here are the list of tables and view of WEB database.

Sr. No.	TABLE_CATALOG	TABLE_NAME	TABLE_TYPE
1	SitecoreLessonsSitecore_Web	AccessControl	BASE TABLE
2	SitecoreLessonsSitecore_Web	Archive	BASE TABLE
3	SitecoreLessonsSitecore_Web	ArchivedFields	BASE TABLE
4	SitecoreLessonsSitecore_Web	ArchivedItems	BASE TABLE
5	SitecoreLessonsSitecore_Web	ArchivedVersions	BASE TABLE
6	SitecoreLessonsSitecore_Web	Blobs	BASE TABLE
7	SitecoreLessonsSitecore_Web	ClientData	BASE TABLE
8	SitecoreLessonsSitecore_Web	Descendants	BASE TABLE
9	SitecoreLessonsSitecore_Web	EventQueue	BASE TABLE
10	SitecoreLessonsSitecore_Web	History	BASE TABLE
11	SitecoreLessonsSitecore_Web	IDTable	BASE TABLE
12	SitecoreLessonsSitecore_Web	Items	BASE TABLE
13	SitecoreLessonsSitecore_Web	Links	BASE TABLE
14	SitecoreLessonsSitecore_Web	Notifications	BASE TABLE
15	SitecoreLessonsSitecore_Web	Properties	BASE TABLE
16	SitecoreLessonsSitecore_Web	PublishQueue	BASE TABLE
17	SitecoreLessonsSitecore_Web	Shadows	BASE TABLE
18	SitecoreLessonsSitecore_Web	SharedFields	BASE TABLE
19	SitecoreLessonsSitecore_Web	Tasks	BASE TABLE
20	SitecoreLessonsSitecore_Web	UnversionedFields	BASE TABLE
21	SitecoreLessonsSitecore_Web	VersionedFields	BASE TABLE
22	SitecoreLessonsSitecore_Web	WorkflowHistory	BASE TABLE
23	SitecoreLessonsSitecore_Web	Fields	VIEW

Core Database

Core database contains Sitecore environment setting and ASP.NET membership details. 

Here are the list of tables and view of CORE database.

Sr. No.	TABLE_NAME	TABLE_TYPE
1	AccessControl	BASE TABLE
2	Archive	BASE TABLE
3	ArchivedFields	BASE TABLE
4	ArchivedItems	BASE TABLE
5	ArchivedVersions	BASE TABLE
6	aspnet_Applications	BASE TABLE
7	aspnet_Membership	BASE TABLE
8	aspnet_Paths	BASE TABLE
9	aspnet_PersonalizationAllUsers	BASE TABLE
10	aspnet_PersonalizationPerUser	BASE TABLE
11	aspnet_Profile	BASE TABLE
12	aspnet_Roles	BASE TABLE
13	aspnet_SchemaVersions	BASE TABLE
14	aspnet_Users	BASE TABLE
15	aspnet_UsersInRoles	BASE TABLE
16	aspnet_WebEvent_Events	BASE TABLE
17	Blobs	BASE TABLE
18	ClientData	BASE TABLE
19	Descendants	BASE TABLE
20	EventQueue	BASE TABLE
21	History	BASE TABLE
22	IDTable	BASE TABLE
23	Items	BASE TABLE
24	Links	BASE TABLE
25	Notifications	BASE TABLE
26	Properties	BASE TABLE
27	PublishQueue	BASE TABLE
28	RolesInRoles	BASE TABLE
29	Shadows	BASE TABLE
30	SharedFields	BASE TABLE
31	Tasks	BASE TABLE
32	UnversionedFields	BASE TABLE
33	VersionedFields	BASE TABLE
34	WorkflowHistory	BASE TABLE
35	Fields	VIEW
36	vw_aspnet_Applications	VIEW
37	vw_aspnet_MembershipUsers	VIEW
38	vw_aspnet_Profiles	VIEW
39	vw_aspnet_Roles	VIEW
40	vw_aspnet_Users	VIEW
41	vw_aspnet_UsersInRoles	VIEW
42	vw_aspnet_WebPartState_Paths	VIEW
43	vw_aspnet_WebPartState_Shared	VIEW
44	vw_aspnet_WebPartState_User	VIEW

Core database contains 34 tables and 10 views. All rows marked in blue color are views.

Out of 34 tables 22 tables are same like master and web database, however rest 12 tables, marked in Green color, are used for maintaining ASP.NET membership details like users, roles, profiles etc.

CORE database don’t have any synonyms, functions, triggers, assemblies, types, Rules, Defaults, Sequences but have ASP.NET membership related stored procedures.

Use below query to get all the stored procedures 

select ROUTINE_CATALOG, ROUTINE_NAME , CREATED, LAST_ALTERED from information_schema.ROUTINES WHERE Routine_type='Procedure' and Routine_Name not like 'sp%' order by Routine_name

Below is the list of stored procedures in Core database.

Sr. No.	ROUTINE_CATALOG	ROUTINE_NAME
1	SitecoreLessonsSitecore_Core	aspnet_AnyDataInTables
2	SitecoreLessonsSitecore_Core	aspnet_Applications_CreateApplication
3	SitecoreLessonsSitecore_Core	aspnet_CheckSchemaVersion
4	SitecoreLessonsSitecore_Core	aspnet_Membership_ChangePasswordQuestionAndAnswer
5	SitecoreLessonsSitecore_Core	aspnet_Membership_CreateUser
6	SitecoreLessonsSitecore_Core	aspnet_Membership_FindUsersByEmail
7	SitecoreLessonsSitecore_Core	aspnet_Membership_FindUsersByName
8	SitecoreLessonsSitecore_Core	aspnet_Membership_GetAllUsers
9	SitecoreLessonsSitecore_Core	aspnet_Membership_GetNumberOfUsersOnline
10	SitecoreLessonsSitecore_Core	aspnet_Membership_GetPassword
11	SitecoreLessonsSitecore_Core	aspnet_Membership_GetPasswordWithFormat
12	SitecoreLessonsSitecore_Core	aspnet_Membership_GetUserByEmail
13	SitecoreLessonsSitecore_Core	aspnet_Membership_GetUserByName
14	SitecoreLessonsSitecore_Core	aspnet_Membership_GetUserByUserId
15	SitecoreLessonsSitecore_Core	aspnet_Membership_ResetPassword
16	SitecoreLessonsSitecore_Core	aspnet_Membership_SetPassword
17	SitecoreLessonsSitecore_Core	aspnet_Membership_UnlockUser
18	SitecoreLessonsSitecore_Core	aspnet_Membership_UpdateUser
19	SitecoreLessonsSitecore_Core	aspnet_Membership_UpdateUserInfo
20	SitecoreLessonsSitecore_Core	aspnet_Paths_CreatePath
21	SitecoreLessonsSitecore_Core	aspnet_Personalization_GetApplicationId
22	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAdministration_DeleteAllState
23	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAdministration_FindState
24	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAdministration_GetCountOfState
25	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAdministration_ResetSharedState
26	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAdministration_ResetUserState
27	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAllUsers_GetPageSettings
28	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAllUsers_ResetPageSettings
29	SitecoreLessonsSitecore_Core	aspnet_PersonalizationAllUsers_SetPageSettings
30	SitecoreLessonsSitecore_Core	aspnet_PersonalizationPerUser_GetPageSettings
31	SitecoreLessonsSitecore_Core	aspnet_PersonalizationPerUser_ResetPageSettings
32	SitecoreLessonsSitecore_Core	aspnet_PersonalizationPerUser_SetPageSettings
33	SitecoreLessonsSitecore_Core	aspnet_Profile_DeleteInactiveProfiles
34	SitecoreLessonsSitecore_Core	aspnet_Profile_DeleteProfiles
35	SitecoreLessonsSitecore_Core	aspnet_Profile_GetNumberOfInactiveProfiles
36	SitecoreLessonsSitecore_Core	aspnet_Profile_GetProfiles
37	SitecoreLessonsSitecore_Core	aspnet_Profile_GetProperties
38	SitecoreLessonsSitecore_Core	aspnet_Profile_SetProperties
39	SitecoreLessonsSitecore_Core	aspnet_RegisterSchemaVersion
40	SitecoreLessonsSitecore_Core	aspnet_Roles_CreateRole
41	SitecoreLessonsSitecore_Core	aspnet_Roles_DeleteRole
42	SitecoreLessonsSitecore_Core	aspnet_Roles_GetAllRoles
43	SitecoreLessonsSitecore_Core	aspnet_Roles_RoleExists
44	SitecoreLessonsSitecore_Core	aspnet_Setup_RemoveAllRoleMembers
45	SitecoreLessonsSitecore_Core	aspnet_Setup_RestorePermissions
46	SitecoreLessonsSitecore_Core	aspnet_UnRegisterSchemaVersion
47	SitecoreLessonsSitecore_Core	aspnet_Users_CreateUser
48	SitecoreLessonsSitecore_Core	aspnet_Users_DeleteUser
49	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_AddUsersToRoles
50	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_FindUsersInRole
51	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_GetRolesForUser
52	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_GetUsersInRoles
53	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_IsUserInRole
54	SitecoreLessonsSitecore_Core	aspnet_UsersInRoles_RemoveUsersFromRoles
55	SitecoreLessonsSitecore_Core	aspnet_WebEvent_LogEvent

 

Analytics Database

This is reporting database of Sitecore and store aggregated data from the collection database suitable for fast querying and reporting. All graphs on Experience Analytics DASHBOARD are fetched from this database.

Here are the list of tables and view of Analytics database.

Sr. No.	TABLE_CATALOG	TABLE_NAME	TABLE_TYPE
1	SitecoreLessonsSitecore_Analytics	Accounts	BASE TABLE
2	SitecoreLessonsSitecore_Analytics	Assets	BASE TABLE
3	SitecoreLessonsSitecore_Analytics	BusinessUnits	BASE TABLE
4	SitecoreLessonsSitecore_Analytics	CampaignActivityDefinitions	BASE TABLE
5	SitecoreLessonsSitecore_Analytics	Campaigns	BASE TABLE
6	SitecoreLessonsSitecore_Analytics	Contacts	BASE TABLE
7	SitecoreLessonsSitecore_Analytics	DeviceNames	BASE TABLE
8	SitecoreLessonsSitecore_Analytics	DimensionKeys	BASE TABLE
9	SitecoreLessonsSitecore_Analytics	Fact_AutomationStates	BASE TABLE
10	SitecoreLessonsSitecore_Analytics	Fact_Conversions	BASE TABLE
11	SitecoreLessonsSitecore_Analytics	Fact_Downloads	BASE TABLE
12	SitecoreLessonsSitecore_Analytics	Fact_Failures	BASE TABLE
13	SitecoreLessonsSitecore_Analytics	Fact_FollowHits	BASE TABLE
14	SitecoreLessonsSitecore_Analytics	Fact_MvTesting	BASE TABLE
15	SitecoreLessonsSitecore_Analytics	Fact_MvTestingDetails	BASE TABLE
16	SitecoreLessonsSitecore_Analytics	Fact_PageViews	BASE TABLE
17	SitecoreLessonsSitecore_Analytics	Fact_PageViewsByLanguage	BASE TABLE
18	SitecoreLessonsSitecore_Analytics	Fact_Personalization	BASE TABLE
19	SitecoreLessonsSitecore_Analytics	Fact_RulesExposure	BASE TABLE
20	SitecoreLessonsSitecore_Analytics	Fact_Searches	BASE TABLE
21	SitecoreLessonsSitecore_Analytics	Fact_SegmentMetrics	BASE TABLE
22	SitecoreLessonsSitecore_Analytics	Fact_SegmentMetricsReduced	BASE TABLE
23	SitecoreLessonsSitecore_Analytics	Fact_SiteSearches	BASE TABLE
24	SitecoreLessonsSitecore_Analytics	Fact_SlowPages	BASE TABLE
25	SitecoreLessonsSitecore_Analytics	Fact_TestConversions	BASE TABLE
26	SitecoreLessonsSitecore_Analytics	Fact_TestOutcomes	BASE TABLE
27	SitecoreLessonsSitecore_Analytics	Fact_TestPageClicks	BASE TABLE
28	SitecoreLessonsSitecore_Analytics	Fact_TestStatistics	BASE TABLE
29	SitecoreLessonsSitecore_Analytics	Fact_Traffic	BASE TABLE
30	SitecoreLessonsSitecore_Analytics	Fact_ValueBySource	BASE TABLE
31	SitecoreLessonsSitecore_Analytics	Fact_Visits	BASE TABLE
32	SitecoreLessonsSitecore_Analytics	Fact_VisitsByBusinessContactLocation	BASE TABLE
33	SitecoreLessonsSitecore_Analytics	FailureDetails	BASE TABLE
34	SitecoreLessonsSitecore_Analytics	FunnelDefinitions	BASE TABLE
35	SitecoreLessonsSitecore_Analytics	GoalDefinitions	BASE TABLE
36	SitecoreLessonsSitecore_Analytics	Items	BASE TABLE
37	SitecoreLessonsSitecore_Analytics	Keywords	BASE TABLE
38	SitecoreLessonsSitecore_Analytics	Languages	BASE TABLE
39	SitecoreLessonsSitecore_Analytics	MarketingAssetDefinitions	BASE TABLE
40	SitecoreLessonsSitecore_Analytics	OutcomeDefinitions	BASE TABLE
41	SitecoreLessonsSitecore_Analytics	PageEventDefinitions	BASE TABLE
42	SitecoreLessonsSitecore_Analytics	Properties	BASE TABLE
43	SitecoreLessonsSitecore_Analytics	ReferringSites	BASE TABLE
44	SitecoreLessonsSitecore_Analytics	SegmentRecords	BASE TABLE
45	SitecoreLessonsSitecore_Analytics	SegmentRecordsReduced	BASE TABLE
46	SitecoreLessonsSitecore_Analytics	Segments	BASE TABLE
47	SitecoreLessonsSitecore_Analytics	SiteNames	BASE TABLE
48	SitecoreLessonsSitecore_Analytics	Taxonomy_TaxonEntity	BASE TABLE
49	SitecoreLessonsSitecore_Analytics	Taxonomy_TaxonEntityFieldDefinition	BASE TABLE
50	SitecoreLessonsSitecore_Analytics	Taxonomy_TaxonEntityFieldValue	BASE TABLE
51	SitecoreLessonsSitecore_Analytics	Testing_ClusterMembers	BASE TABLE
52	SitecoreLessonsSitecore_Analytics	Testing_Clusters	BASE TABLE
53	SitecoreLessonsSitecore_Analytics	TrafficTypes	BASE TABLE
54	SitecoreLessonsSitecore_Analytics	Trail_AutomationStates	BASE TABLE
55	SitecoreLessonsSitecore_Analytics	Trail_Interactions	BASE TABLE
56	SitecoreLessonsSitecore_Analytics	Trail_PathAnalyzer	BASE TABLE
57	SitecoreLessonsSitecore_Analytics	TreeDefinitions	BASE TABLE
58	SitecoreLessonsSitecore_Analytics	Trees	BASE TABLE
59	SitecoreLessonsSitecore_Analytics	VisitorClassification	BASE TABLE
60	SitecoreLessonsSitecore_Analytics	CampaignsOverview	VIEW
61	SitecoreLessonsSitecore_Analytics	Conversions	VIEW
62	SitecoreLessonsSitecore_Analytics	Downloads	VIEW
63	SitecoreLessonsSitecore_Analytics	FollowHits	VIEW
64	SitecoreLessonsSitecore_Analytics	NotFoundUrls	VIEW
65	SitecoreLessonsSitecore_Analytics	ReportDataView	VIEW
66	SitecoreLessonsSitecore_Analytics	SiteSearches	VIEW
67	SitecoreLessonsSitecore_Analytics	SlowPages	VIEW
68	SitecoreLessonsSitecore_Analytics	TopLeads	VIEW
69	SitecoreLessonsSitecore_Analytics	Traffic	VIEW
70	SitecoreLessonsSitecore_Analytics	TrafficOverview	VIEW
71	SitecoreLessonsSitecore_Analytics	ValueBySource	VIEW

Below is the list of stored procedures in Analytics database.

Sr. No.	ROUTINE_CATALOG	ROUTINE_NAME
1	SitecoreLessonsSitecore_Analytics	__DeleteAllReportingData
2	SitecoreLessonsSitecore_Analytics	Add_AutomationStates
3	SitecoreLessonsSitecore_Analytics	Add_Conversions
4	SitecoreLessonsSitecore_Analytics	Add_Conversions_Tvp
5	SitecoreLessonsSitecore_Analytics	Add_Downloads
6	SitecoreLessonsSitecore_Analytics	Add_Downloads_Tvp
7	SitecoreLessonsSitecore_Analytics	Add_Failures
8	SitecoreLessonsSitecore_Analytics	Add_Failures_Tvp
9	SitecoreLessonsSitecore_Analytics	Add_FollowHits
10	SitecoreLessonsSitecore_Analytics	Add_FollowHits_Tvp
11	SitecoreLessonsSitecore_Analytics	Add_MvTesting
12	SitecoreLessonsSitecore_Analytics	Add_MvTestingDetails
13	SitecoreLessonsSitecore_Analytics	Add_PageViews
14	SitecoreLessonsSitecore_Analytics	Add_PageViewsByLanguage
15	SitecoreLessonsSitecore_Analytics	Add_Personalization
16	SitecoreLessonsSitecore_Analytics	Add_RulesExposure
17	SitecoreLessonsSitecore_Analytics	Add_SegmentMetrics
18	SitecoreLessonsSitecore_Analytics	Add_SegmentMetrics_Tvp
19	SitecoreLessonsSitecore_Analytics	Add_SiteSearches
20	SitecoreLessonsSitecore_Analytics	Add_SiteSearches_Tvp
21	SitecoreLessonsSitecore_Analytics	Add_SlowPages
22	SitecoreLessonsSitecore_Analytics	Add_SlowPages_Tvp
23	SitecoreLessonsSitecore_Analytics	Add_TestConversions
24	SitecoreLessonsSitecore_Analytics	Add_TestingCluster
25	SitecoreLessonsSitecore_Analytics	Add_TestingClusterMembers
26	SitecoreLessonsSitecore_Analytics	Add_TestOutcomes
27	SitecoreLessonsSitecore_Analytics	Add_TestPageClicks
28	SitecoreLessonsSitecore_Analytics	Add_TestStatistics
29	SitecoreLessonsSitecore_Analytics	Add_Traffic
30	SitecoreLessonsSitecore_Analytics	Add_Traffic_Tvp
31	SitecoreLessonsSitecore_Analytics	Add_ValueBySource
32	SitecoreLessonsSitecore_Analytics	Add_ValueBySource_Tvp
33	SitecoreLessonsSitecore_Analytics	Add_Visits
34	SitecoreLessonsSitecore_Analytics	Add_Visits_Tvp
35	SitecoreLessonsSitecore_Analytics	Add_VisitsByBusinessContactLocation
36	SitecoreLessonsSitecore_Analytics	Add_VisitsByBusinessContactLocation_Tvp
37	SitecoreLessonsSitecore_Analytics	Delete_CampaignActivityDefinitions
38	SitecoreLessonsSitecore_Analytics	Delete_FunnelDefinition
39	SitecoreLessonsSitecore_Analytics	Delete_GoalDefinitions
40	SitecoreLessonsSitecore_Analytics	Delete_MarketingAssetDefinition
41	SitecoreLessonsSitecore_Analytics	Delete_OutcomeDefinition
42	SitecoreLessonsSitecore_Analytics	Ensure_Asset
43	SitecoreLessonsSitecore_Analytics	Ensure_Assets_Tvp
44	SitecoreLessonsSitecore_Analytics	Ensure_BusinessUnit
45	SitecoreLessonsSitecore_Analytics	Ensure_BusinessUnits_Tvp
46	SitecoreLessonsSitecore_Analytics	Ensure_DeviceName
47	SitecoreLessonsSitecore_Analytics	Ensure_DeviceNames_Tvp
48	SitecoreLessonsSitecore_Analytics	Ensure_DimensionKeys
49	SitecoreLessonsSitecore_Analytics	Ensure_DimensionKeys_Tvp
50	SitecoreLessonsSitecore_Analytics	Ensure_FailureDetails
51	SitecoreLessonsSitecore_Analytics	Ensure_FailureDetails_Tvp
52	SitecoreLessonsSitecore_Analytics	Ensure_Item
53	SitecoreLessonsSitecore_Analytics	Ensure_Items_Tvp
54	SitecoreLessonsSitecore_Analytics	Ensure_Keywords
55	SitecoreLessonsSitecore_Analytics	Ensure_Keywords_Tvp
56	SitecoreLessonsSitecore_Analytics	Ensure_Language
57	SitecoreLessonsSitecore_Analytics	Ensure_Languages_Tvp
58	SitecoreLessonsSitecore_Analytics	Ensure_ReferringSite
59	SitecoreLessonsSitecore_Analytics	Ensure_ReferringSites_Tvp
60	SitecoreLessonsSitecore_Analytics	Ensure_SegmentRecords
61	SitecoreLessonsSitecore_Analytics	Ensure_SegmentRecords_Tvp
62	SitecoreLessonsSitecore_Analytics	Ensure_SiteName
63	SitecoreLessonsSitecore_Analytics	Ensure_SiteNames_Tvp
64	SitecoreLessonsSitecore_Analytics	Get_AllTaxonEntities
65	SitecoreLessonsSitecore_Analytics	Get_MissingDailyTrees
66	SitecoreLessonsSitecore_Analytics	Get_TaxonEntity
67	SitecoreLessonsSitecore_Analytics	Get_TaxonEntityChildren
68	SitecoreLessonsSitecore_Analytics	Get_TaxonEntityList
69	SitecoreLessonsSitecore_Analytics	ReduceSegmentMetrics
70	SitecoreLessonsSitecore_Analytics	Register_AutomationStates
71	SitecoreLessonsSitecore_Analytics	Register_Interaction
72	SitecoreLessonsSitecore_Analytics	Upsert_Account
73	SitecoreLessonsSitecore_Analytics	Upsert_Campaign
74	SitecoreLessonsSitecore_Analytics	Upsert_CampaignActivityDefinitions
75	SitecoreLessonsSitecore_Analytics	Upsert_Contact
76	SitecoreLessonsSitecore_Analytics	Upsert_FunnelDefinition
77	SitecoreLessonsSitecore_Analytics	Upsert_GoalDefinition
78	SitecoreLessonsSitecore_Analytics	Upsert_MarketingAssetDefinition
79	SitecoreLessonsSitecore_Analytics	Upsert_OutcomeDefinition
80	SitecoreLessonsSitecore_Analytics	Upsert_PageEventDefinition
81	SitecoreLessonsSitecore_Analytics	Upsert_TaxonEntities
82	SitecoreLessonsSitecore_Analytics	Upsert_TaxonEntity
83	SitecoreLessonsSitecore_Analytics	Upsert_TaxonEntityField
84	SitecoreLessonsSitecore_Analytics	Upsert_TaxonEntityFields
85	SitecoreLessonsSitecore_Analytics	Upsert_TrafficType
86	SitecoreLessonsSitecore_Analytics	Upsert_VisitorClassification

There are 2 Scalar-valued functions which you can get by using below query

select ROUTINE_CATALOG, ROUTINE_NAME , CREATED, LAST_ALTERED from information_schema.ROUTINES WHERE Routine_type='Function' and Routine_Name not like 'fn%' order by Routine_name

Below is the list of functions in Analytics database.

Sr. No.	ROUTINE_CATALOG	ROUTINE_NAME
1	SitecoreLessonsSitecore_Analytics	GetTagValue
2	SitecoreLessonsSitecore_Analytics	GetTaxonEntityChildIds

There are lots of User-defined Table Types are present in Analytics database as

Analytics Table Types

Analytics database don’t have any synonyms, triggers, assemblies, Rules, Defaults and Sequences.

Sessions Database

This database stores user sessions details. Session database is a session state store used by the content delivery and personalization process. This is a standard session state store provider that includes support for the Session_End event.

There are 2 tables in Sessions database.

Sessions Database Details

Synonyms

Interestingly there are 2 synonyms are also present in Sessions database – Applications and Sessions.

Applications synonym pointing to Application table while Sessions pointing to SessionState table.

Why Synonyms

Sessions is the only database where Synonyms are present. I suppose this is due to maintain backward compatibility. 

If you fire query on table or its synonyms, you get the same result as shown in below image

This database don’t have any triggers, assemblies, types, Rules, Defaults and Sequences.

Functions

There is only 1 Scalar-valued functions present in this database as

 

Sr. No.	ROUTINE_CATALOG	ROUTINE_NAME
1	SitecoreLessonsSitecore_Sessions	IsLocked

I hope you like my first article on Sitecore database. Stay tune for more Sitecore database related articles. 

Please leave your comments or share this article if it’s useful for you.