Transact-SQL data type (text/graphics/date and time/currency/specific type)

4.2.6 text and graphic data types Text and graphic data types are used to store a large number of non-Unicode and Unicode characters, as well as fixed-length and variable-length data types of binary data, including text, ntext, and image. L text is a variable-length data type used to store a large number of non-Unicode text data. Its capacity is theoretically 231-1 (2 147 483) bytes. The actual application depends on the storage space of the hard disk. In versions earlier than SQL Server 2000, a text object in the database is actually a pointer that points to a data page in 8 KB. These data pages are dynamically added and logically connected. In SQL Server 2000, text and image data are directly stored in the data rows of the table, rather than on different data pages. In this way, the space used to store text and image types is reduced, and the number of I/O operations on the disk to process such data is correspondingly reduced. L ntext is a variable-length data type used to store a large number of Unicode text data. Its theoretical capacity is 230-1 (1 073 741 823) bytes. Other ntext uses are basically the same as text. L The image type is a variable-length data type used to store a large amount of binary data. Its theoretical capacity is 231-1 (2 147 483 647) bytes. The storage mode of image data is the same as that of text data. It is usually used to store OLE objects such as graphics. When inputting data, like inputting binary data, you must add the starting symbol "0x" before the data as the binary identifier. 4.2.7 Date and Time Data Types The date and time data types represent the date and the time in the day, including the datetime type and the smalldatetime type. L datetime is a data type used to store the combination of date and time. It can store all the dates and times from, January 1, January 1, 1753 AD to, January 1, December 31, 9999 AD, with an accuracy of up to 3% seconds. The storage space occupied by datetime data is 8 bytes. The first 4 bytes are used to store the days before or after January 1, January 1, 1900. The values are positive and negative, and the positive number indicates the date after this date, A negative number indicates the date before this date. The last four bytes are used to store the number of milliseconds that have elapsed since the specified time. If the time part is omitted during input, the system defaults to 12: 00: 00: 000 am. If the date part is omitted, the system defaults to January 1, 1900. L The smalldatetime type is similar to the datetime type, but its storage date and time range is small, from January 1, January 1-20, 1900 to January 1, June 6. Its accuracy is also low, and it can only be accurate to the minute level. The value of the minute bit is obtained by rounding the value according to the number of seconds and taking 30 seconds as the limit. The storage space occupied by smalldatetime data is 4 bytes, the first two bytes are stored from the days since the base date of January 1, January 1, 1900, the last two bytes are used to store the minutes that have elapsed since the current day. 4.2.8 currency data type The currency data type is used to store currency or cash values, including the money and smallmoney types. When using the currency data type, the currency symbol should be added before the data so that the system can identify the currency of the country it is. If the currency symbol is not added, the system defaults to "￥ ". L money is a decimal value with four decimal digits. The value ranges from-263 (-9 223 372 036 854 775 808) )~ 263-1 (+ 9 223 372 036 854 775 807), accurate to 10 thousandth of the currency unit. The storage size is 8 bytes. L smallmoney-type currency data value range:-2 147 483 648 ~ + 2 147 between 483 and 647, accurate to 10 thousandth of the currency unit. The storage size is 4 bytes. 4.2.9 specific data types SQL Server 2000 contains some special data types for data storage, including Timestamp and uniqueidentifier. L The timestamp data type provides a unique value in the database range, which is equivalent to binary (8) or varbinary (8 ), however, when the column defined by it is updated or inserted into a data row, the value of this column is automatically updated. Each database table can have only one timestamp data column. If the column name in the table is "timestamp", the column type is automatically defined as timestamp. L The uniqueidentifier data type is used to store a 16-bit binary data, which is called a globally unique identifier (guid ). This data is generated by the newid () function of SQL Server 2000. The numbers produced by this function on computers around the world are not the same. 4.2.10 user-defined data types The sysname data type is provided by the system for users to customize the data type. This data type is defined as nvarchar (128), that is, it can store 128 Unicode characters or 256 general characters. 4.2.11 add data types As mentioned above, SQL Server 2000 has three new data types: bigint, SQL _variant, and table. The bigint type has been introduced in the integer type. L SQL _variant is a data type that stores various data types supported by SQL Server (except for text, ntext, timestamp, and SQL _variant. This data type greatly facilitates SQL Server development. L The table type is used to store the result set processed by the table or view. Table data types are not applicable to columns in tables, but can only be used for Transact-SQL variables and return values of user-defined functions. This type allows the variable to store a table, making it easier and quicker for the function or process to return query results.