0

So I'm having some performance issues with some large queries against SharePoint (yes, I'm already using CAML), so I decided to take a peek at the SharePoint database despite the dire warnings never to do so. I expected list item data to be stored in columns like Datetime1, Datetime2, etc, but instead discovered (to my horror), that list column data seems to be stored in XML in the AllUserData.tp_ColumnSet column. 

<datetime2>2010-12-21T05:00:00</datetime2>
<int2>1926</int2>
<nvarchar1>i:0#.w|username</nvarchar1>
<nvarchar2>i:0#.w|username</nvarchar2>
<nvarchar3>doc</nvarchar3>
<nvarchar7>BLUES</nvarchar7>
<nvarchar10>K7WP3XQJ2JDF-13-19431</nvarchar10>
<nvarchar11>/_layouts/15/DocIdRedir.aspx?ID=K7WP3XQJ2JDF-13-19431</nvarchar11>
<nvarchar12>K7WP3XQJ2JDF-13-19431</nvarchar12>
<nvarchar27>SOLD</nvarchar27>

Am I looking in the wrong place? Is there some setting causing my data to be stored like this?

Improve this question

1 Answer 1

Reset to default
2

This is an architectural change in 2013. See: http://joelblogs.co.uk/2013/02/15/sharepoint-2013-content-databases-and-the-alluserdata-table/

EDIT: The article above has been updated/corrected. Thanks Mike D.!

Improve this answer
2
  • This design change is mindbogglingly bad. Take all the benefits of using a relational database and throw them out the window by serializing to XML first and storing in a single column.
    – MgSam
    Commented Jul 22, 2013 at 12:34
  • 2
    Sharepoint isn't actually serializing the data to XML. SQL Server is using sparse columns and returning a column set: msdn.microsoft.com/en-us/library/cc280604.aspx
    – Mike D.
    Commented Aug 31, 2015 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.