This documentation is archived and is not being maintained.

List Data Types

SharePoint Team Services 1.1

The following table summarizes the data types exposed in a list's user interface and how these types are mapped to actual data types in the SQL Server database.

User Interface Data TypesSQL Data TypesDescription
Counterint1 through 2,147,483,647. The database automatically generates a new value when rows are inserted into the table.

This data type is used internally and is not exposed to the end user.

Textnvarchar(255)A Unicode text field that can hold up to 255 characters.

SQL Server uses a UCS-2 Unicode format.

Long Textnvarchar(450)A longer version of the text field. This data type is used for special lists and is not exposed to the end user.

Note   450 is the maximum indexable field size.

NotentextA Unicode text field that can hold up to 1,073,741,823 characters. These fields cannot be indexed or used in a WHERE clause.
Integerint– 2,147,483,648 through 2,147,483,647
Number (Float)float-1.79E + 308 through 1.79E + 308
CurrencyfloatExcept for formatting, identical to Number (Float).
DateTimedatetimeJanuary 1, 1753 to December 31, 9999. Time values are stored in the time zone of the server.
URLintURLs are stored in a separate table. The list only contains a reference to an ID in a documents table (DocMD). When a URL is inserted into a list, whether that URL is already in the list must first be checked. If it is, the existing URL's ID is used. If it is not in the list, a new row is added to the URLs table and then the ID of the new row is inserted into the list. A URL can appear only once in the URLs table for link fix-up to work correctly.
BooleanbitSQL Server "bit" fields can have three states: 1, 0, and NULL.
Choice (Text)nvarchar(255)Choice columns can have a base type of integer or text. The list user interface only supports the text base type.
Choice (Integer)intInteger choice columns are used for sorting by numeric order not by text order. For example, a priority choice may be High(1), Medium(2), Low(3). This will sort from high to low (1, 2, 3), not in alphabetical order (High, Low, Medium). This data type is used for several built-in lists.
LookupintA lookup to the ID field of another list. For example, author fields involve a lookup to the UserInfo table but contain only the ID, not the author's name. Lookup fields sort by the text value in the target table. For example, authors are sorted by title, not by their ID value.

See Also

Overview of the SharePoint Team Services Database

Using SQL with the SharePoint Team Services Database