About the Supported SQL Data Types

bigint

int

real

uniqueidentifier

bit

money

smalldatetime

varbinary

char

nchar

smallint

varbinary(max)

date

ntext

smallmoney

varchar

datetime

numeric

text

varchar(max)

decimal

nvarchar

time

float

nvarchar(max)

tinyint

A field of this data type can store an integer value from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). The SQL Field Size for a field of this type is 8 bytes.

A char field stores character strings consisting of letters, numbers or symbols. Char fields are fixed width and do not support the unicode character set. Char fields support a single character set that is specified during SQL Server setup. The maximum field size for a char field is 8000 characters (or 8000 bytes, 1 byte per character). However, since this is a fixed width field type, you should configure the field to use the smallest field size necessary given the type of information you intend to store in this field. Compare this data type with nchar and varchar. For fields that require more characters, use nvarchar(max) or varchar(max).

An nchar field stores unicode character strings consisting of letters, numbers or symbols. Nchar fields are fixed width and can support a maximum of 4000 characters. (Unicode characters require 2 bytes per character.) However, since this is a fixed width field type, you should configure the field to use the smallest field size necessary given the type of information you intend to store in this field. Compare this data type with char and nvarchar. For fields that require more characters, use nvarchar(max) or varchar(max). 

An ntext field stores large unicode character strings. Prior to SQL Server 2005, this data type was typically used for fields that required more than 4000 unicode characters or if a table needed to store multiple large fields. Beginning with SQL Server 2005, Microsoft has introduced nvarchar(max), which eliminates the need to use ntext fields (Microsoft has indicated that ntext will be removed from a future release of Microsoft SQL Server). See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information.

While Aptify continues to support ntext for backwards compatibility, all of the large fields in Aptify 4.x and 5.x use either nvarchar(max) or varchar(max). For existing entities, you should migrate all ntext fields to nvarchar(max) whenever feasible, and for all new entities, you should use nvarchar(max) rather than ntext.

Note that SQL Server and Aptify do not support sorting view results by an ntext field, so ntext fields do not appear in the Sort by drop-down list on a view's Sorting tab. Also, a user cannot filter records based on an ntext field. Therefore, ntext fields do not appear in the Field drop-down list when creating view filters or using the Find dialog.

An nvarchar field stores unicode character strings consisting of letters, numbers or symbols. The actual size of an nvarchar field varies depending on the amount of data entered in the field. The SQL Field Size you specify sets the maximum number of characters that can be entered for a field of this type. In any case, an nvarchar field cannot exceed 4000 characters (unicode characters require 2 bytes per character). Compare this data type with nchar and varchar. For fields that require more characters, use nvarchar(max) or varchar(max). Note that nvarchar is the default SQL Data Type when you open a new Fields record.

A field of this type stores a valid date and time combination. A smalldatetime field requires 4 bytes and supports a date range from 1/1/1900 to 6/6/2079. 

A text field stores large character strings. Prior to SQL Server 2005, this data type was typically used for fields that required more than 8000 characters or if a table needed to store multiple large fields. Beginning with SQL Server 2005, Microsoft has introduced varchar(max), which eliminates the need to use text fields (Microsoft has indicated that text will be removed from a future release of Microsoft SQL Server). See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information.

While Aptify continues to support text for backwards compatibility, all of the large fields in Aptify 4.x and 5.x use either nvarchar(max) or varchar(max). For existing entities, you should migrate all text fields to varchar(max) whenever feasible, and for all new entities, you should use varchar(max) rather than text.

A field of this data type corresponds to a globally unique identifier (GUID). A GUID is a 16-byte number that is unique for each record. It is represented by 32 hexadecimal digits (0 - 9 and A - F) in the following format: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

A sample GUID is shown in the figure below.

When adding a uniqueidentifier field to an Entities record, an administrator can enter NEWID as the field's Default Value . With this default value, Aptify automatically generates a GUID and populates this field for any record that does not already have a GUID.

Unless a GUID has been automatically generated (by setting the field's Default Value to NEWID), a user must specify one of the following values for a uniqueidentifier field when creating a record:

• Blank: When a unique identifier field is left blank, Aptify automatically stores a Null value in the database for that field (assuming that the field is not required).
• A valid GUID: Any non-blank value specified by the user must be a GUID in the appropriate hexadecimal format. Note that you can optionally use brackets ({ }) to enclose the GUI (for example, {C31A15DA-D5BA-4560-8A36-D11CA601EFE1}).

See Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on the uniqueindentifier data type and the NEWID function.

A varbinary(max) field is a special case of the varbinary data type that stores a large amount of binary digits. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on this data type.

A varchar field stores character strings consisting of letters, numbers or symbols. The actual size of a varchar field varies depending on the amount of data entered in the field. The SQL Field Size you specify sets the maximum possible size of the field. Varchar fields support a single character set that is specified during SQL Server setup; they do not support the unicode character set. The maximum field size for a varchar field is 8000 characters (or 8000 bytes, 1 byte per character). Compare this data type with char and nvarchar. For fields that require more characters, use varchar(max).