SQL Server Data Type

This article from: http://www.baanerp.com/post/48.html
A data type is a type of data attribute, indicating the type of information represented by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics. The types and names of the defined data types are more or less different. Sqlserver provides 25 data types:
· Binary [(n)]
· Varbinary [(n)]
· Char [(n)]
· Varchar [(n)]
· Nchar [(n)]
· Nvarchar [(n)]
· Datetime
· Smalldatetime
· Decimal [(P [, S])]
· Numeric [(P [, S])]
· Float [(n)]
· Real
· Int
· Smallint
· Tinyint
· Money
· Smallmoney
· Bit
· Cursor
· Sysname
· Timestamp
· Uniqueidentifier
· Text
· Image
· Ntext

(1) binary data type

Binary data includesBinary, varbinary, and image
The binary data type can be either fixed-length (Binary) or variable-length.
Binary [(n)] is a fixed N-bit binary data. The value range of N is from 1 to 8000. The memory size is n + 4 bytes.
Varbinary [(n)] is a binary data with N-bit varying length. The value range of N is from 1 to 8000. The memory size is n + 4 bytes, not n Bytes.
The data stored in the image data type is stored as a bit string, which is not explained by SQL Server and must be explained by the application. For example, an application can store data in the image data type in BMP, tief, GIF, and JPEG formats.

(2) character data type

The types of character data includeChar, varchar, and text
Character data is composed of any combination of letters, symbols, and numbers.
Varchar is a variable-length data with a length of no more than 8 KB. Char is a fixed-length character data with a maximum length of 8 KB. ASCII data larger than 8 KB can be stored as text data. For example, because all HTML documents are ASCII characters and generally exceed 8 kb in length, these documents can be stored in SQL Server as text data.

(3) Unicode Data Type

Unicode data types includeNchar, nvarchar, and ntext
In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by specific character sets. You can select a character set during SQL Server installation. The Unicode data type allows columns to store any characters defined by the Unicode standard. The Unicode Standard contains all characters defined in various character sets. When Unicode data types are used, the odds are two times the bytes used by non-Unicode data types.
In SQL Server, Unicode data is stored as nchar, nvarchar, and ntext data. Columns stored with this character type can store characters in multiple character sets. When the length of a column changes, the nvarchar character type should be used. At this time, a maximum of 4000 characters can be stored. When the column length remains unchanged, the nchar character type should be used. Similarly, a maximum of 4000 characters can be stored. When the ntext data type is used, this column can store more than 4000 characters.

(4) Date and Time Data Types

