SharePoint 2013 list in SQL

Question

I have a couple of questions:

1. Where does SharePoint list stores data in SQL?
2. Is there article for SP2013 on MSDN or TechNet? - I can find outdated articles which explain table structure for a list but those articles are not maintained anymore by Microsoft hence this question.
3. Can I access data directly and is it recommended?

I found SharePoint List Query Designer and Text-based Query Designer User Interface which are providing an option to query a list like a table. Has anyone explored these tools?

Answer 1

Where does SharePoint list stores data in SQL?

In SharePoint Content DataBase at AllLists INNER JOIN AllUserData ON AllLists.tp_ID = AllUserData.tp_ListId where the data stored at AllUserData.tp_ColumnSet column as XML

---

Can I access data directly and is it recommended?

No, it's not recommended and not supported and I advise you to don't touch the SharePoint database to don't lose the Microsoft support and to avoid DB locks

unsupported database changes include, but are not limited to, the following:

• Adding database triggers
• Adding new indexes or changing existing indexes within tables
• Adding, changing, or deleting any primary or foreign key relationships
• Changing or deleting existing stored procedures
• Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
• Adding new stored procedures
• Adding, changing, or deleting any data in any table of any of the databases for the products that are listed in the "Applies to" section
• Adding, changing, or deleting any columns in any table of any of the databases for the products that are listed in the "Applies to" section
• Making any modification to the database schema
• Adding tables to any of the databases for the products that are listed in the "Applies to" section Changing the database collation
• Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
• Enabling SQL Server change data capture (CDC)
• Enabling SQL Server transactional replication
• Enabling SQL Server merge replication

Supported database modifications

• Exceptions to the prohibition against database modifications are made for specific usage scenarios:
• Operations that are initiated from the SharePoint administrative user interface
• SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
• Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation
• Activities that are in compliance with the SharePoint Protocols documentation

For more details Support for changes to the databases that are used by Office server products and by Windows SharePoint Services

---

Regarding SharePoint List Query Designer and Text-based Query Designer User Interface which are providing an option to query a list like table. Has anyone explored these tools?,

It's supported tools, Because it doesn't retrieve the data from SQL content Database directly

Answer 2

All list data is stored in AllUserData; the specific user enterable values are stored in the tp_ColumnSet field. More information can be found at AllUserData Table, part of the Protocol documentation. This document is current.

It is not supported to query these tables directly as you may cause unintended locks.