Introduction to SQL Server database table management

First, the basic concept of the table

A table is a data object that contains all the data in a database, and a table definition is a column collection

Data is organized in a table similar to the spreadsheet in that it is organized in rows and columns

Each row represents a unique record, and each column represents a field in the record

(1), data integrity

Data integrity refers to the accuracy and reliability of data, which should prevent the existence of inconsistent semantic data in the database and prevent invalid operations or error messages caused by the input/output of the error message.

1. Entity integrity

Entity integrity defines a row as a unique entity for a particular table

Entity integrity enforces the integrity of a table's identifier column or primary key by Unque (unique) index, unque constraint, or PRIMARY key (primary key) constraints

2. Domain Integrity

Domain integrity refers to the validity of a specific column

You can enforce domain integrity restriction types (by using data types), restrict formatting (by using check constraints and rules), or limit the range of possible values (by using FOREIGN KEY constraints, check constraints, default definitions, not NULL definitions, and rules)

3. Referential integrity

Referential integrity preserves the relationships defined between tables when you enter or delete rows

Referential integrity is based on foreign key (foreign key) and check (check) constraints, and the relationship between a foreign key and a primary key or a key to a unique key

Referential integrity ensures that key values are consistent across all tables

4. User-defined Integrity

User-defined integrity naming you can define specific business rules that are not part of any other integrity category, and all integrity categories support user-defined integrity

(2), primary key

The primary key uniquely identifies the row data in the table, and one primary key value corresponds to a row of data

A primary key consists of one or more character Fu Che whose values are unique, null values are not allowed (null), and a table can have only one primary key

If a primary key consists of more than one column, one column will allow duplicate values, but the values of all columns in the primary key must be unique

Second, use SSMS Operation data table

Each database in SQL Server can store up to 2 billion tables, each table can have 1024 columns

Table row data and total size are only limited by available storage space and can store up to 8060 bytes per row

(1), data type

A data type is a property of data that specifies the type of data that an object can hold, such as Integer data, character data, currency data, date and time data, binary strings, and so on.

Data type Category: "Precision numbers, approximate numbers, dates and times, strings, Unicode strings, binary strings, other data types"

"Accurate numbers"

int: An integer data type that can be used by almost all numeric data types to store integers between c from-(-2147483648) to (2147483648), which occupies 4 bytes of space.

BigInt: Store data values beyond the int data type support range, you can store-to the value between, it points to use 8 bytes of space

SmallInt: Stores some numeric type data that is often limited to a specific range and can store integers from-(-32768) to (32768), which occupies 1 bytes of space

Tinyint: Useful for storing a limited number of values, storing a value from 0 to 255, which occupies 1 bytes of space

bit: integer data whose value can only be 0, 1, or null (null value). It can only store data that has only two possible values, such as "Yes or no", "Falce or on, off"

Decimal: Decimal, used to store numeric data of fixed precision and range from-to-1. Use it to specify range and precision. ' Range ' is the total number of digits that can be stored around small data points. ' Precision ' is the number of digits stored to the right of the decimal point

Numeric:numeric is functionally superior to decimal

Money: Used to indicate a monetary and currency value. Accurate to one out of 10,000 of the monetary unit. It takes up 8 bytes of space, from 922337203685477.5808 to 922337203685477.5807

Sumallmoney:smallmoney is functionally equivalent to money. From-214748.3648 to 214748.3647

"Approximate number"

float: An approximate numeric data type used to represent floating-point numeric data. Floating-point data is approximate, so not all values within the range of the data type can be accurately represented. Represents any number of values between -1.79E+308 and 1.79E+308

Real: An approximate numeric data type used to represent floating-point numeric data. Represents a floating-point number with a value between -3.40E+38 and 3.40E+38

"Date and Time"

Date: Used to represent dates. It takes up 3 bytes of space and stores all date data from January 1 to A.D. December 31, 9999

Time: Used to represent a period of a day. It takes up 5 bytes of space

DateTime: Used to represent dates and times. It takes up 8 bytes of space. Stores all date and time data between January 1, 1753 and December 31, 9999

DateTime2: You can view datetime2 as an extension of an existing datetime type. Its data range is larger, the default decimal precision is higher

DateTime offset: Used to represent a date and time. The time zone is increased compared with the datetime2

smalldatetime: Used to represent dates and times from January 1, 1900 to June 6, 2079, accurate to one minute, this data type occupies 4 bytes of space