Date and Time data types includeDatetime and smalldatetimeTwo Types
The date and time data types are composed of valid date and time. For example, valid date and time data includes "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01 am 8/17/98 ". The previous data type is before the date, the time is before the last data type is before the time, and the date is after. In Microsoft SQL Server, the date and time data types include datetime and smalldatetime. The stored date range starts from January 1, January 1, 1753, end on April 9, December 31, 9999 (each value requires 8 storage bytes ). When the smalldatetime data type is used, the stored date range is from January 1, January 1, 1900 to January 1, December 31, 2079 (each value requires four storage bytes ).
The date format can be set. The command to set the date format is as follows:
Set dateformat {format | @ format _ var |
Where, format | @ format_var is the date order. Valid parameters include mdy, DMY, ymd, ydm, MYD, and dym. By default, the date format is mdy.
For example, after you run set dateformat ymd, the date format is in the format of year, month, and day. After you run set dateformat DMY, the date format is in the format of day, month, and year.

(5) Digital Data Types

Numeric data only contains numbers. Numeric data types include positive and negative numbers, decimals (floating point numbers), and Integers
Integers are composed of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the integer storage data type isInt, smallint, and tinyint. The range of int data type stored data is greater than that of smallint data type stored data, while that of smallint data type stored data is greater than that of tinyint data type stored data. The range of data stored by dogs using int data is from-2 147 483 648 147 to 2 483 647 (each value requires 4 bytes of storage space ). When the smallint data type is used, the data storage range is from-32 768 to 32 767 (each value requires 2 bytes of storage space ). When the tinyint data type is used, the data storage range is from 0 to 255 (each value requires 1 byte storage space ).
The exact data types in SQL Server are decimal and numeric. The storage space occupied by such data is determined based on the number of digits after the number of digits of the data.
In SQL Server, the Data Types of approximate decimal data are float and real. For example, the 1/3 score is recorded. 3333333. It can be accurately expressed when an approximate data type is used. Therefore, the data retrieved from the system may be different from the data stored in this column.

(6) currency data indicates the number of positive or negative currencies.

In Microsoft SQL Server, the data type of currency data isMoney and smallmoney

The money data type requires 8 storage bytes, And the smallmoney data type requires 4 Storage bytes.

(7) Special Data Types

Special data types include data types not mentioned previously. There are three special data types:Timestamp, bit, and uniqueidentifier.
Timestamp is used to indicate the sequence of SQL Server activities, in the binary projection format. The timestamp data has no relationship with the inserted data or the date and time.
Bit is composed of 1 or 0. The BIT data type is used when it indicates true or false, on, or off. For example, you can query whether a client request for each access can be stored in a column of this data type.
Uniqueidentifier consists of 16-byte hexadecimal numbers, indicating a globally unique one. GUID is useful when the record rows in a table must be unique. For example, you can use this data type in the Customer ID column to distinguish different customers.

2. User-Defined Data Types

The user-defined data type is based on the data type provided in Microsoft SQL Server. When several tables must store the same data type, and to ensure that these columns have the same data type, length, and can be empty, you can use user-defined data types. For example, you can define a data type called postal_code, which is based on the char data type.
When creating a user-defined data type, three data types must be provided: the data type name, the system data type based on, and the data type can be empty.

(1) create a user-defined data type

You can use the transact-SQL statement to create user-defined data types. The system stored procedure sp_addtype can be used to create user-defined data types. The syntax format is as follows:
Sp_addtype {type}, [, system_data_bype] [, 'null _ type']
Type is the name of the user-defined data type. System_data_type is the data type provided by the system, such as decimal, Int, and char. Null_type indicates how the data type handles null values. It must be enclosed by single quotation marks, such as 'null', 'not null', or 'nonull '.
Example:
Use cust
Exec sp_addtype SSN, 'varchar (11) ', "Not null'
Create a user-defined data type SSN. The system data type is a string of 11 characters and cannot be empty.
Example:
Use cust
Exec sp_addtype birthday, datetime, 'null'
Create a user-defined data type birthday, which is based on the system data type datetime and can be empty.
Example:
Use master
Exec sp_addtype telephone, 'varchar (24), 'not null'
Eexc sp_addtype fax, 'varchar (24) ', 'null'
Create two data types: telephone and fax

(2) Delete user-defined data types

You can delete a data type that is not required. The command for deleting user-defined data types is sp_droptype {'type '}.
Example:
Use master
Exec sp_droptype 'ssn'
Note: When columns in a table are still using user-defined data types, or are bound with default or rules, the user-defined data types cannot be deleted.

Description of SQL Server Field Types

The following describes the Field Types of SQL Server7.0 and later versions. For more information about the field types of SQL Server 6.5, see the description provided by SQL Server.

Field Type	Description
Bit	Integer number 0 or 1
Int	Integer number from-2 ^ 31 (-2,147,483,648) to 2 ^ 31 (2,147,483,647)
Smallint	Integer number from-2 ^ 15 (-32,768) to 2 ^ 15 (32,767)
Tinyint	Integer number from 0 to 255
Decimal	Number of fixed precision and valid digits from-10 ^ 38 to 10 ^ 38-1
Numeric	Synonym for decimal
Money	From-2 ^ 63 (-922,337,203,685,477.5808) to 2 ^ 63-1 (922,337,203,685,477.5807), the minimum unit of currency is 10 thousandth.
Smallmoney	From-214,748.3648 to 214,748.3647 of monetary data, the minimum unit of currency is 10 points per cent.
Float	Variable-precision numbers from-1.79e + 308 to 1.79e + 308
Real	Variable-precision numbers from-3.04e + 38 to 3.04e + 38
Datetime	The minimum time unit is 3% seconds or 3.33 milliseconds from the date and time data of January 1
Smalldatetime	The minimum time unit is minute.
Timestamp	Timestamp, a unique number of database widths
Uniqueidentifier	Guid
Char	Fixed Length non-Unicode bytes data with a maximum length of 8000
Varchar	Variable Length non-Unicode bytes data, up to 8000
Text	Non-Unicode bytes data with a maximum length of 2 ^ 31-1 (2g)
Nchar	Bytes data with a fixed length of Unicode. The maximum length is 8000.
Nvarchar	Extended Unicode bytes data, up to 8000
Ntext	Variable-length Unicode bytes. The maximum length is 2 ^ 31-1 (2g)
Binary	Fixed-length binary data, maximum length: 8000
Varbinary	Variable-length binary data; maximum length: 8000
Image	Variable-length binary data with a maximum length of 2 ^ 31-1 (2g)

DB2 provides the query language SQL (Structured Query Language) for relational databases. It is a colloquial, easy-to-learn and easy-to-understand syntax. Almost every database system must provide this language to represent relational operations, including data definition (DDL) and Data Processing (DML ). SQL was originally spelled as sequel. The prototype of this language was completed in the IBM San Jose laboratory in the name of "system R". After many usability and efficiency tests within IBM and among others, the results were quite satisfactory, and decided to develop IBM products on the technical basis of system R. Moreover, the American National Institute of Standards (ANSI) and the International Organization for Standardization (ISO) comply with a standard relational data language definition that is almost Based on ibm SQL at 1987.

I. Data Definition Language)
A document language is a language defined for the format and form of a document. It is the first thing that every database needs to create, the table relationships, column primary keys in the table, and the reference relationships between tables are all planned at the beginning.

