none
Column Type Maximums to Avoid Row Wrapping in SharePoint Lists

    Domanda

  • I've read the TechNet articles Designing large lists and maximizing list performance (SharePoint Server 2010) and SharePoint Server 2010 capacity management: Software boundaries and limits regarding the maximum number of columns of a given type before row wrapping occurs on the SPList.  However, It's still unclear to me whether the limits are for each column type, or the sum of the columns types in the table.  For example, can you can 16 integers, 16 single value lookups, 16 people and groups, and 16 managed metadata in any list; or must the total of integers, single value lookups, people and groups, and managed metadata columns not exceed 16?

    Also, the SPFieldType enumeration contains 33 different types.  How do these map into the "Column Types" described in the articles?

    I have written a PowerShell script that traverses all SPLists in my farm and returns the counts of each SPFieldType for each list.  I'm trying to take these counts and generate a report to identify which lists exceed or approach the limits that will cause row wrapping.

    Thanks!


    giovedì 28 giugno 2012 15:13

Tutte le risposte

  • Hi Ordeneaux,

    The limits are for each column, not the sum of the columns in the table.

    The size limits of the rows will be specified depending on the maximum number to table rows for the internal database. This applies to each of the items in a list so that they can be accommodated. This requires several columns with can being wrapped in as many as six internal table rows. When you have a list with many small columns then you can add more of them if you need to. This means that additional rows for larger lists can be done in order to minimize the number of table rows for the database used for each item in a list.

    You can refer to the following link.

    http://www.sharepointsecurity.com/sharepoint/sharepoint-development/understanding-rows-in-sharepoint-2010/

    You can also use object module get the column type count.

    public void Display()
            {
                SPSite site = new SPSite("http://jack-7716f30e37:2012/personal/Test/default.aspx");
                using (SPWeb Web = site.OpenWeb())
                {
                    SPList list = Web.Lists["Test"];
                    int a = 0;
                    int b = 0;
                    int c = 0;
                    int d = 0;
                    int e = 0;
                    //...
                    SPFieldCollection FieldCollection = Web.Fields;
                    foreach (SPField field in FieldCollection)
                    {
                        SPFieldType fieldType = field.Type;
                        switch (fieldType)
                        {
                            case SPFieldType.Boolean: a++; break;
                            case SPFieldType.Calculated: b++; break;
                            case SPFieldType.Choice: c++; break;
                            case SPFieldType.Number: d++; break;
                            case SPFieldType.DateTime: e++; break;
                            //...
                        } 
                    }
                    Console.WriteLine(a);
                    Console.WriteLine(b);
                    Console.WriteLine(c);
    }
    }

    Thanks,

    Jack

    • Contrassegnato come risposta Ken Ordeneaux lunedì 2 luglio 2012 14:24
    • Contrassegno come risposta annullato Ken Ordeneaux giovedì 5 luglio 2012 20:54
    lunedì 2 luglio 2012 05:09
  • Hi Jack,

    Thanks so much for clearing that up!

    Just a note, the line in the code example "SPFieldCollection FieldCollection = Web.Fields;" should read "SPFieldCollection FieldCollection = list.Fields;"

    Here is my PowerShell script if anyone is interested:

    #Enter the URL to your SharePoint web application, and the script will iterate all site collections and sites contained in it.
    $serverUrl = "http://sp2010vm";

    #Need to adjust the buffer size to prevent text wrapping in the output.
    $host.UI.RawUI.BufferSize = new-object System.Management.Automation.Host.Size(512,50)

    #Loads the SharePoint Powershell commands
    if ( (Get-PSSnapin -Name Microsoft.Sharepoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
    {
        Add-PsSnapin Microsoft.Sharepoint.PowerShell
    }
    $webApp = Get-SPWebApplication $serverUrl
    # Write out report column headers
    $outString = "List Url"+","+"List Title"
    $types = [Microsoft.SharePoint.SPFieldType].GetFields()
    foreach($type in $types)
    {
     $outString += ","+$type.Name.Trim();
    }
    Write-Host $outString
    #Now we begin traversing the SharePoint web application site collections
    foreach($site in $webApp.Sites)
    {
     foreach($web in $site.AllWebs)
     {
      foreach($list in $web.Lists)
      {
       #Write the URL and Title of the list/library
       $outString = $serverUrl+$list.RootFolder.ServerRelativeUrl+","+ $list.Title;
       
       #initialize new hashtable to collect column type counts
       $hashTable = @{};
       foreach($type in $types)
       {
        $hashTable.Add($type.Name,0);
       }
       
       #iterate through the list's columns and incriment the count of each column type
       foreach($field in $list.Fields)
       {
        $hashTable[$field.Type.ToString()]++;
       }
       #Write the totals
       foreach($type in $types)
       {
        $outString = $outString+ ","+$hashTable[$type.Name];
       }
       Write-Host $outString
       
      }
      #Very important to dispose of the SPWeb objects to keep memory usage at a decent level.
      $web.Dispose();
     }
    }

    Thanks,

    Ken


    lunedì 2 luglio 2012 14:43
  • Actually, the limits are determined by the number of fields of each SqlDataType in the AllUserData table in the SP Content Database. So, the limits are "additive."

    I did some analysis by querying the content database (using Sql Mgmt Studio) before and after I added columns of each type to a SharePoint list to document the allocation of database columns. Here are my findings:

    Sql Data Type # of database columns Number of database columns used by SharePoint column Types
    Managed Metadata Lookup (Single Value) People/Group Single Line of Text Multiple Lines of Text Choice Url Yes/No Date and Time Number Currency Calculated Unique Identifier* Integer*
    nvarchar(255) 64 1 1 2
    nvarchar(MAX) 32 1 1
    int 16 1 1 1 1
    bit 16 1
    float 12 1 1
    dateTime 8 1
    sqlVariant 8 1
    uniqueidentifier 1 1

    * Unique Identifiers (GUIDs), and Integer columns are not available through the "Create Column" page or SharePoint Designer.  These column types are only included in some OOB Content Types or can be created using BCS.
    * Even if you specify 0 decimal places, Number columns always use a float db column.  They do not use the int columns.
    giovedì 5 luglio 2012 17:38
  • My findings are not consistant with the TechNet articles I referenced in the question post, but I observed the changes in my content database and believe Technet is incorrect about the Managed Metadata and URL columns.  I hope this helps get the documentation corrected.

    Ken

    giovedì 5 luglio 2012 20:51