1. Change the bit type of the SQLSERVER database to not Boolean (that is, change true to false; change false to true)
Example: update table set bit field = bit field-1
Recently, a friend asked me that it was very slow for him to delete millions or tens of millions of data from SQLSERVER. He analyzed the problem and gave some comments as follows, it may be useful to many people. In addition, I have never written a BLOG. Let's discuss it together.
If your hard disk space is small and you do not want to set the database log to the minimum value (because you want other normal logs to Be RECORD), and the speed requirement is high, we recommend that you use turncate table1 to clear all the data, because truncate is a DDL operation and does not generate rollback, and does not write logs faster. If there is auto-increment, it will be restored to 1, delete generates rollback. If you delete a table with a large amount of data, it will be slow, consume a lot of rollback segments, and record G-level logs; of course, if conditional deletion such as where time <'1970-3-10 'is required, you can use delete to record logs, but the answer is not acceptable, the SQL Server engine is designed to log the Delete operation. So far, there is no way to force some statements not to be recorded in the log. If the execution of Delete Table1 where Time <'2014-3-10 'involves many records, therefore, the log record is large (3-4 GB). If it is feasible, we recommend that you use the following method:
Select the record you want to keep to the new table. If you use Full Recovery Mode
The log may be large based on the number of Select INTO records
Select * into Table2 From Table1 Where Time> = '2017-03-10'
Then directly Truncate Table1. No logs are recorded in any recovery mode.
Truncate table Table1
Finally, rename Table2 to Table1.
EC sp_rename 'table2', 'table1'