Tuesday, October 20, 2009

Lists as Native SQL Tables in SharePoint 2010? #spc09


Even after today's keynote speech by Steve Ballmer and Tom Rizzo, I am wondering if this got included in SharePoint 2010?

I expected it to be one of the big announcements, but heard nothing about it. I also have seen nothing to indicate that it got added as I have played around with the Technical Preview version that we have had and were under NDA until today.

Why was I expecting this to be one of the big announcements? Only because at the SharePoint Conference in 2008 in Seattle it was promised at the keynote by Bill Gates and Tom Rizzo (the Director of SharePoint at Microsoft).

Why am I so anxious about this feature being included? The reason is that the guts of SharePoint (including document libraries) is built 100% on SharePoint List technology. And, it is the limitations of SharePoint List technology that has held back the possibility of porting thousands of applications around the world to SharePoint. What Gates and Rizzo talked about in 2008 seemed to open the door for the possibility for the bread-and-butter line of business (or vertical market) applications that are prevalent in industry after industry, to be ported to SharePoint.

Why would anyone want this to happen? Well, from a user perspective, in many organizations SharePoint and the Office client applications have been slowly but surely becoming the business application operating system - the way that Windows became the business application operating system many years ago. A big chunk of daily tasks now get done by rank and file users that are primarily using Office and SharePoint to get them done. Isn't the next logical step to port the line-of-business applications that they use to be native SharePoint applications in order to provide the best user experience?

The problem to-date is that there is not a one-to-one relationship between a SharePoint List and a native SQL Server table. Instead, there is a single (as in one) native SQL Server table that contains all of the list data for all lists in a SharePoint site collection. (In organizations that have SharePoint implemented in a single site collection, that means all field, row and list data is contained in a single SQL Server table.)

Most database-oriented people that have worked with SharePoint for a few years know that this is the case, but it is not something that is widely discussed and many newcomers to SharePoint don't know about this fact. So, just to drive this home, if you are in an relatively small organization that has say 75 sites in a site collection and an average of 6 lists in each site (task lists, announcement lists, links lists, etc, etc.) and an average of 60 rows in each list, that means that all of this data, a total of 27,000 records are all stored in a single SQL server table. Therefore if you assumed that the 450 lists in your site collection represent 450 SQL Server tables, you would have assumed wrong. All the rows for all 450 of these lists are stored in a single SQL Server table that just continues to grow and grow as you add more lists and rows.

Imagine what this SQL Server table looks like at a large multi-national corporation for one of their site collections? There might be millions and millions of records in this SQL Server table.

The bottom line to all of this is that SharePoint Lists are abstracted from the native SQL Server database. In other words, no matter how many lists you create and how many columns and records you add to them, all of that data is stored in a single SQL Server table that only SharePoint has the permission to write to directly.

Of course, this defies all of the rules of architecting fully normalized database structures. But, when SharePoint was conceived, Lists were not envisioned as being used just like relational database tables. The thought was that SharePoint Lists were just a better way to do everyday data tracking activities that users had previously relied on Excel spreadsheets for. Therefore a single table that is not normalized would be OK for SharePoint List data.

But now, the user interface of SharePoint has become so popular and the richness of the common SharePoint feature-set so powerful, that users are wanting more. Why shouldn't application developers be able to develop native relational database applications using SharePoint as the platform? Only one major reason that I can think of - SharePoint Lists are not available as native SQL Server tables that can support complex relationships, triggers and a host of other important functions that are needed to develop powerful database applications.

That gets me back to where I started. Is this feature (option to have native SQL Server table for a SharePoint List) really in SharePoint 2010 and I have just missed it? So far, I can't find it - and Bill Gates is now retired :( .

Important Update: New information related to this feature can be found in this new blog post. It definitely looks like the feature has been included in SharePoint 2010 - it just wasn't very apparent!
Post a Comment