There's another hidden "Modified" field, also known as "Last Modified" field with internal name of "Last_x0020_Modified". What's difference? Following are their official description from MSDN:
- Modified: Identifies a field that contains the last modified date and time information that is associated with the specified SharePoint Foundation object.
- Last_x0020_Modified: Identifies a field that contains version control information for the last modified version of the specified SharePoint Foundation list object.
<Field ID="{28cf69c5-fa48-462a-b5cd-27b6f9d2bd5f}"Using SharePoint Manager we can see the raw "Modified" and "Last Modified" value (XML) stored in a list item:
Name="Modified"
SourceID="http://schemas.microsoft.com/sharepoint/v3"
StaticName="Modified"
Group="_Hidden"
ColName="tp_Modified"
RowOrdinal="0"
ReadOnly="TRUE"
Type="DateTime"
DisplayName="$Resources:core,Modified;"
StorageTZ="TRUE">
</Field>
<Field ID="{173f76c8-aebd-446a-9bc9-769a2bd2c18f}"
Name="Last_x0020_Modified"
SourceID="http://schemas.microsoft.com/sharepoint/v3"
StaticName="Last_x0020_Modified"
Group="_Hidden"
ReadOnly="TRUE"
Hidden="TRUE"
DisplayName="$Resources:core,Modified;"
Type="Lookup"
List="Docs"
FieldRef="ID"
ShowField="TimeLastModified"
Format="TRUE"
JoinColName="DoclibRowId"
JoinRowOrdinal="0"
JoinType="INNER">
</Field>
<?xml version="1.0" encoding="utf-16"?>As we can see "Modified" field is of DateTime type and real DateTime is stored in SharePoint content database; on the other hand, "Last_x0020_Modified" field is a lookup column pointing to "Docs" list's "TimeLastModified" column.
<z:row xmlns:z="#RowsetSchema"
ows_Modified="2009-06-15 11:23:13"
ows_Last_x0020_Modified="23;#2009-06-15 11:23:13"
... />
With further investigation, I found that this "Docs" list and the "TimeLastModified" field do not really exist. They are virtual and the values are determinted at run time. I tried to figure out what SharePoint exactully is doing to obtain this lookup value. So I did a quick test on these two fields:
public partial class ProgramThe code is simple. I use CAML query to get the "Modified" and "Last Modified" data without any filter. I saw two quries sent to SharePoint content database for such CAML query. The first one is:
{
static void Main(string[] args)
{
string siteName = "http://localhost";
string listName = "Documents";
using (SPSite site = new SPSite(siteName))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[listName];
DateTime modifiedTime, lastModifiedTime;
SPQuery query = new SPQuery();
query.ViewAttributes = "Scope='Recursive'";
query.ViewFields = "<FieldRef Name='Modified' Nullable='TRUE'/>"
+"<FieldRef Name='Last_x0020_Modified' Nullable='TRUE'/>";
SPListItemCollection items = list.GetItems(query);
modifiedTime = Convert.ToDateTime(items[0]["Modified"]);
lastModifiedTime = Convert.ToDateTime(items[0]["Last_x0020_Modified"]);
Console.WriteLine("Modified: " + lastModifiedTime.ToString()
+ " Last Modified: " + lastModifiedTime.ToString());
}
}
Console.Read();
}
}
Notice two GUIDs passing in this query do not match the ID of "Modified" or "Last Modified". The query returns:
exec proc_GetListFields '2E1D8267-FBA0-4995-8CBB-08E747FB54D7','750DB8DD-A6AA-49B5-9D23-EB6E4B95EAD7'
<FieldRef Name="ContentTypeId" />
<FieldRef Name="Title" ColName="nvarchar1" />
<FieldRef Name="_ModerationComments" ColName="ntext1" />
<FieldRef Name="File_x0020_Type" ColName="nvarchar2" />
<FieldRef Name="Name" ColName="nvarchar3" />
<FieldRef Name="EMail" ColName="nvarchar4" />
<FieldRef Name="Notes" ColName="ntext2" />
<FieldRef Name="SipAddress" ColName="nvarchar5" />
<FieldRef Name="Locale" ColName="int1" />
<FieldRef Name="CalendarType" ColName="int2" />
<FieldRef Name="AdjustHijriDays" ColName="int3" />
<FieldRef Name="TimeZone" ColName="int4" />
<FieldRef Name="Time24" ColName="bit1" />
<FieldRef Name="AltCalendarType" ColName="int5" />
<FieldRef Name="CalendarViewOptions" ColName="int6" />
<FieldRef Name="WorkDays" ColName="int7" />
<FieldRef Name="WorkDayStartHour" ColName="int8" />
<FieldRef Name="WorkDayEndHour" ColName="int9" />
<FieldRef Name="IsSiteAdmin" ColName="bit2" />
<FieldRef Name="Deleted" ColName="bit3" />
<FieldRef Name="Picture" ColName="nvarchar6" ColName2="nvarchar7" />
<FieldRef Name="Department" ColName="nvarchar8" />
<FieldRef Name="JobTitle" ColName="nvarchar9" />
<FieldRef Name="IsActive" ColName="bit4" />
The second query sent to database is massive:
exec sp_executesql N' SELECT TOP 2147483648 t2.[tp_Created] AS c3c8,t1.[Type] AS c0,t1.[TimeLastModified] AS c9,t3.[tp_ID] AS c10c5,CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N''/'' + t1.LeafName END AS c11,t1.[ScopeId] AS c16,UserData.[nvarchar4],UserData.[tp_CheckoutUserId],UserData.[tp_Version],t1.[Id] AS c15,t2.[nvarchar5] AS c3c7,t3.[nvarchar1] AS c10c4,UserData.[tp_HasCopyDestinations],UserData.[tp_ModerationStatus],UserData.[tp_Level],t2.[nvarchar1] AS c3c4,t2.[nvarchar4] AS c3c6,t3.[nvarchar4] AS c10c6,t3.[tp_Created] AS c10c8,t1.[MetaInfo] AS c14,t1.[LeafName] AS c2,UserData.[tp_Modified],UserData.[nvarchar3],t2.[tp_ID] AS c3c5,t3.[nvarchar5] AS c10c7,UserData.[tp_ID],t1.[ProgId] AS
c13,UserData.[tp_CopySource],t1.[TimeCreated] AS c1,UserData.[tp_Editor],t1.[IsCheckoutToLocal] AS c12 FROM UserData INNER MERGE JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1 AND UserData.[tp_RowOrdinal] = 0 AND t1.SiteId = UserData.tp_SiteId AND t1.SiteId = @L2 AND t1.DirName = UserData.tp_DirName AND t1.LeafName = UserData.tp_LeafName AND t1.Level = UserData.tp_Level AND (UserData.tp_Level = 255 AND t1.LTCheckoutUserId =@IU OR (UserData.tp_Level = 1 AND (UserData.tp_DraftOwnerId IS NULL OR (UserData.tp_DraftOwnerId <>@IU AND 1=0 )) OR UserData.tp_Level = 2 AND (UserData.tp_DraftOwnerId = @IU OR 1=1 )) AND (t1.LTCheckoutUserId IS NULL OR t1.LTCheckoutUserId <> @IU )) AND (1 = 1)) LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) AND t2.[tp_CalculatedVersion] = 0 AND t2.[tp_DeleteTransactionId] = 0x AND t2.tp_ListId = @L3 AND UserData.tp_ListId = @L4) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_CheckoutUserId]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) ) AND t3.[tp_CalculatedVersion] = 0 AND t3.[tp_DeleteTransactionId] = 0x AND t3.tp_ListId = @L3 AND UserData.tp_ListId = @L4) WHERE (UserData.tp_Level= 255 AND UserData.tp_CheckoutUserId = @IU OR ( UserData.tp_Level = 2 AND UserData.tp_DraftOwnerId IS NOT NULL OR UserData.tp_Level = 1 AND UserData.tp_DraftOwnerId IS NULL ) AND ( UserData.tp_CheckoutUserId IS NULL OR UserData.tp_CheckoutUserId <> @IU)) AND UserData.tp_SiteId=@L2 AND (UserData.tp_DirName=@DN) AND UserData.tp_RowOrdinal=0 AND (t1.SiteId=@L2 AND (t1.DirName=@DN)) ORDER BY t1.[Type] Desc,UserData.[tp_ID] Asc OPTION (FORCE ORDER) ',N'@L0 uniqueidentifier,@L2 uniqueidentifier,@IU int,@L3 uniqueidentifier,@L4 uniqueidentifier,@DN nvarchar(260)',@L0='00000000-0000-0000-0000-000000000000',@L2='3640A558-D026-4ABF-BB7F-549ECA727EEC',@IU=3,@L3='750DB8DD-A6AA-49B5-9D23-EB6E4B95EAD7',@L4='EE1ADF17-47DB-4065-A8DC-8638E6903484',@DN=N'Documents'
What I found is that all change on a list item conducted through UI would affect its "Modified" field immediately. Updating a list item by code is a different story. I will cover that in next post.
It looks like the "Last Modified" field is used internally by SharePoint itself. We should use "Modified" field in our logic to trace a list item's last modified time in general.
