Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, October 8, 2016

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
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
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
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
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
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 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
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.