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!


Anonymous said...

Very lengthy poor blog.You are mistaken: http://msdn.microsoft.com/en-us/library/ee231515(VS.100).aspx

Unknown said...

I remember that announcment way back when. I think the promise is realized a bit differently, YOU create the database table then use BCS and external content types to turn it into a SharePoint list as opposed to going the other way around (creating a list that's backed by a database table). The result seems pretty much the same to me.

Jeff Cate said...

Mr. Anonymous, I can agree with one of your points - the blog post is very lengthy. I didn't have much time after spending the entire day at the conference.

As far as the blog post being "poor", every man (or woman) is entitled to their opinion.

On me being mistaken and the MSDN article you point to, I don't think so.

We are the only company in the world that offers a public training class specifically on the BDC and I can tell you that if have done any real work with the BDC you are acutely aware that it is not a solution for what I am talking about in this blog post.

Jeff Cate said...


You may be right, but only time will tell. I say this because the BDC in MOSS 2007 was not functional enough to deliver on this promise. Whether the new functionality in the BCS in SharePoint 2010 will take us there remains to be seen.

I do know that what was promised with the MOSS 2007 BDC back in 2007 didn't really play out fully in real life. The features were just not as well done as the marketing initially led us to believe.

Conrad said...

Jeff, I wholeheartedly agree with you. My #1 gripe with SharePoint is that Microsoft gives you most of what you would need to replace a line of business app, but then they say "don't do it." Does anyone out there really love Lookup columns?

Another issue with the BDC is that in a large environment, where configuration change requests must be submitted to a committee, you simply won't have the flexibility to add new definitions whenever you want.

Unknown said...

This looks something like what we're talking about, doesn't it?


The article discusses the presentation features of SharePoint 2010 Server, specifically about External Content Types and External Lists.

I've yet to do this excercise myself yet.

Jeff Cate said...

SpoonsJTD and Tony Bierman are both correct on this one. There are a couple of recorded breakout sessions from the SharePoint Conference that I just got a chance to watch. External Content Types and External Lists (two new SharePoint 2010 features) look like they fulfill this promise.

For more details, read this follow-on blog post.

Startup Cohorts said...

The Primary reason for organisations to store data in sharepoint list is to use the platform capabilities that are available Out of the Box. E.g. If i had to consume the workflow features of SharePoint that triggers on a List.
2nd Feature is to be used is the document Libraries

3rd Feature is the Searching Capabilities.

That brings to an interesting question from the SQL guys, that management of business critical data should be out of SharePoint List in perfectly relational tables, where the administration of the Database can be taken up quite well by the DBAs. Backup, Storage, Optimisation, User Management, Indexing, Reporting and so on and so forth.

The Moment you bring the data into SharePoint list, we are taking away some of those management capabilities for SQL Database and Tables.

yes you gain some, in terms of taking the IT work load of SharePoint List management and put it in to the Business Users. But are all business users capable of managing business data in SharePoint List.

Good to see comments on this.