More On SharePoint Modified Field

I discussed the SharePoint "Modified" and "Last Modified" fields in my previous post. During the test I have an interesting finding on how update a list item will affect the "Modified" field. That's where this post is coming from.

In SharePoint there're two methods you can call to update a list item (SPListItem): SystemUpdate and Update. Following are their MSDN description(http://msdn.microsoft.com/en-us/library/ms438065.aspx):

• SystemUpdate: Updates the database with changes that are made to the list item without changing the Modified or Modified By fields.
• Update: Updates the database with changes that are made to the list item. (Overrides SPItem.Update().)

Obviously SystemUpdate method won't update the "Modified" field. What about the "Last Modified" field? Let's do a quick test:

public partial class Program
{
static void Main(string[] args)
{
   string siteName = "http://localhost";
   string listName = "Documents";
   string itemInfo = string.Empty;

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.None);
   Console.WriteLine("--Orignal--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.SystemUpdate);
   Console.WriteLine("--After SystemUpdate--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();
   System.Threading.Thread.Sleep(2000);

   itemInfo = TestItemLastModifiedTime(siteName, listName, ExecutCommand.Update);
   Console.WriteLine("--After Update--" + System.Environment.NewLine + itemInfo);
   Console.WriteLine();

   Console.Read();
}

enum ExecutCommand
{
   None,
   SystemUpdate,
   Update
}

private static string TestItemLastModifiedTime(string siteName, string listName, ExecutCommand command)
{
   using (SPSite site = new SPSite(siteName))
   {
       using (SPWeb web = site.OpenWeb())
       {
           SPList list = web.Lists[listName];
           SPListItem item = list.Items[0];
           switch (command)
           {
               case ExecutCommand.None:
                   return GetModifiedInfo(item);
               case ExecutCommand.SystemUpdate:
                   item["Title"] = item.Title.Trim();
                   item.SystemUpdate(true);
                   break;
               case ExecutCommand.Update:
                   item["Title"] = item.Title.Trim();
                   if (item.File.CheckOutStatus == SPFile.SPCheckOutStatus.None)
                       item.File.CheckOut();
                   item.Update();
                   break;
               default:
                   return string.Empty;
           }
           return GetModifiedInfo(item);
       }
   }
}

private static string GetModifiedInfo(SPListItem item)
{
   DateTime modified = Convert.ToDateTime(item["Modified"]);
   DateTime lastModified = Convert.ToDateTime(item["Last_x0020_Modified"]);
   string newline = System.Environment.NewLine;
   string msg = string.Format("SPListItem Modified: \t\t{0}{1}SPListItem LastModified: \t{2}",
           modified.ToString("yyyy-MM-dd HH:mm:ss"), newline, lastModified.ToString("yyyy-MM-dd HH:mm:ss"));
   return msg;
}
}

The result:

--Orignal--
SPListItem Modified:            2009-07-02 11:33:20
SPListItem LastModified:        2009-07-02 11:33:20

--After SystemUpdate--
SPListItem Modified:            2009-07-02 11:33:20
SPListItem LastModified:        2009-07-02 11:33:20

--After Update--
SPListItem Modified:            2009-07-03 21:15:16
SPListItem LastModified:        2009-07-02 11:33:20

It clearly shows that both SystemUpdate and Update Methods don't have any impact on "Last Modified" field.

All SharePoint data, including all version data, are stored in AllUserData table in the SharePoint backend content database. It looks like that both "Modified" and "Last Modified" time are from "tp_Modified" column in AllUserData table: "Modified" from the latest version while "Last Modified" from the last major version. You can verify this by checking the content database:

SELECT tp_Modified, tp_UIVersionString FROM AllUserData
WHERE tp_DirName = 'Documents' AND tp_LeafName = 'MyDocument1'
ORDER BY tp_Modified DESC

--return:
tp_Modified   tp_UIVersionString
2009-07-02 11:33:20.000  5.1
2009-07-03 21:15:16.000  5.0
2009-06-15 17:33:59.000  4.1