T-SQL entry strategy create datasheet

Using PRIMARY KEY constraints

Primary keys do not allow duplicates 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 multiple 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,COL2,COL3)

);

Using Uniqueness constraints

Uniquely unique to force the uniqueness of a non-key column, we can define a unique constraint on a field or on more than one field.

--1 single field UNIQUE constraint

IF object_id (' testtable ', ' U ') is not NULL DROP TABLE testtable;

CREATE TABLE TestTable

(

Col1 Char (10),

col2 int,

col3 float,

Unique (col1)

);

--2 a multiple-field unique 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:

Similar points:

Duplicate values are not allowed on the combination of a primary key column or a progressive column, and duplicate values are not allowed on the combination of columns or columns that are defined as uniqueness constraints, and their columns create a unique index.

Different points:

Only one primary key can be defined in the table, but multiple unique constraints may be defined, the column that contains the primary key does not allow null values, but the uniqueness constraint column allows null values

Use non-null constraints

A field with a not NULL constraint has a value that is not allowed to be null (NULL)

IF object_id (' testtable ', ' U ') is not NULL DROP TABLE testtable;

CREATE TABLE TestTable

(

Col1 Char (a) not null unique,--non-null constraint and Uniqueness constraint

col2 int NOT NULL,--Non-null constraint

col3 float

);

Using Default Constraints

A field may not want to enter a value directly or temporarily, and hopefully it will be able to form an initial value or some field value is taken from somewhere else. You can use the default constraint at this time.

IF object_id (' usertable ', ' U ') is not NULL DROP TABLE usertable;

CREATE TABLE usertable

(

Username varchar (20),

Loginuser varchar () DEFAULT user,

Logintime datetime DEFAULT getdate (),

uservocation varchar DEFAULT ' computer and its related '

);

Using CHECK constraints

To avoid human errors when entering data, you can resolve (check) by defining a method for checking constraints

IF object_id (' testtable ', ' U ') is not NULL DROP TABLE testtable;

CREATE TABLE TestTable

(

UserID varchar CHECK (userid like ' [A-z]%[1-4] ' and datalength (userid) =5),

--The 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

The temporary table table name is #为前缀的一类数据表是临时存储数据库tempdb中的一类数据库对象 with # or #. The temporary table with the # prefix is a local temporary table, valid within the current session, and invalid outside the session. Prefix # #的临时表是全局临时表所有会话都可以访问. The life cycle of a temporary table is the life cycle of a session that creates a temporary table, which persists as long as the session in which it was created exists. The datasheet is automatically saved to tempdb, and the tables will be deleted after the database restarts.

2: Table variables

A table variable is a variable that is used to store table data, as well as a generic variable using declare to declare

Whether it's a temp table or a table variable, you have to manipulate them as data tables.

3: The difference between a table variable and a temporary table

(1) Temporary tables saved on disk (logically stored on database tempdb) table variables are saved in memory.

(2) Accessing a temporary table generates log information, while accessing a table variable does not

(3) You can create indexes on temporary tables but not on table variables

(4) The temporary table requires a lock mechanism, while the table variable does not need