Use special data _ MySQL

Special Data types refer to data types that are not suitable for other data types. For example, if you want to store the value of yes or no in the customer survey with 1 or 0, you can use the bit data type. Microsofregulatory; SQLServer #8482; 2000 there are several data types in this category: bitbit data does not need to be placed in single quotes. It refers to the special data types that are not suitable for other data types. For example, if you want to store the "yes" or "no" value in the customer survey with 1 or 0, you can use the bit data type. Microsoft®SQL Server™2000 there are several data types in this category:

Bit
Bit data does not need to be placed in single quotes. It is a digital data similar to the integer and numeric data of SQL Server, but the bit column can only store 0 and 1.

SQL _variant
The SQL _variant data type in SQL Server allows a single column, parameter, or variable to store data values of different data types. Each instance in the SQL _variant column records the data value and metadata that describes the value: the basic data type, maximum size, decimal places, precision, and sorting rules of the value.

The second table in the following example contains a SQL _variant column:

Create table ObjectTable
(ObjectID int
CONSTRAINT PKObjectTable primary key,
ObjectName nvarchar (80 ),
ObjectWeight decimal (10, 3 ),
ObjectColor nvarchar (20)
)

Create table VariablePropertyTable
(ObjectID int REFERENCES ObjectTable (ObjectID ),
PropertyName nvarchar (100 ),
PropertyValue SQL _variant,
CONSTRAINT PKVariablePropertyTable
Primary key (ObjectID, PropertyName)
)

To obtain the metadata information of any specific SQL _variant instance, use the SQL _VARIANT_PROPERTY function.

Table
The table data type is similar to a temporary table, which can be used to store a result set for later processing. This data type can only be used to define local variables of the table type and return values of user-defined functions.

The definitions of a table variable or return value include the column, data type, precision, the number of decimal places in each column, and optional primary key, UNIQUE, and CHECK constraints.

The format of rows stored in table variables or returned values of user-defined functions must be defined when variables are declared or functions are created. Its syntax is based on CREATE
TABLE syntax. For example:

DECLARE @ TableVar TABLE
(Cola int primary key,
Colb char (3 ))

Insert into @ TableVar VALUES (1, 'ABC ')
Insert into @ TableVar VALUES (2, 'Def ')

SELECT * FROM @ TableVar
GO

Returns the table variables and user-defined functions of a table. they can only be used in certain SELECT and INSERT statements, and UPDATE, DELETE, and
The declare cursor statement supports tables. The returned table variables and user-defined functions cannot be used in any other Transact-SQL statements.

The indexes or other constraints used in the table must be defined as part of the DECLARE variable or create function statement. You cannot use it later.
Because the create index or alter table statement cannot reference TABLE variables and user-defined functions.

For more information about the syntax used to define table variables and user-defined functions, see DECLARE @ local_variable (T-SQL) and CREATE
FUNCTION (T-SQL ).

Timestamp
The SQL Server timestamp data type is independent of the time and date. SQL Server timestamp is a binary number that indicates the relative sequence of data modifications in the database. The timestamp data type was initially implemented to support the SQL Server restoration algorithm. The current
@ DBTS the value is marked once, and then @ DBTS is added with 1. This is enough to help the recovery process determine the relative order of page modifications, but the timestamp value has nothing to do with time.

In SQL Server 7.0 and SQL Server 2000, the value of @ DBTS increases progressively only when it is used in the timestamp column. If a table contains a timestamp column, the timestamp value of this row is set to the current @ DBTS value every time a row is modified by the INSERT, UPDATE, or DELETE statement, then @ DBTS and add 1.
Because the timestamp value changes during each modification, do not use the timestamp column in The Key (especially the primary key.

To record the number of data changes in the table, you can use the datetime or smalldatetime data type to record these events and triggers. in this way, when a change occurs, the values can be automatically updated.

User-defined data types
User-defined data types allow you to extend the basic data types (such as varchar) of SQL Server by using descriptive names and formats customized for specific purposes ). For example, the following statement implements a birthday user-defined data type, which allows NULL and uses the datetime basic data class.
Type:

EXEC sp_addtype birthday, datetime, 'null'

Be careful when selecting basic data types that implement user-defined data types. For example, in the United States, the form of a social insurance number is nnn-nn-nnnn. Although a social insurance number contains numbers, these numbers are only used to form an identifier and cannot be used for mathematical operations. Therefore, the common practice is to first create a user-defined social insurance number data type as a varchar data type, and then create a CHECK constraint to execute the format of the social insurance number stored in the table.

EXEC sp_addtype SSN, 'varchar (11) ', 'not Null'
GO
Create table ShowSSNUsage
(EmployeeID int primary key,
EmployeeSSN SSN,
CONSTRAINT CheckSSN CHECK (EmployeeSSN LIKE
'[0-9] [0-9] [0-9]-[0-9] [0-9]-[0-9] [0-9] [0 -9] [0-9] ')
)
GO

If the SSN column is usually used as the index, especially the key in the clustered index, if the SSN user-defined data type is implemented using the int data type, the key size can be reduced from 11 bytes to 4 bytes. This reduction in the size of the key can improve data retrieval. Improving data retrieval efficiency and avoiding the CHECK constraints are usually more important than performing extra conversion from int to character format when the SSN value is displayed or modified.