Special Datatypes

Special datatypes consist of datatypes with a distinct, or special use.

These are the special datatypes:

bit

Is a datatype that holds either 1 or 0. Integer values other than 1 or 0 are accepted but are always interpreted as 1. Storage size is 1 byte. Multiple bit types in a table can be collected into bytes. For example, 7-bit columns fit into 1 byte; 9-bit columns take 2 bytes. The status column in the syscolumns system table indicates the unique offset position for bit columns.

Columns of type bit cannot be NULL and cannot have indexes on them. Use bit for true/false or yes/no types of data.

timestamp

Is a datatype that is automatically updated every time a row containing a timestamp column is inserted or updated. Browse mode requires timestamp columns in tables that are to be browsed in DB-Library applications. Values in timestamp columns are not datetime data, but binary(8) varbinary(8) data, indicating the sequence of SQL Server activity on the row. A table can have only one timestamp column.

The timestamp datatype has no relation to the system time ¾ it is simply a monotonically increasing counter whose values will always be unique within a database.

If you do not supply a datatype, a column named timestamp is automatically defined as a timestamp datatype. You can create a column named timestamp and assign it another datatype (although assigning another datatype can confuse other users and does not allow browsing with the DB-Library BROWSE functions).

By default, the timestamp datatype is defined as binary(8). If timestamp is used with NOT NULL, the column will be stored as varbinary(8). In client applications, the metadata declares the timestamp as varbinary, regardless of the nullability. The current timestamp value for a database can be selected with the global variable @@DBTS.

user_defined_datatype_name

Is the name of a user-defined datatype created with the sp_addtype system stored procedure. For details, see the sp_addtype system stored procedure.