Lists as Native SQL Tables in SharePoint 2010? #spc09
Published 10/20 courtesy of SharePoint SolutionsEven after todays 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. Isnt 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 dont 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 right to directly. In other words, only SharePoint is allowed to understand and manage the abstraction. Data written to a list must go through the SharePoint API to be written to the database - developers are not allowed to write applications inside or outside of SharePoint that write directly to the master SharePoint List table in SQL Server.
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 shouldnt 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 cant find it - and Bill Gates is now retired :( .
Recent SharePoint Questions
- Customize "Send To" menu in Sharepoint 2007
- Site is opening only on Server but not on other clients. How to fix this?
- Handy & Free of Charge SharePoint Tools
- One Document - two sites
- What is the difference between a document library and a form library?
- What is Collaborative Application Markup Language?
- • What is the difference between SharePoint Portal Server and Windows SharePoint Services?
- Why should you use SharePoint?
- Displaying columns
- Best way to apply permission and access
more sharepoint questions
More Articles By
Develop Mobile Applications for SharePoint with Mobile Entree - CMSWire
Develop Mobile Applications for SharePoint with Mobile Entree
CMSWire, CA
By Barb Mosher | Jun 5, 2009 Seeing as how SharePoint (news, site) is so widely used within the enterprise today, it's…
Bamboos Year in Review: Marc OBrien Introduces the Bamboo Online Applications Division
Editor's note: Last year we introduced the Bamboo Year in Review feature, kicking off with a note
While writing the final sentences of my post on how to create a SharePoint blog last week, I realized that I needed to circle back and spend some time… I was asked the other day if there might be a slick CSS method for getting long column names to wrap in a SharePoint list’s new item form (NewForm.aspx). The… I’ve talked to a number of folks in recent months that have wanted to add the links stored in a user’s My Links list in their My Site to other… For a couple of reasons, Mission: Automation – SharePoint Workflow and InfoPath is one of my favorite classes to teach.Working with the Admin Links on your SharePoint Blog
More Articles Under "Articles"
How to get column names to wrap in #SharePoint new item forms (NewForm.aspx)
The My Links Web Part – It’s Not Just for My Sites #sharepoint
SharePoint Workflow and InfoPath Training… Online! #sharepoint
Most Viewed Content
- Develop Mobile Applications for SharePoint with Mobile Entree - CMSWire
- Bamboos Year in Review: Marc OBrien Introduces the Bamboo Online…
- Working with the Admin Links on your SharePoint Blog
- This Week in Bamboo (June 28th - July 4th, 2009)
- Cisco Plans Office-Like Service, Andreessen Begins a Silicon Valley Venture…
