SQL Server service on, off, and data types

One, SQL Server services

Enter cmd  in Start-run to close the database service through net stop mssqlserver        
                                         net start mssqlserver        Open
                                        net Pause mssqlserver        Pause
                                        net  Continue mssqlserver Continue

Ii. Types of data

SQL server2008 Field type detailed

The bit integer bit data type is an integer whose value can be only 0, 1, or null. This data type is used to store data that has only two possible values, such as Yes or No, True or false, on, or off.

Note: A data type that is space-saving and should be used as much as possible if it satisfies the requirements.

The tinyint integer tinyint data type can store integers from 0 to 255. It is useful when you only intend to store a limited number of values. This data type occupies 1 bytes in the database.

Note: If the bit type is too monotonous to meet your needs, you can consider using the tinyint type because this type is relatively safe and does not accept embedding of malicious script content.

The smallint integer smallint data type can store integers from 15 powers (-32768) of 2 to 15 powers (32767) of 2. This data type is useful for storing numeric data that is often scoped to a specific range. This data type occupies 2 bytes of space in the database.

Note: If the tinyint type is too monotonous to meet your needs, you can consider using the smallint type because this type is relatively safe and does not accept embedding of malicious script content.

the int int data type can store integers from 2 to 31 powers (-2147483648) to 2 to 31 powers (2147483 647). Almost all numeric data stored in a database can be used with this data type. This type of data occupies 4 bytes in the database.

Note: If smallint is not able to meet your needs, you can consider using a larger int type.

The decimal exact numeric decimal data type can be used to store numeric data of a fixed precision and range from 10 to 38 power-1 to 10 of 38 power-1. When you use this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored on the right of the decimal point

Numeric exact numeric numeric data types are similar to decimal.

SmallMoney currency Type the smallmoney data type is used to represent money and currency values. This data type can store data from 214748.3648 to 214748.3647, accurate to one out of 10,000 of the currency unit

The Money currency Money data type is used to denote currency and monetary values. This data type can store data from 922 billion to 922 billion, accurate to one out of 10,000 of the currency unit

Float approximate numeric float data type is an approximate numeric type that is used by floating point numbers. It is said that the floating-point number is approximate, because not all the numbers in its range can be accurately represented. Floating-point numbers can be any number from -1.79E+308 to 1.79E+308

Real approximate numeric real data types, like floating-point numbers, are approximate numeric types. It can represent a floating-point number between -3.40E+38 and 3.40E+38.

smalldatetime datetime type smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, accurate to one minute

A datetime datetime type is used to represent a date and time. This data type stores all date and time data from January 1, 1753 to December 31, 9999, accurate to 1% seconds or 3.33 milliseconds.

Cursor Special data Type the cursor datatype is a special data type that contains a reference to a cursor. This data type is used in stored procedures and cannot be used when creating tables

Timestamp Special data type timestamp data types are special data types that are used to create a database-wide unique digital. There can be only one timestamp column in a table. Each time you insert or modify a row, the value of the timestamp column changes. Although it has "time" in its name, the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.

uniqueidentifier special data type uniqueidentifier data types are used to store a globally unique identifier, which is a GUID. The GUID is truly globally unique. This number has little chance of being rebuilt in another system. You can initialize a column with a unique identifier by using the NEWID function or by converting a string to a unique identifier.

The char char data type is used to store fixed-length, non-uniform encoded data of the specified lengths. When you define a column for this type, you must specify a column length. This data type is useful when you always know the length of the data you want to store. For example, when you store data by zip code plus 4 character formatting, you know that you always need to use 10 characters. This data type has a maximum column width of 8,000 characters.

varchar character varchar data types, like char types, are used to store non-uniform encoded character data. Unlike the char type, this data type is variable length. When you define a column for that data type, you specify the maximum length of the columns. The biggest difference to the char data type is that the length of the stored data is not the length of the column.

The text character text data type is used to store a large number of non-uniform encoded character data. This data type can have a maximum of 231-1 or 2 billion characters.

The nchar unified coded character type nchar data type is used to store fixed-length, uniformly encoded character data. Uniform encoding uses a double-byte structure to store each character, rather than a single byte (in normal text). It allows for a large number of extended characters. This data type can store 4000 characters, which is used to increase the byte space by one times.

