SharePoint Lists plays a pivotal role in any SharePoint development primarily because it is the preferred source of data storage in SharePoint. All the great features like security permissions trimming, views, various types of columns, data validations, and entry forms that come out of the box and can be easily configured give SharePoint Lists an edge over any other data storage options. However, there are several scenarios that make SharePoint Lists less desirable especially when it comes to reporting. Reporting becomes very critical when we store business-critical data in SharePoint since it is vital for business users to make decisions. E.g. if you are storing customer orders in SharePoint Lists then soon you will need to develop some reports to get insights on your customer buying trends.
The more we use SharePoint for building business applications, the greater the need to generate reports on that data and that’s where SharePoint Lists has some serious limitations and could be troublesome to generate the desired reports. In this blog, we will cover some of the reasons why SQL Server database will be preferred over SharePoint Lists for reporting.
1. Lack of data relationships and Complex Data structure
The SharePoint Lists data structure is pretty good when it comes to flat lists that don’t have any relationships. However, any business application will involve several SharePoint Lists in which relationships are created using lookup columns. The more lookup columns you add, the more complexity you add to the data model. Apart from making the data model more complex, there is also a lack of data organization in terms of relationship types to consider. For instance, you cant define data relationships i.e. (one-many, many-many, many-one) which makes it hard to identify data relationships. This is essential to properly generate reports that involve data from multi-related SharePoint Lists or to create data model for data mining. That’s, where having your data in SQL Server tables allows you to define relationships the way they are meant to be and subsequently define realistic data models.
2. Large List (i.e. items > 5000) degrades performance
Most modern databases can easily store large amounts of data in tables without any hindrance. Unlike a database, SharePoint becomes tedious when it comes to handling a large no of records i.e. >5000 within a single list. Even the SharePoint product team advises limiting the number of items in a list to 5,000 for each list otherwise, the list performance significantly decreases. We all know, over time amount of data continues to grow and soon you will find yourself looking at lists surpassing the 5000 items mark. There are ways to manage Large Lists by creating filtered views but that doesn’t eliminate the performance degradation completely. Several reports may require a complete dataset for aggregation and that’s where SharePoint Lists will let you down compare to SQL Server database.
3. Limitations around Indexing
Indexing is one of the key features for improving query optimization which leads to improved performance. Setting up Fields that are frequently used such as Product Name, Quantity, Unit Price, etc as pre-indexed can result in the improved overall performance of reports. Databases can be optimized for data retrieval by using optimization techniques such as creating pointers to where data is stored in the database. Indexing in SharePoint is available for improving search of content stored in SharePoint; however, functionality is limited to 20 indexed columns in a list. This may be enough for some lists, however, if the list has a lot more columns then this limitation can be an obstacle to improving performance. There are no such limitations in SQL Server databases and you can set up as many columns as pre-indexed as you want.
4. Lack of OOTB Reporting Tool like SSRS
SharePoint brings us a huge list of OOTB features however a proper reporting tool is not one of them. This means that customers need to acquire and use an additional SharePoint reporting tool to generate some proper reports. On the other hand, SQL Server database in its enterprise edition and other iterations comes with a powerful reporting service (SSRS) which helps you gain insights from the data stored in SQL databases and even provides the flexibility to mix data from various other sources. Below are some of the main features of SSRS.
5. Lack of Data Compression and Encryption OOTB
Business-critical data often involves data encryption to make sure data remains secure. This is something that doesn’t come out of the box with SharePoint where the SQL server has a built-in transparent data compression feature along with encryption. This allows us to utilize encryption of data without using any 3rd party tool. The same goes for data compression as well which is supported by SQL Server natively.
6. SharePoint Query Language not as comprehensive as SQL
SharePoint uses KQL (Keyword Query Language) and CAML (Collaborative Application Mark-up Language) as query languages for data retrieval which can handle basic filtering and sorting but writing complex queries can be very challenging. SQL is one of the most popular query languages in the world which is also preferred and widely used by developers for writing complex and optimized queries compared to KQL or CAML. If performance is essential then SQL can produce much more efficient and optimized queries than SharePoint counterparts.
7. Lack of Integration with BI & Reporting Tools
You will hardly find any popular reporting tool that doesn’t natively support SQL Server as a data source whereas when it comes to SharePoint Lists as a data source, the list is very limited and you would also have to go through the supported features for each tool to make sure it fulfils your requirements. Here is a quick list of the most popular reporting tools that support SQL Server
Final Thoughts
SharePoint Lists definitely helps us speed up the development of business applications providing a lot of components out of the box however reporting is not something that comes under SharePoint Lists forte and a SQL Server is well equipped to fulfil all your reporting needs. Furthermore, you can use a hybrid approach by using SharePoint Lists and keeping the data synchronized in a SQL Server database.
AxioWorks SQList continuously export SharePoint lists and libraries as normalised SQL Server tables, making live SharePoint data available to reporting tools like Power BI, Crystal Reports, or SSRS.
AxioWorks SQList is a real-time SharePoint-SQL Server data replication and synchronization tool which can help you to set up this hybrid approach and get the best of both worlds by allowing users to use SharePoint Lists for data operations and keep the data auto-synchronized in a SQL Server database and seamlessly use the database for reports.