SharePoint List internals with content DB details

SharePoint List internals with content DB details
12 votes, 4.42 avg. rating (88% score)

Introduction

Any SharePoint list we create, its information will be saved in Sql server content database.In this post we will see the internals of SharePoint list in relation to the content DB.
What information the article has

  • What happens in content DB when SharePoint List is created
  • What happens in content DB when SharePoint List item is created
  • What happens in content DB when SharePoint List item is deleted

Skill Level – Advanced

AllLists

AllLists table in the content db stores the information when list is created. It might be very complex to describe every column, but if we get to know some
important columns we will certainly understand what is going.

Columns

tp_ID [uniqueidentifier]
>To unquely identify the list

tp_WebId [uniqueidentifier]
>To identify web, the list belongs to. It is the value taken from ‘AllWebs’ table which has Web Id in ‘Id’ column(tp_ID and tp_WebId together form a primary key for ‘AllLists’ table)

tp_Title [nvarchar]
>To store title of the list

tp_BaseType [int]
>To store the base type of the list
(Generic List: 0, Document Library: 1, Discussion Board:3, Survey:4,Issue:5)

tp_ServerTemplate[int]
>To store the type of the list(list type id)
(ex:100-Generic list,101-Document library,102-Survey,103-Links list,104-Announcements list,
105-Contacts list,106-Events list, 107-Tasks list,108-Discussion board,109-Picture library)

tp_Fields [varbinary]
>To store fields of list in encoded format

tp_ContentTypes [varbinary]
>To store content type of list in encoded format

A new custom list ‘SalesInfo’ is created. Now we will see values of ‘AllLists’ table





tp_ID ‘1FB218D6-8C61-45A5-B30D-32EF4F8087AC’ is the identifier of ‘SalesInfo’ list, any item in this list will be saved in ‘AllUserData’ table along with list id (tp_ID) value

AllUserData

AllUserData table in content db is the single table to store items of any list. This table has 192 columns and all the list items
including their history is saved in this table. Documents uploaded to an item or documents in document library are also saved in this table.
The primary key(non clustered) of this table is formed with 7 columns

Columns

tp_ID[int]
>To identify the list item

tp_GUID[uniqueidentifier]
>To uniquely identify the list tiem

tp_ListId [uniqueidentifier]
To identify which list the item belongs to. It is the value of ‘tp_ID’ in ‘AllLists’ table

tp_SiteId [uniqueidentifier]
>To identify which site the item belongs to. It is the value of ‘Id’ in ‘AllSites’ table

tp_RowOrdinal [tinyint]
>zero based ordinal index in the set of rows representing the list item.

tp_Author [int]
>user identifier who created the list item.

tp_Editor [int]
>user identifier who last edited the list item.

tp_Modified [datetime]
>The date and time (in UTC format) when this list item was last modified.

tp_Created [datetime]
>The date and time (in UTC format) when this list item was created.

tp_DeleteTransactionId [varbinary]
>An identifier for implementation specific deleted items recycle bin. This will be equal to 0x if the list item is nondeleted.

tp_IsCurrentVersion [bit]
>bit represent if row belongs to a current version or an historical version of the list item. This value must be 1 if this row contains a current version else it will be 0

nvarchar1..64 [nvarchar]
>Columns navarchar1,navarchar2….navarchar64 stores values of application fields of type nvarchar.The 64 columns are named nvarchar1 to nvarchar64. If the column does not contain data, this value will be NULL.

ntext1..32 [ntext]
>Columns that store values of application fields of type ntext. The 32 columns are named ntext1 to ntext32. If the column does not contain data, this value will be NULL.

bit1..16[bit]
>Columns that stores values of application fields of type bit. The 16 columns are named bit1 to bit16. If the column does not contain data, this value will be NULL.

datetime1..8 [datetime]
>Columns that saves values of application fields of type datetime. The 8 columns are named datetime1 to datetime8. If the column does not contain data, this value will be NULL.

float1..12 [float]
>Columns that saves values of application fields of type float. The 12 columns are named float1 to float12. If the column does not contain data, this value will be NULL.

int1..16 [int]
>Columns that save values of application fields of type int. The 16 columns are named int1 to int16. If the column does not contain data, this value will be NULL.

sql_variant1..8 [sql_variant]
>Columns that save values of application fields of type sql_variant. The eight columns are named sql_variant1 to sql_variant8. If the column does not contain data, this value will be NULL.

