Posts Tagged ‘SharePoint 2010’

Following are the limitation of SharePoint software.

Limit Maximum Value Limit Type
Web application 20 per farm Supported
Zone 5 per web application Boundary
Managed path 20 per web application Supported
Solution cache size 300 MB per web application Threshold
Application Pools 20 per farm Supported
Number of content databases 500 per farm Supported
Content database size (general usage scenarios) 200 GB per content database Supported
Content database size (all usage scenarios) 4 TB per content database Supported
Content database size (document archive scenario) No explicit content database limit Supported
Content database items 60 million (includes documents and items) Supported
Site collections per content database 10,000 Supported
Site collections per farm 750,000 Supported
Web site 250,000 per site collection Supported
Site collection size Max size of the content database Supported
List row size 8,000 bytes per row Boundary
File size 2 GB Boundary
Documents 30,000,000 per library Supported
Major versions 400,000 Supported
Minor versions 511 Boundary
Items 30,000,000 per list Supported
Rows size limit 6 table rows internal to the database used for a list or library item Supported
Bulk operations 100 items per bulk operation Boundary
List view lookup threshold 8 join operations per query Threshold
List view threshold 5,000 Threshold
List view threshold for auditors and administrators 20,000 Threshold
Subsite 2,000 per site view Threshold
Coauthoring in Word and PowerPoint for .docx, .pptx and .ppsx files 10 concurrent editors per document Threshold
Security scope 1,000 per list Threshold

SPQuery List Joins

Posted: January 3, 2014 in SharePoint
Tags: ,
SharePoint 2010 Step by Step SPQuery List joins Using CAML
I am using the example following example lists
  1. CustomerCity List
  2. Customer
List Columns
CustomerCity columns are:
Single Line text
Customer columns are:
Single Line text
Lookup type of CustomerCity
Dummy Data used
For CustomerCity
For Customer
Using the Join in SharePoint 2010 List Using SPQuery Class.
We need to set the three most important properties for that.
  1. Joins
  2. ProjectedFields
  3. ViewFields
SharePoint 2010 adds Join To CAML SPQuery.Joins
Types of joins
  1. Inner
  2. Left
Requested Lookup columns
Projections allow inclusion of fields joined lists
Joins: Each join is represented by a Join element child of the Joins element. Only inner and left outer joins are permitted. Moreover, the field in the primary list must be a Lookup type field that looks up to the field in the foreign list. There can be joins to multiple lists, multiple joins to the same list, and chains of joins. If a given list is the foreign list in more than one join, it must have distinct aliases assigned to it by the ListAliasattributes of the Join elements representing the joins.
Note: Multiple Lines of text, Choice type columns are not supported in ProjectedFields.
private void button1_Click(object sender, EventArgs e)
            string siteUrl = “http://home&#8221;;
            SPWeb _web = new SPSite(siteUrl).OpenWeb();
            var items =_web.Lists[“Customer”].GetItems(GetQuery());
            foreach (SPListItem item in items)
                MessageBox.Show(string.Format(“{0}—-{1}”, item[“Title”], item[“CityTitle”]));
        private SPQuery GetQuery()
            SPQuery _query = new SPQuery();
            _query.Query = “”;
            _query.Joins = @”<Join Type=’INNER’ ListAlias=’City’>
                          <!–List Name: CustomerCity–>
                            <FieldRef Name=’City’ RefType=’ID’ />
                            <FieldRef List=’City’ Name=’ID’ />
            _query.ProjectedFields = @”<Field Name=’CityTitle’ Type=’Lookup’ List=’City’ ShowField=’Title’ />
                                    <Field Name=’CityContentTypeId’ Type=’Lookup’ List=’City’ ShowField=’ContentTypeId’ />”;
            _query.ViewFields = @” <FieldRef Name=’Title’ />
                                     <FieldRef Name=’CityTitle’ />”;
            return _query;
The above “Query” property of the SPQuery class I left blank you can enter the condition according to your requirement.

SharePoint lists are, by and large, similar to database tables in structure and behavior. Like Tables, Lists support different data types and can have triggers (event receivers) that fire on events such as creating, updating or deleting items. Also, lists can be configured to filter, sort or group items based on column values.

Also, SharePoint lists support the concept of view like in database and can be queried by CAML. The columns in the list can be indexed for better performance while querying.

In SharePoint 2010, the lists are much more enhanced to incorporate important aspects of RDBMS like :

  1.  Referential Integrity
  2.  Unique Keys
  3.  Joins

1.  Referential Integrity with improved lookup columns

In previous versions of SharePoint (2003, 2007), the  lists lacked an important aspect of RDBMS – Referential integrity- that is a measure of data consistency in a database. If you have a lookup between two lists and you want to have Referential Integrity, previous SP versions won’t provide it. The lookup columns could be used just to get their data from other list columns.

Now, SharePoint 2010 provides the mechanism to enforce Referential integrity in our data model. Also, it can block or cascade your deletes between lists automatically.

Just like foreign key constraints in RDBMS, SharePoint 2010 allows you to configure restrict delete and cascade delete rules on lookup column relationships.

• Cascade delete : When we delete a list item, SharePoint automatically delete all items that have references to that item by look up column. It can be used to enforce parent-child relationships.

• Restrict delete : With this rule, SharePoint don’t allow you to delete a list item that is referenced by a lookup column in another list. It can be used to enforce peer-to-peer relationships.

[Note: SharePoint is not going to be a replacement for SQL Server with this functionality. If you have a very complex data model, SQL Server is an obvious choice]

An additional improvement on lookup columns is that now we can retrieve more columns(Projected Columns) from the related list.

Configuring the Delete Behavior on a Lookup Field 

