SQL Server Basics

1. Why do SQL Server table names add square brackets?

This is not required, but if the table name or field name references a keyword in SQL Server, the database does not recognize whether this is a keyword or a table name (or field name) that must be added.

For example, a table named User,user is a keyword in SQL Server, so you have to do this when querying:

SELECT * FROM [user]

Otherwise you will get an error.

Similarly, if the user belongs to a field name, it is also added, as

SELECT * from table name WHERE [user]=1

2. Go,begin,end in SQL

　　

Go sends a signal to the SQL Server utility to end a batch of Transact-SQL statements. Go is executing the T-SQL statements in batches. (One step is successful, the next step, i.e. one go)

The BEGIN and END statements are used to combine multiple Transact-SQL statements into a single logical block. The BEGIN and END statements can be used anywhere a control flow statement must execute a block of statements that contains two or more Transact-SQL statements.

For example, you do not need to use the BEGIN or END statement when the IF statement controls only the execution of a Transact-SQL statement:

IF (@ @ERROR <> 0)
   SET @ErrorSaveVariable = @ @ERROR

If @ @ERROR is 0, only the SET statement is skipped.

Use the BEGIN and END statements to make the IF statement skip the statement block when the result evaluates to FALSE:

IF (@ @ERROR <> 0)
BEGIN
   SET @ErrorSaveVariable = @ @ERROR
   
         CAST (@ErrorSaveVariable as VARCHAR (10))
END

The BEGIN and END statements must be used in pairs: neither can be used alone. The BEGIN statement appears on a single line, followed by a Transact-SQL statement block. Finally, the end statement appears separately in one row, indicating the end of the statement block.

3.MySQL statement converted to SQL Server

ALTER TABLE Order_Detail ADD constraint fk_relationship_1 foreign key (book_id)
    References book (ID) on the delete restrict on update restrict;

The syntax for SQL Server is as follows:

On DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}
Specifies the action to take if rows in the created table have referential relationships and the referenced rows have been removed from the parent table. The default value is NO ACTION.

NO ACTION
The database engine throws an error and rolls back the delete operation on the row in the parent table.

CASCADE
If a row is deleted from the parent table, the corresponding row is removed from the reference table.

SET NULL
If the corresponding row in the parent table is deleted, all values that make up the foreign key are set to NULL. To perform this constraint, the foreign key column must be nullable.

SET DEFAULT
If the corresponding row in the parent table is deleted, all values that make up the foreign key are set to the default values. To perform this constraint, all foreign key columns must have a DEFAULT definition. If a column can be null, and no explicit default value is set, NULL is used as the implicit default value for the column.

The inside of MySQL  
Switch to SQL Server and  you can switch to on  DELETE  NO ACTION
You can also not write because the default value is no ACTION.

3. MySQL on delete restrict differs from on ORDELETE Cascade

FOREIGN KEY constraints The meaning of the child table:
Insert/update is not allowed on a child table if a candidate key is not found in the parent table

What foreign key constraints mean to the parent table:
When you make a update/delete on a parent table to update or delete a candidate key that has one or more matching rows in the child table, the behavior of the parent table depends on: the on update/on delete clause that is specified when the foreign key of the child table is defined, INNODB supports 5 ways, broken down as follows

. Cascade Way
Synchronize update/delete out a matching record of a child table when Update/delete records on the parent table
On DELETE cascade available from mysql3.23.50; On UPDATE cascade available starting from mysql4.0.8

. Set NULL mode
Set the column of the matching record on the child table to NULL when the record is update/delete on the parent table
Note that the foreign key column of the child table cannot be not NULL
On delete Set NULL is available from mysql3.23.50; On update set NULL is available starting from mysql4.0.8

. No Action Mode
If there are matching records in the child table, the Update/delete action is not allowed for the parent table corresponding to the candidate key
This is the ANSI SQL-92 standard, starting from mysql4.0.8 support

. Restrict Way
Same no action, check foreign KEY constraints immediately

　　

SQL Server Basics