You are hereSharePoint v3: tp_DeleteTransactionID


SharePoint v3: tp_DeleteTransactionID


By hagrin - Posted on 25 February 2007

When working with SharePoint v3 list data, it's important to understand the data structure used to store list items. List data is stored in the AllUserData table and the name should be your first indication that you might run into a problem if trying to pull or manipulate data stored in this table. Why? Well, "all" user data is stored in that table including deleted data and you will need to know how to filter out deleted items and that is where the tp_DeleteTransactionID column comes into focus.

The tp_DeleteTransactionID column is a varbinary field so you cannot readily read the contents of that column in SQL Server Enterprise Manager. This coulmn acts as a "deleted flag" and you will need to be able to know what items you have removed from your list, but you forgot to remove from your Recycle Bin. List items that you delete from your list, but that remain in your Recycle Bin will have the value of the tp_DeleteTransactionID column updated from the default value of 0x. Once you remove the items from your Recycle Bin, the item's corresponding rows will be deleted from the AllUserData, but until you do so those item's rows will remain in the AllUserData table.

So, if you want to filter out deleted content for the purpose of data manipulation or transformation, but you don't want to have to worry that the user has remembered to empty the Recycle Bin, add the following WHERE clause to your SQL statements -

WHERE tp_DeleteTransactionID = 0x

Notice, that the value 0x is not surrounded by value defining apostrophes since the value is not a true string, numeric, date, etc. value, but a hex representation for the varbinary field.