1. Create a table:
Create Table table_name (
Column1 datatype [not null] [not null primary key],
Column2 datatype [not null],
...)
Note:
Datatype -- is the data format. For details, see the table.
Nut null-do not allow empty data (no data is provided ).
Primary Key -- is the primary key of the table.

2. Change the table
Alter table table_name
Add column column_name datatype
Note: Add a column (the syntax of a column is not deleted ).
Alter table table_name
Add primary key (column_name)
Note: You can change the table definition to set a column as a primary key.
Alter table table_name
Drop primary key (column_name)
Delete the definition of the primary key.

3. Create an index
Create index index_name on table_name (column_name)
Note: index the column of a table to increase the query speed.

4. Delete
Drop table_name
Drop index_name

Ii. Data Format datatypes
Smallint
An integer of 16 bits.
Interger
An integer of 32 bits.
Decimal (P, S)
The exact value of P and the decimal integer of S. The exact value of P refers to the number of digits (digits) in all, and the value of S refers to the decimal number.
The number of digits after the vertex. If not specified, the system is set to P = 5; S = 0.
Float
32-bit real number.
Double
The real number of 64-bit RMB.
Char (N)
A string of N length. N cannot exceed 254.
Varchar (N)
A string with an unfixed length and a maximum length of N. N cannot exceed 4000.
Graphic (N)
The Unit is the same as char (N), but it is a double-bytes character. N cannot exceed 127. This form is
Supports two character length fonts, such as Chinese characters.
Vargraphic (N)
A two-character string with a variable length and a maximum length of N. N cannot exceed 2000.
Date
Contains the year, month, and date.
Time
Contains hours, minutes, and seconds.
Timestamp
Including year, month, day, hour, minute, second, 1‰ seconds.

Combined Query
Combined Query means that the queried data source is not only a single table, but more than one
Table.
Select *
From Table1, Table2
Where table1.colum1 = table2.column1
Note:
1. query the data in the two tables with the same column1 value.
2. Of course, the columns of the two tables must be in the same format.
3. A complex query may use many tables.

Integrated Query:
Select count (*)
From table_name
Where column_name = xxx
Note:
There are several pieces of qualified materials to query.
Select sum (column1)
From table_name
Note:
1. Calculate the sum. The selected column must be a handful of numbers.
2. In addition, AVG () is an integrated query that calculates the mean, Max (), and min () for the maximum and minimum values.
Select column1, AVG (column2)
From table_name
Group by column1
Having AVG (column2)> xxx
Note:
1. Group by: Taking column1 as a group, the average value of column2 must be the same as the keywords of AVG, sum, and other integrated queries.
.
2. Having: it must be used with group by as an integral constraint.

Compound Query
Select *
From table_name1
Where exists (
Select *
From table_name2
Where conditions)
Note:
1. The condition of Where can be another query.
2. exists indicates whether or not exists.
Select *
From table_name1
Where Column 1 In (
Select column1
From table_name2
Where conditions)
Note:
1. In is followed by a set, indicating that column1 exists in the set.
2. Select data format must comply with column1.

Other queries
Select *
From table_name1
Where column1 like 'x %'
Note: Like must echo the following 'x % 'to indicate a string starting with X.
Select *
From table_name1
Where Column 1 In ('Xxx', 'yyy ',..)
Note: In is followed by a set, indicating that column1 exists in the set.
Select *
From table_name1
Where column1 between XX and YY
Description: Between indicates that the value of column1 is between XX and yy.

3. Change Information:
Update table_name
Set column1 = 'xxx'
Where conditoins
Note:
1. Change a field to 'xxx '.
2. conditions is the condition to be met. If there is no where, all the columns of the entire table will be changed.

4. Delete materials:
Delete from table_name
Where conditions
Note: delete qualified materials.

Note: If the where condition is followed by a date comparison, different databases have different expressions. The details are as follows:
(1) If it is an Access database, it is: where mydate> # 2000-01-01 #
(2) If it is an Oracle database, it is: where mydate> cast (' 2000-01-01 'As date)
Or: Where mydate> to_date (' 2000-01-01 ', 'Yyyy-mm-dd ')
Write in Delphi:
Thedate =' 2000-01-01 ';
Query1. SQL. Add ('select * from ABC where mydate> cast ('+ ''' + thedate + ''' + 'as date )');

If the datetime type is compared:
Where mydatetime> to_date ('2017-01-01 10: 00: 01' , 'Yyyy-mm-dd hh24: MI: ss ')