Datatypes

In Transact-SQL, datatypes specify the type of information, size, and storage format of table columns, stored procedure parameters, and local variables.

You can use SAP ASE system datatypes when you are defining columns, or you can create user-defined datatypes.

For example, the int (integer) datatype stores whole numbers in the range of plus or minus 2³¹, and the tinyint (tiny integer) datatype stores whole numbers between 0 and 255 only.

SAP ASE supplies several system datatypes, and two user-defined datatypes, timestamp and sysname. Use sp_addtype to build user-defined datatypes based on the system datatypes.

You must specify a system datatype or user-defined datatype when declaring a column, local variable, or parameter. The following example uses the system datatypes char, numeric, and money to define the columns in the create table statement:

create table sales_daily
    (stor_id char(4),
    ord_num numeric(10,0),
    ord_amt money)

The next example uses the bit system datatype to define the local variable in the declare statement:

declare @switch bit

Use sp_help to determine which datatypes have been defined for columns of existing tables.

• System-Supplied Datatypes
  You can use either a system datatype or user-defined datatype when declaring a column, local variable, or parameter.
• LOB Locators in Transact-SQL Statements
  Large object (LOB) locators let you reference an LOB in Transact-SQL statements rather than referencing the LOB itself.
• Convert Between Datatypes
  SAP ASE automatically handles many conversions from one datatype to another. These are called implicit conversions. Use the convert, inttohex, and hextoint functions to explicitly request other conversions.
• Mixed-Mode Arithmetic and Datatype Hierarchy
  When you perform arithmetic on values with different datatypes, SAP ASE must determine the datatype and, in some cases, the length and precision, of the result.
• User-Defined Datatypes
  A Transact-SQL enhancement to SQL allows you to design your own datatypes to supplement the system datatypes. A user-defined datatype is defined in terms of system datatypes.
• Datatype Entry Rules
  Several of the SAP ASE-supplied datatypes have special rules for entering and searching for data.
• Get Information About Datatypes
  Use sp_help to display information about the properties of a system datatype or a user-defined datatype.