We can configure list relationship rules through the user interface when we create look up column.

Look Up field

We can also programmatically configure delete behavior in a feature receiver class. Since the list relationship is specific to individual list instances, we cannot configure it declaratively in column definitions or content types.Programmatically setting the delete behavior can be useful if you use features to deploy the lists and columns.

SPFieldLookup is class that represents lookup columns. This class has a RelationshipDeleteBehaviorenum property that enables us to specify the delete behavior for the column.It can be set to one of the following SPRelationshipDeleteBehavior enumeration values:

  • SPRelationshipDeleteBehavior.Cascade. Setting this value deletes all items if the lookup column refers to an item that no longer exists in the related list.
  •  SPRelationshipDeleteBehavior.Restrict. Setting this value prohibit the users from deleting items in the related list if those items are referred to in the lookup column values.

For example, we can use below methods in a feature receiver class to restrict or cascade delete.

private void RestrictDelete(SPWeb web)


SPList list = web.Lists[“MyList”];

SPField field = list.Fields[“TestNameLookUp”];

SPFieldLookup fieldLookup = (SPFieldLookup)field;

//For unique values of column in items

fieldLookup.EnforceUniqueValues = true;

//For indexing column

fieldLookup.Indexed = true;

fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;



private void CascadeDelete(SPWeb web)


SPList list = web.Lists[“MyList”];

SPField field = list.Fields[“TestNameLookUp”];

SPFieldLookup fieldLookup = (SPFieldLookup)field;

//For unique values of column in items

fieldLookup.EnforceUniqueValues = true;

//For indexing column

fieldLookup.Indexed = true;

fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;



Programmatically Find Lookups and Related Lists

We can use the GetRelatedFields method to return a SPRelatedFieldCollection collection. We can retrieve properties, such as the LookupList that the column  is related to,  the relationship behavior when some item is deleted from the list and also some other useful information.

Below is the example

private void GetRelatedColumnsAndListsInfo(SPWeb web)


SPList list = web.Lists[“MyCustomersList”];

//Get related columns

SPRelatedFieldCollection relatedFields = list.GetRelatedFields();

foreach (SPRelatedField relatedField in relatedFields)


//Get Lookup list

SPList relatedList = relatedField.LookupList;

Console.WriteLine(relatedField.ListId + ” ” +   relatedField.FieldId);

Console.WriteLine(“List Name: ” + relatedList.Title + ” Relationship Behavior: ” + relatedField.RelationshipDeleteBehavior.ToString());



Controlling the cascade delete limits and time outs

At web appilcation level, We can have control over the Cascade deletes  usingSPWebApplication.CascadeDeleteMaximumItemLimit andSPWebApplication.CascadeDeleteTimeoutMultiplier.

  • CascadeDeleteMaximumItemLimit allows you to specify the maximum  number of cascaded items that can be deleted. By default, this value is 1000 items.
  •  CascadeDeleteTimeoutMultiplier  allows you to specify the timeout(in secs)  for the operation. The default value is 120 seconds.

private void SetCascadeDeletes(SPWebApplication webApp)


//sets max cascade delete limit to 2000

webApp.CascadeDeleteMaximumItemLimit = 2000;

//sets the time out to 5 mins

webApp.CascadeDeleteTimeoutMultiplier = 300;



2.  Unique Keys (Columns)

SharePoint 2010  lists have the ability to ensure uniqueness for the values in your columns. As the unique column  is guaranteed to have a unique value, We can  it as an  index to make look ups faster.


3. List Joins

SharePoint lists support joins like database lists.  SharePoint can perform left and inner joins but not right joins.

  • By inner join, we can combine the values from the liststables.
  • By left join or left outer join , anything that appears in the left listtable  will be returned in the result set even if it does not exist in the right listtable.

We can perform a join between two lists on a lookup field by setting the Joins property on our SPQueryobject with the join we want to perform.

Apart from the Joins property, we must also specify a value for the ProjectedFields property. This property gets other required columns from the lookup list. We can alias the column by using the Name attribute and specify the column name in the ShowField attribute. After getting results, we have to use theSPFieldLookupValue  to display the values for projected columns.

In the below example, we are joining on the Customers list, where the Customers.customer = Orders.Customer

private void PerformListJoin(SPWeb web)         {

SPList OrderList = web.Lists[“Orders”];

SPQuery CustomerQuery = new SPQuery();

CustomerQuery.Joins =             ” <Join Type=’INNER’ ListAlias=’Customers’> ” +             ” <Eq> ” +             ” <FieldRef Name=’Customer’ RefType=’Id’ /> ” +             ” <FieldRef List=’Customers’ Name=’ID’ /> ” +             ” </Eq> ” +             ” </Join> “;

StringBuilder ProjectedFields = new StringBuilder();

ProjectedFields.Append(” <Field Name=’CustomerTitle’  Type=’Lookup’ List=’Customers’ ShowField=’Title’ /> “);

ProjectedFields.Append(” <Field Name=’CustomerAddress’Type=’Lookup’ List=’Customers’ ShowField=’CustomerNum’ /> “);

CustomerQuery.ProjectedFields = ProjectedFields.ToString();

SPListItemCollection Results = OrderList.GetItems(CustomerQuery);

foreach (SPListItem Result in Results)             {

SPFieldLookupValue CustomerTitle = new SPFieldLookupValue(Result[“CustomerTitle”].ToString());

SPFieldLookupValue CustomerAddress = new SPFieldLookupValue(Result[“CustomerAddress”].ToString());

Console.WriteLine(Result.Title + ” ” + CustomerTitle.LookupValue + ”  ” + CustomerAddress.LookupValue);