This is a very simple article about obtaining the next id for a Sharepoint list in Sharepoint 2010. The reason why I am writting this is because I have recently been involved in a upgrade project from 2007 to 2010. As part of their current SP2007 environment they have a few number of customizations that had to be migrated, one of them was a custom workflow that at a certain stage would require to calculate the next available id in a Sharepoint list

How this was achieved was by querying the content database and getting the value of the tp_NextAvailableId field of the AllLists table. Following is an example of the SQL query that would be used in SP2007 to retrieve this information:

SELECT tp_NextAvailableId FROM AllLists where tp_ID='{Id of the List}'

Now I am not saying this is the right thing to do, you can run into all sorts of concurrency problems if querying this field, especially from a workflow activity as this old piece of code was doing. However, after migrating the site we saw that the workflow was no longer executing and was throwing an invalid SQL exception. This is one of the reasons why you should never access directly the content database from your custom features. It turns out the database schema has significantly changed in 2010 and this query was no longer valid.

What we did in this case was replace the SQL query by one that would run properly in a Sharepoint 2010 environment. However I strongly advise you to review your solution if you find in the need of running a similar query in your code. By coupling your code to the Sharepoint database schema not only will cause trouble when migrating to new versions of the platform but also when deploying product Service Packs. In Sharepoint 2010 compatibility between a database schema and object model is guaranteed only between consecutive releases of Service Packs.

SELECT NextAvailableId FROM AllListsAux where ListID='{Id of the List}'

Created on 28/03/2011