Creating a data table for T-SQL import strategy

Use primary key constraints

Duplicate primary keys and null values are not allowed.

-- 1 single-field primary key

IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Col1 varchar (10 ),

Col2 int,

Col3 datetime,

Col4 numeric (10, 2 ),

Col5 xml,

Col6 image,

Primary key (col1)

);

-- 2 IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Col1 varchar (10 ),

Col2 int,

Col3 datetime,

Col4 numeric (10, 2 ),

Col5 xml,

Col6 image,

Primary key (col1, col2, col3)

);

Use uniqueness constraints

Unique is used to force the uniqueness of non-primary key columns. We can define unique constraints on one field or multiple fields.

-- 1 unique constraint for a single Field

IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Col1 char (10 ),

Col2 int,

Col3 float,

Unique (col1)

);

-- 2 unique multi-field Constraint

IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Col1 char (10 ),

Col2 int,

Col3 float,

Unique (col1, col2)

);

Similarities and differences between a primary key and a unique constraint:

Similarity:

Duplicate values are not allowed in primary key columns or gradually column combinations, and duplicate values are not allowed in combinations of columns or columns with defined uniqueness constraints, the column where they are located creates a unique index.

Differences:

Only one primary key can be defined in the table, but multiple unique constraints can be defined. The column where the primary key is located does not allow null values, but the unique constraint column allows null values.

Use non-null Constraints

The value of a field with the not null constraint cannot be NULL)

IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Col1 char (10) not null unique, -- non-NULL constraint and uniqueness Constraint

Col2 int not null, -- non-NULL Constraint

Col3 float

);

Use default Constraints

For some fields, you may not want to directly enter the value or do not enter it for the time being. You also want it to form an initial value or some field values are taken from other places. In this case, you can use the default constraint.

IF OBJECT_ID ('usertable', 'U') is not null drop table usertable;

Create table usertable

(

Username varchar (20 ),

Loginuser varchar (10) DEFAULT user,

Logintime datetime DEFAULT getdate (),

Uservocation varchar (50) DEFAULT 'computer and its relevance'

);

Use check Constraints

To avoid human errors during data input, you can define check constraints)

IF OBJECT_ID ('testtable', 'U') is not null drop table testtable;

Create table testtable

(

Userid varchar (10) CHECK (userid LIKE '[a-z] % [1-4]' and datalength (userid) = 5 ),

-- Age value cannot be 0

-- The length of the userid value must be 5

-- The last character in the userid value must be 1, 2, 3, or 4

-- The first character of the userid value must be a letter.

Age int CHECK (age> 0 AND age <= 150)

);

Create temporary tables and table Variables

1: temporary table

A type of data table whose names are prefixed with # Or # Is a type of database object in the temporary storage database tempdb. The temporary table prefixed with # is a local temporary table, which is valid in the current session and is not valid outside the session. The temporary table with the prefix ## is accessible to all sessions in the global temporary table. The life cycle of a temporary table is the life cycle of the session for creating a temporary table. As long as the session for creating a temporary table still exists, the temporary table will continue to exist. Data Tables are automatically saved to tempdb. After the database is restarted, these tables will be deleted.

2: Table Variables

Table variables are used to store table data. They are declared using declare as well as common variables.

Both temporary tables and table variables must be treated as data tables.

3: differences between table variables and temporary tables

(1) temporary tables are stored on disks (logically stored in the database tempdb) and Table variables are stored in the memory,

(2) access to the temporary table will generate log information, but access table variables will not

(3) You can create indexes for temporary tables, but cannot create indexes for table variables.

(4) temporary tables require a lock mechanism, while table variables do not.