"SharePoint is not a database!" article of 2009 - does it still apply for SP 2010 and 2013?

Question

I'm talking about this blog post by Chris Woodill.
He mentions the following advantages of modeling data via SQL programming instead of SharePoint's lists:

1. Primary And foreign keys: one of the most basic concepts in a database is the enforcement of a unique identifier (either system
   generated or user supplied) that can identify a record and link
   across table in parent child relationships.
2. Transactions: if two changes are required to go together and the second change fails you want the first change to be undone (rolled back).
3. SQL language: complex queries can me written in code using a standard language. Queries can be saved and repurposed.
4. Stored procedures: database programmers can write complex routines that can be called as reusable code blocks.
5. Indexing and query optimization: used to improve performance, indexing allows the database designer to pre-index specific fields
   that are frequently used in queries to improve performance. In
   addition, most databases have optimization engines that based on what you are trying to fetch will optimize how the data is retrieved.
6. Large data and binary fields: most modern databases allow you to store large binary files such as video files, large volumes of text, images, etc. in the database. SharePoint can be made to store large files as documents but you only get one per record and a generic binary object.
7. Access outside of SharePoint: lists are accessible outside of SharePoint but only through XML or programming interfaces. Lists are not great data stores to be used for line of business applications written independently of SharePoint because the integration is relatively poor in comparison to running a SQL query on a database.
8. Cascading deletes: if a parent record is deleted, its children should also be deleted. In most databases, this can be configured to happen automatically or else reject the delete of the parent until the children are deleted first. This eliminates the risk of orphan child records.

Are all these 8 problems still present in SP 2010 and 2013? Which have been solved?

Answer 1

As James mentions it's not a valuable comparison as it's two completely different tools. No "SharePoint is not a database" and no "SQL is not a collaboration, document management, ... platform".

But to answer your question only point 7 and 8 has been changed (fixed) in SP2013.
For more information: Here is my take on the eight points:

1. Primary and foreign keys: SharePoint has always had ID as primary key and lookup fields as foreign keys. Nothing has changed in 2010/2013 (except support for cascading deletes, but that's point 8)

2. Transactions: No support for this possible very important feature in SharePoint. This simply isn't possible with all of the features SharePoint support around updates: Event Receivers, Workflows, Alerts. There is no way to rollback changes.

3. SQL language: Still no support for SQL in SharePoint, but lots of other languages for queries: CAML, OData (new in SP2010) and search queries

4. Stored procedures: Well that's just part of SQL. SharePoint have lots of more advanced possibilities for writing reusable functionality

5. Indexing and query optimization:" SharePoint has some limited support for making fields indexed. The query optimization is in fact done by SQL when you're querying SharePoint.

6. Large data and binary fields: You've always had support for large files in SharePoint, but you have to decide if you want your "record" to be a single document or an item which can have multiple files attached. In fact I'd say SharePoint is a lot better at handling file content than SQL databases

7. Access outside of SharePoint: The new REST APIs (SP2010) makes SharePoint lists a lot more accessible for outside programs than before and is probably a lot better than letting these programs access your SQL database directly.

8. Cascading deletes: Is supported by SharePoint as of SP2010

Answer 2

James is so right, it's not SharePoint vs SQL Server, it's a matter of using the right tool for the right business needs but to go deeper in these assumptions

Cascading Delete / Primary & Foreign Keys are available since SP2010 and handled through built-in features like :

• Unique Column policy
• Joins between lists
• Projected Fields
• Relational integrity

(note that relationship was already possible back in 2007 through the lookup fields). Don't forget to have a look at that article to get a clearer view of the concepts : http://msdn.microsoft.com/en-us/library/ff798514.aspx

Large data and binary fields : No issue here, it's just a matter of knowing what will be done with these large data. What's the point of having them stored inside your database if you don't use SharePoint functionalities (eg : versioning) ? You can easily offload them from SQL Server with Remote Blob Storage configuration and you can cache them on the WFEs with the Blob cache to avoid uneeded roadtrips with the database

Access outside of SharePoint: This is an area that has been really improved in 2010 and now even better in 2013. You have the Client Object Model, the REST Interface, the Web API services, the classic web services & WCF services, the RSS interfaces, ... It's very easy to get data out of SharePoint in nearly any format, ready to be consumed (but most of the time you'll need to be authenticated to retrieve the content)

Indexing and query optimization, SQL Language : you can handle indexes straight from SharePoint interface for large lists management. For query optimizations, nothing can beat handcrafted optimized SQL queries but you're not supposed to query the SP databases without going through CAML (even if you could hide it by using SPMetal but beware of the queries called). In SharePoint, never forget about the importance of the Search topology & architecture. It will allow you to get nearly any amount of data in a few milliseconds (once indexed). Much better & faster than most of the most optimized queries !

In all situations, don't forget that you can have separate SQL databases that will be consumed by SharePoint throught the Business Connectivity Service and that will allow you to have full control of your data structure, stored procedures & optimizations while keeping the benefits of SharePoint for surfacing / working with these data. That would be the best solution if you need transactions for example (which are not part of the deal without a bunch of additional work)

So, in short, nearly all the issues are non existing (or simply never were). Make sure that you know what SharePoint is, how you can configure / optimize it to fit the business requirements and don't try to make it the perfect solution that will fit all business requirements.

Answer 3

Apologies if this doesn't directly answer your questions, but I think the main point to be made here is that SQL databases and Microsoft SharePoint solve two completely different issues.

Can you store documents and collaborate with people using SQL out of the box?

Can you find colleagues with similar skills, interests who have uploaded documents you are interested in, with SQL out of the box?

Can you connect data directly to Outlook so you can track information whilst away from the office, with SQL out of the box?

Can you assign tasks to lots of individuals across many different projects and view a list of all the tasks assigned to you in a single location? Without ANY customisation of the interface, in SQL?

There are far many more scenarios in which SharePoint is a more viable solution than SQL on it's own. There are also many in which SQL is more appropriate than just SharePoint.

When deciding in which is more appropriate, always look at your business problems and goals first before looking at which platform is more appropriate. Once you know what you need, you can then go through the capabilities of both and start ticking boxes.