If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

About the Supported SQL Data Types

Aptify supports the following SQL data types:

 

Selecting a Field's SQL Data Type

 

SQL Data Types

Description

bigint

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.

bitA bit field supports a value of 0 or 1. This field type is suitable for enable/disable or true/false flags. The Aptify Baseline Form Template Generator displays bit fields using the check box form component.
char

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).

When designing entities that contain large fields, keep in mind that the maximum size of each record or row in a SQL server table is 8060 bytes. Therefore, the maximum size of all fields in a particular table should not exceed this limit. If necessary, you can specify sub-tables for your entity to store one or fields. See Creating Multiple Base Tables for details.

Note Concerning Microsoft SQL Server 2008: Microsoft SQL Server 2008 relaxes the restrictions of the maximum record size for certain field types. See Row-Overflow Data Exceeding 8 KB in the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms186981(v=sql.105).aspx) for more information.

dateA field of this type stores a valid date. This type of field does not store a time component. A date field requires 3 bytes and supports a date range from 1/1/1000 to 12/31/9999. Note that the date data type is new to Microsoft SQL Server 2008.
datetimeA field of this type stores a valid date and time combination. A datetime field requires 8 bytes and supports a date range from 1/1/1753 to 12/31/9999.
decimalA field of this data type can store a decimal value. In SQL Server and Aptify, the decimal and numeric data types provide the same functionality. When you select one of these data types, you must also specify the SQL Field Precision (the number of digits in the number) and SQL Field Scale (which is the number of digits in the number that appear to the right of the decimal point). See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on these data types.
floatA field of this data type stores a floating point approximate numeric value. In Aptify, the storage size of a float field is 8 bytes, which supports 15 digits. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on floating point data types. Also, see real for more information.
intA field of this data type can store an integer value from -2,147,483,648 through 2,147,483,647. The SQL Field Size for a field of this type is 4 bytes.
moneyA field of this type can store a money value up to four decimal places (if your field requires more decimal places, use the decimal or numeric data type instead). The money type uses 8 bytes of storage and supports a data range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807. Compare with smallmoney. Also, see Money Fields for information on how to specify that a money field supports multiple currencies.
nchar

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). 

When designing entities that contain large fields, keep in mind that the maximum size of each record or row in a SQL server table is 8060 bytes. Therefore, the maximum size of each field in a particular table should not exceed this limit. If necessary, you can specify sub-tables for your entity to store one or fields. See Creating Multiple Base Tables for details.

Note Concerning Microsoft SQL Server 2008: Microsoft SQL Server 2008 relaxes the restrictions of the maximum record size for certain field types. See the Microsoft SQL Server Books Online for more information about run-overflow data.

ntext

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.

numericA field of this data type can store a decimal value. In SQL Server and Aptify, the decimal and numeric data types provide the same functionality. When you select one of these data types, you must also specify the SQL Field Precision (the number of digits in the number) and SQL Field Scale (which is the number of digits in the number that appear to the right of the decimal point). See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on these data types.
nvarchar

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.

When designing entities that contain large fields, keep in mind that the maximum size of each record or row in a SQL server table is 8060 bytes. Therefore, the maximum size of each field in a particular table should not exceed this limit. If necessary, you can specify sub-tables for your entity to store one or fields. See Creating Multiple Base Tables for details.

Note Concerning Microsoft SQL Server 2008: Microsoft SQL Server 2008 relaxes the restrictions of the maximum record size for certain field types. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information about row-overflow data. 

nvarchar(max)An nvarchar(max) field is a special case of the nvarchar data type that stores a large amount of unicode characters consisting of letters, numbers or symbols. Introduced in Microsoft SQL Server 2005, nvarchar(max) stores and retrieves large unicode character sets more efficiently than ntext. Aptify recommends that you use nvarchar(max) for all fields that you previously would have designated as ntext. 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.
realA field of this data type stores a floating point approximate numeric value. In Aptify, the storage size of a real field is 4 bytes, which supports 7 digits. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information on floating point data types. Also, see float for more information.
smalldatetime

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. 

smallintA field of this data type can store an integer value from -32,768 through 32,767. The SQL Field Size for a field of this type is 2 bytes.
smallmoneyA field of this type can store a money value up to four decimal places (if your field requires more decimal places, use the decimal or numeric data type instead). The smallmoney type uses 4 bytes of storage and supports a data range from -214,748.3648 through 214,748.3647. Compare with money. Also, see Money Fields for information on how to specify that a money field supports multiple currencies.
text

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.

Note that SQL Server and Aptify do not support sorting view results by a text field, so text 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 a text field. Therefore, text fields do not appear in the Field drop-down list when creating view filters or using the Find dialog.

timeA field of this data type stores a valid time without a date component. A time field requires 5 bytes and should be specified in the HH:MM:SS format. Note that the time data type is new to Microsoft SQL Server 2008.
tinyintA field of this data type can store an integer value from 0 through 255. The SQL Field Size for a field of this type is 1 byte.
uniqueidentifier

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.

Sample GUID
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.

Setting NEWID Default Value
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.

varbinaryA varbinary field stores binary data. The actual size of a varbinary 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. The maximum field size for a varbinary field is 8000 bytes. For fields that require more characters, use varbinary(max).
varbinary(max)

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.

When designing entities that contain large fields, keep in mind that the maximum size of each record or row in a SQL server table is 8060 bytes. Therefore, the maximum size of each field in a particular table should not exceed this limit. If necessary, you can specify sub-tables for your entity to store one or fields. See Creating Multiple Base Tables for details.

Note Concerning Microsoft SQL Server 2008: Microsoft SQL Server 2008 relaxes the restrictions of the maximum record size for certain field types. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information about row-overflow data.

varchar

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).

When designing entities that contain large fields, keep in mind that the maximum size of each record or row in a SQL server table is 8060 bytes. Therefore, the maximum size of each field in a particular table should not exceed this limit. If necessary, you can specify sub-tables for your entity to store one or fields. See Creating Multiple Base Tables for details.

Note Concerning Microsoft SQL Server 2008: Microsoft SQL Server 2008 relaxes the restrictions of the maximum record size for certain field types. See the Microsoft SQL Server Books Online (https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx) for more information about row-overflow data. 

  varchar(max)A varchar(max) field is a special case of the varchar data type that stores a large amount of characters consisting of letters, numbers or symbols. Introduced in Microsoft SQL Server 2005, varchar(max) stores and retrieves large character sets more efficiently than ntext. Aptify recommends that you use nvarchar(max) for all fields that you previously would have designated as text. 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.

Copyright © 2014-2019 Aptify - Confidential and Proprietary