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