[SQL Server] "CREATE" of transact-SQL statements"

"Create" of a Transact-SQL statement ":

 

1. Create a database

Create Database Name

[On Primary

(Name = ..., Filename = ..., Size = ..., Maxsize = ..., Filegrowth = ...)

...

Log On

(Name = ..., Filename = ..., Size = ..., Maxsize = ..., Filegrowth = ...)

...

Filegroup file group name]

 

Note:

(1) do not use decimals in the size, maxsize, and filegrouwth parameters. For non-integer MB files, multiply the size by 1024 and convert it to kilobytes.

 

 

2. Create a table

Create Table Name

(Column name 1 data type {identify | not null | null },

Column name 2 data type {identify | not null | null },

...)

 

Note:

(1) null indicates that the column value can be null. Do not consider null values as 0 or Null String equivalent values of the column.

(2) identify is a counter, indicating that the value of this column is a group of incremental integer data. The default value is 1, and the growth step is 1 by default.

 

 

3. create constraints

Create Table Name

(Column name 1 data type {identify | notnull | null} [constraint name] constraint definition,

Column name 2 data type {identify | not null | null} [constraint name] constraint definition,

...

[Constraint name] constraint definition)

 

 

Name	Function	Implementation integrity
Primary Key	Define the primary key to ensure that the primary key columns do not repeat	Entity integrity
Unique	Ensure that the column does not contain duplicate values	Entity integrity
Foreign key	Defines foreign keys to ensure data consistency between data tables.	Integrity of reference
Check	Define the data range of certain columns in the table	Custom integrity
Default	Provide default values for column data	Custom integrity

 

4. Create an index

Create [unique] [clustered | nonclustered] Index name on table name (column name 1, column name 2 ,...)

 

Note:

(1) by default, the created index is the only non-clustered index.

(2) When creating an index, you must first create a clustered index and then create a non-clustered index. (The data location indicated by the pointer in a non-clustered index is determined after the clustered index is created)

 

 

5. Create a view

Create view <view Name> [(column name group)]

<Withencryption>

As <subquery>

<Withcheck option>

 

Note:

(1) only views can be created in the current database

(2) If the base table or view referenced by the view is deleted, the view cannot be used until a new base table or view is created.

(3) You cannot create an index on a view. You cannot reference a view in the rule, default, or trigger definition.

 

 

6. Create a rule

Create rule name

As rule expression

 

Note:

(1) Rule expressions can contain comparison operators and arithmetic operators, but not database object names or table column names.

(2) The bound column is represented by a formal parameter in the Rule expression. The @ symbol is added before the parameter.

 

 

7. Create default

Default create default name

As expression

 

Note:

(1) only one default constraint can be defined for each column.

(2) The default constraint is to verify the column value only when the insert statement is used.

(3) columns with identify attributes cannot be used by default.

 

 

8. Create a stored procedure

Create procedure process name [; version number]

[@ Parameter name parameter type [= default value] [Output]…]

[With recompile | encryption | recompile, encryption]

[For replication]

As SQL statement Group

 

Note:

(1) The put table name parameter is a return parameter.

(2) recompile is the recompilation option. The table name sqlserver does not save the execution plan of the stored procedure.

(3) replication is used to specify that the stored procedure created for replication cannot be executed on the subscription server.

 

 

 

9. Create a trigger

Create trigger name

On Table Name

With Encryption

For Delete, insert, update

Withappend

Not forreplication

As SQL statement Group trigger name

 

Note:

(1) with APPEND specifies that other triggers of the existing type should be added

(2) Not for replication indicates that when a trigger table is modified during a replication process, the trigger associated with the table cannot be executed.

 

The preceding statements are nine frequently used creation statements for T-SQL statements. They are also accumulated by net.