The nvarchar unified encoded character type nvarchar data type is used as a uniformly encoded character type data with variable length. This data type can store up to 4000 characters, and the byte space used increases by one times.

The ntext unified coded character type ntext data type is used to store a large number of uniformly encoded character data. This data type can store 230-1 or nearly 1 billion characters, and the byte space used increases by one times

Binary binary data type binary data type is used to store fixed-length binary data up to 8000 bytes long. You should use this data type when the contents of the input table are close to the same length.

The varbinary binary data type varbinary data type is used to store variable-length binary data up to 8000 bytes long. You should use this data type when the content size of the input table is variable

Image binary data Type the image data type is used to store variable-length binary data up to 231-1 or approximately 2 billion bytes

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

(1) Binary data type binary data includes binary, Varbinary, and Image

Binary data types can be either fixed-length (binary) or variable-length. binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes.

The data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

(2) Character data type

The types of character data include Char,varchar and Text character data that are any combination of letters, symbols, and numbers.

Varchar is a variable-length character data whose length does not exceed 8KB.

Char is a fixed-length character data with a length of up to 8KB.

ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the Text data type.

(3) Unicode data type

Unicode data types include Nchar,nvarchar and ntext in Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server Setup, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. 　　With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types. In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.

(4) Date and time data type

Date and time data types include datetime and smalldatetime two types of date and time data types are composed of valid dates and times. For example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before, the time in the last data type is a moment before, and the date in the back. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 Storage bytes). 　　When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage). The format of the date can be set. 　　The command to format the date is as follows: Set DATEFORMAT {format | @format _var| Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. 　　By default, the date format is MDY. For example, when the set DateFormat YMD is executed, the date is formatted as a month and day form; When the set DateFormat DMY is executed, the date is in the form of sun and moon.

(5) Numeric data types

Numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integer integers consisting of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint. The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the tinyint data type stores the data. The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage). 　　When you use the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space). The exact data type for the data in SQL Server is Decimal and Numeric. 　　The amount of storage space that this data occupies is determined by the number of bits in the data. In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded. 3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

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

In Microsoft SQL Server, the data type of the currency data is smallmoney 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 that were not previously mentioned. 　　There are 3 special data types, namely Timestamp, Bit and uniqueidentifier. The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. 　　Timestamp data is not related to inserting data or date and time. The Bit is made up of 1 or 0. When representing true or false, on, or off, use the Bit data type. 　　For example, ask if a client request for each visit can be stored in a column of this data type. The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness. For example, using this data type in the customer Identification Number column can differentiate between different customers.

2. User-defined data types

User-defined data types are based on the data types that are available in Microsoft SQL Server. You can use a user-defined data type when you must store the same data type in several tables, and to ensure that the columns have the same data type, length, and nullability. 　　For example, you can define a data type called Postal_Code, which is based on the Char data type. When you create a user-defined data type, you must provide three numbers: the name of the data type, the system data type that you are basing on, and the nullability of the data type.

(1) Create a user-defined data type

You can use Transact-SQL statements to create user-defined data types. System stored procedures sp_addtype can be used to create user-defined data types. The syntax is as follows: sp_addtype {type},[,system_data_bype][, ' null_type ') where type is the name of the user-defined data type. System_data_type are system-supplied data types, such as Decimal, Int, Char, and so on. 　　Null_type indicates how the data type handles null values and must be enclosed in single quotes, such as ' null ', ' NOT null ', or ' nonull '. 　　Example: Use cust Exec sp_addtype ssn, ' Varchar (one) ', ' not Null ' creates a user-defined data type SSN, which is based on a system data type that is variable length to 11 characters, and does not allow nulls. 　　Example: Use cust Exec sp_addtype birthday,datetime, ' Null ' to create a user-defined data type birthday, which is based on the system data type is datetime and allows null. Example: Use master Exec sp_addtype telephone, ' varchar ', ' not Null ' eexc sp_addtype fax, ' varchar ', ' Null ' to create two data types , namely telephone and fax

(2) Delete user-defined data types