A custom list SalesInfo is created.
Its tp_ID value form AllItems table is 1FB218D6-8C61-45A5-B30D-32EF4F8087AC
Schema of the custom list ‘SalesInfo’
‘Title’ field – single lie of text
‘ProductType’ field – Choice type (values Hardware,Software)
‘ProductCompany’ field – single line of text
‘Comments’ field – single line of text

I have created 2 values in the list. Following is the list image




Now we will check AllUserData table




It has two rows inserted with tp_ListId which is 1FB218D6-8C61-45A5-B30D-32EF4F8087AC the value from AllItems table.

Now we will see the values in the same rows in different columns





The title value is saved in ‘nvarchar1’ column.
The choice values are saved in ‘navarchar3’ column.
Another sigle line of text(ProductCompany) value is saved in ‘nvarchar4’ column.
The Comments field value is saved in ‘nvarchar5’ column for one item and for another item value is null.

Every time we create a column in SharePoint list, internally it will check the available free columns in AllUserdData table and map to them. Once mapped, SharePoint will save information in that column. It is more complex to discuss what if all the columns are full and how SharePoint is handling this.

Check the tp_DeleteTransactionId,it is ‘0x’; that means that it is not deleted.
Now I have deleted an item, it looks like the following image





When any user restores from recycle bin, its tp_DeleteTransactionId will be back to ‘0x’

Conclusion

We can also write your own sql queries on this table to get specific list data, but remember any changes are not supported by Microsoft.We have to respect the fact that we should not modify content DB.
When you write a CAML query on any list, internally it will execute sql query on this table with conditions.
If you have planned to write custom queries on this table, you should be well aware of the where conditions as there is no specific document explaining the internals of content DB.

Hope you enjoyed the article to understand what is happening when a list and item is created, though the intention is not to modify the content DB.

December 19, 2012 В· Adi В· 13 Comments
Tags:  В· Posted in: Sharepoint 2007, Sharepoint 2010, SharePoint 2013

13 Responses

  1. Manish - December 19, 2012

    Thanks Adi,

    For explaining such difficult internals in such simplified manner.

    MSDN should include this article as reference article because it really clarifies how share point really work with Lists.

  2. Adi - December 19, 2012

    Good to hear that :)
    There are lot of assumptions by many users on this topic in-spite of fact data. There is also no proper documentation on content DB internals. Hope this help users to understand what is going on

  3. Avinash - December 19, 2012

    Nice information Adi!

  4. Adi - December 19, 2012

    Thanks Avi

  5. Mathesh - January 15, 2014

    Thanks for you article. I need one more clarification on this. Each column is storing in nvarchar1, nvarchar2…so on. How to identify exact column name what we created in custom list. Please help me to explain this

  6. Adi - January 16, 2014

    Hi Mahesh,

    Fields related information, with list and what are the fields (columns) to that along with names is saved in tp_fields column in compressed format. Data will be saved in AllUserData table. In other words, tp_fields will have column names and nvarchar1…64 of AllUserData will have data of the tp_fields columns.
    My advise is not to code based on this tables schema. Always use object model or webservices to get data or schema of a list. Microsoft has many changes on these tables and columns data types from version to version. We never know this until we really check the table.

    Thanks and Regards,
    Adi

  7. Mathesh - January 16, 2014

    Thanks Adi

  8. Mohammed Kashif - April 15, 2014

    Hello Everyone,

    I want to get all the documents based on content type using SQL Server Query in Sharepoint. I know that, querying the content database without using API is not advisable, but still I want to perform this action through SQL Server Query. Can someone assist ?

    I need to execute this Query from java Application remotely. That’s the main reason i am looking for SQL Server Query. Can you please guide me with SQL Query ?

  9. Hank - September 10, 2014

    When a list has a people picker field with select multiple is allowed, where is that field information stored? I don’t see it in the AllUserData table.

    Really need help in finding that in the DB

  10. Sugeesh Ariyaratna - June 3, 2015

    Great post, found it very useful.
    Thanks…

  11. Carlos Negroni - June 15, 2015

    Thank you very much for sharing this information Adi. I know it is not recommended to query the content DB but I’m not a developer (so I can’t write a CLR Stored Procedure or something similar to get SP lists information) then this alternative is very useful for me.

  12. Adi - June 16, 2015

    Thanks mate. Happy to know that it helped you :)

  13. Gamal - November 5, 2015

    thanks for the useful article.
    When a list has a people picker field with select multiple is allowed, where is that field information stored? I don’t see it in the AllUserData table. Can you help me.
    Thanks in advance

Leave a Reply

What is 12 + 12 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)