SQL tips = "time of the fifth and last Thursday of the week

1. Select dateadd (day, 6-datepart (weekday, getdate (), getdate () => this Friday
Select dateadd (day,-datepart (weekday, getdate ()-2, getdate () => last Thursday
2. [SQL Server] reset the identity column and recount from 1 => DBCC Checkident ( [ Mytable ] , Reseed, 0 );
3. delete a database => truncate table userstatistics
Similarities and differences between truncate, delete, and drop

Note: The delete statement is a delete statement without the WHERE clause.
Similarities
Truncate, delete without WHERE clause, and drop both delete table data.

Note: The delete statement is a delete statement without the WHERE clause.
Similarities
Truncate, delete without WHERE clause, and drop both delete table data.

Differences:
(1). truncate and delete only delete data. Do not delete the table structure (Definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.
(2) The delete statement is DML, which is put into the rollback segement and takes effect only after the transaction is committed. If a corresponding trigger exists, it will be triggered during execution.
Truncate, drop is DDL, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. Trigger is not triggered.
(3) The delete statement does not affect the extent occupied by the table, and the high watermark keeps the original position unchanged.
Obviously, the drop statement releases all the space occupied by the table.
The truncate statement is released to the minextents extent by default, unless reuse storage is used; The truncate will reset the high water line (back to the beginning ).
(4). Speed, generally: Drop>; truncate>; Delete
(5). Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.
To delete some data rows, use Delete. Note that the WHERE clause should be included. The rollback segment should be large enough.
To delete a table, use drop
Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use Delete.
If you want to organize fragments in the table, you can use truncate to keep up with the reuse Stroage, and then re-import/insert data.

4. Relationship between SQL and DDL DML DCL

relational databases have three types of languages: DDL ), (Data Control Language DML ) and (Data Control Language DCL ), SQL is an integration of DDL , DML , and DCL .) standard relational database language.
SQL
DML statements include ( select , insert , and Delete and Update );
DDL statements include ( Create, drop, alter );
DCL statements include ( Grant, revoke ).
5. Update [base_coupon] Set [delivertype] =. delivertype
from [base_coupon] B
join [wangfujing_coupondb]. [DBO]. [base_coupon] A
On. id = B. ID