Can be deleted when the user-defined data type is not required. 　　The command to delete a user-defined data type is sp_droptype {' type '}. Example: Use master Exec sp_droptype ' ssn ' NOTE: This user-defined data type cannot be deleted when a column in a table is also using a user-defined data type, or if there is a default or rule on it.

Description of the field type for SQL Server

The following is a description of the field type for SQL SERVER7.0 or later. For a description of the field type for SQL SERVER6.5, refer to the instructions provided by SQL Server.

Field type Description bit   0 or 1 integer number int   from -2^31 (-2,147,483,648) to 2^31 (2,147,483,647) integer number smallint    integer numbers from -2^15 (-32,768) to 2^15 (32,767) tinyint   integer numbers from 0 to 255     decimal   from-10 ^38 to 10^38-1 the number of fixed precision and significant digits numeric   decimal synonyms     money   from -2^63 (- 922,337,203,685,477.5808) to 2^63-1 (922,337,203,685,477.5807) Currency data, minimum currency unit 10 per thousand smallmoney   from-214, 748.3648 to 214,748.3647 currency data, minimum currency unit 10 per thousand     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   date and time data from January 1, 1753 to 9999 12th 31, The minimum time unit is 3% seconds or 3.33 milliseconds smalldatetime   date and time data from January 1, 1900 to June 6, 2079, Min. minutes     Timestamp    timestamp, unique number of a database width uniqueidentifier   global Unique identifier GUID     char   Fixed-length non-Unicode character data with a maximum length of 8000 varchar   variable length non-Unicode character data with a maximum length of 8000 text   variable length non-Unicode character data with a maximum length of 2 ^31-1 (2G)     nchar   Fixed-length Unicode character data with a maximum length of 8000 nvarchar   variable length Unicode character data with a maximum length of 8000 ntext    variable length Unicode character data with a maximum length of 2^31-1 (2G)     binary   fixed-length binary data up to 8000 varbinary    variable length binary data with a maximum length of 8000 image   variable length binary data with a maximum length of 2^31-1 (2G)

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Bit 1 bits with a value of 0 or 1 Int Integer 4 bytes with a value of -2^31~2^31-1

Smallint 2 bytes with a value of -2^15~2^15-1

Tinyint 1 bytes with a value of 0~255

Decimal (p,s) numeric data with a fixed precision of p and a width of s

Numeric Money 8 bytes, stored in currency type with a value of -2^63~2^63-1

Small Money 4 bytes, holds currency type, value is -214748.3648~+214748.3647 approximate numeric data type

float (n) n between 1~24, 4 bytes, 7-bit precision n=1~7 to real n between 25~53, 8 bytes, 15-bit precision =8~15 to float

DateTime 8 Bytes, describing the date and time of day, with a value of 1/300 seconds in accuracy

smalldatetime 4 bytes, describing the date and time of day, with a precision of minutes

Cursor references to Cursors

Timestamp 8 Bytes, the only data stored in the database

uniqueidentifier 16 bytes, holding the globally unique identifier (GUID)

Char (n) fixed length of non-Unicode string, n=1~8000

Character (n) Varchar (n) variable length, non-Unicode string n=1~8000

Char varying (n) the variable-length non-Unicode data in the Text Server code page. Maximum length is 231-1 characters

Nchar fixed-length Unicode string n=1~4000

National character (n), national char (n) Nvarchar fixed-length Unicode string n=1~4000

national character varying (n) Ntext variable length Unicode data with a maximum length of 230-1 characters

National text binary (n) fixed-length binary data, n between 1~8000, storage space is n+4 bytes

Varbinary (n) variable-length binary data, n=1~8000

Binary varying (n) tmage variable-length binary data with a size of 0~231-1

Attention:

1) For numeric data types, the width (scale) refers to the number of digits that are stored after the decimal point, and the precision (precision) is the number of digits that can be stored that contains the decimal point.

2) The storage width of money and small money is 4.

3) The timestamp column value is automatically updated when each row is updated, and the timestamp column cannot be part of the keyword or keyword.

4) The unique identity data type cannot use arithmetic operators such as +,-and so on, and this data type can only use equality comparison operations. Unicode is the standard for uniformly storing data for all character sets. It uses two times as much storage space as non-Unicode data stores

SQL Server service on, off, and data types