Take 2: Lists as Native SQL Server Tables in SharePoint 2010? #spc09 #SP2010

Last week, while at the SharePoint Conference in Las Vegas, I wrote a blog post on being disappointed that SharePoint 2010 does not seem to include a critical feature promised by Bill Gates and Tom Rizzo in their keynote speech at last year's (2008) SharePoint Conference.

The feature promised was a one-to-one relationship (as needed for specific applications) between a SharePoint List and a native database table (SQL Server, Oracle, etc.). I explain in my first blog post why I think this is an extremely important feature to have.

Turns out that this feature IS included in SharePoint 2010! It is just that it did not receive the level of coverage in this year's keynote speech that I was expecting based on the expectation that Microsoft built up in last year's keynote speech. So, I missed it even though I was sitting in the audience during last week's keynote.

I discovered that the new feature exists (and truly looks outstanding) after getting back home and starting to watch some of the recorded breakout sessions that I didn't get a chance to attend.

The specific new features that enable this are named External Content Types and External Lists. These two new features fall into the overall category of the new Business Connectivity Services feature-set of SharePoint 2010.

The way this works is that you can now create (using SharePoint Designer 2010 or Visual Studio 2010) External Content Types which are really just a definition of an external database table (or web service). Once an External Content Type is defined for the external database table, an External List can be created (using the SharePoint 2010 browser UI, SharePoint Designer 2010 or Visual Studio 2010). The External List behaves just like any first-class List in SharePoint 2010. Anything you can do with a native List in SharePoint 2010 (workflows, permissions, views, etc.) can be done with External Lists.

But, the data that is displayed and stored for an External List is not stored in the SharePoint 2010 content database. The data in the External Lists lives in the external database table - and, only there. Therefore, if it is updated by a non-SharePoint application (think an ERP system or CRM system for instance), the data that is displayed in the SharePoint External List will update immediately.

This is totally different from the way the MOSS 2007 Business Data Catalog worked. In the MOSS 2007 BDC, the business data brought into SharePoint was stored in a List column(s) in the SharePoint Content Database. So, if the data changed in the external database, the values in the corresponding SharePoint List did not get updated immediately.

Futhermore, in the MOSS 2007 BDC it was not really possible to update data in the external database from within SharePoint. With External Content Types and External Lists in SharePoint 2010, all of that has changed. If you change a value of a column in a SharePoint 2010 External List, the new value is immediately written to the external database.

The last, but certainly not the least, important announcement is that External Content Types and External Lists are "baked-in" to SharePoint 2010 Foundation (the new name for WSS 4.0). This means that all of this goodness is now available to any organization that chooses to use SharePoint 2010, regardless of whether you are using a free or premium version of SharePoint.

Very exciting!