List Data Types
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 Types | SQL Data Types | Description |
|---|---|---|
| Counter | int | 1 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. |
| Text | nvarchar(255) | A Unicode text field that can hold up to 255 characters.
SQL Server uses a UCS-2 Unicode format. |
| Long Text | nvarchar(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. |
| Note | ntext | A Unicode text field that can hold up to 1,073,741,823 characters. These fields cannot be indexed or used in a WHERE clause. |
| Integer | int | – 2,147,483,648 through 2,147,483,647 |
| Number (Float) | float | -1.79E + 308 through 1.79E + 308 |
| Currency | float | Except for formatting, identical to Number (Float). |
| DateTime | datetime | January 1, 1753 to December 31, 9999. Time values are stored in the time zone of the server. |
| URL | int | URLs 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. |
| Boolean | bit | SQL 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) | int | Integer 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. |
| Lookup | int | A 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. |