When you create and populate temporary tables in tempdb, use the select into command, rather than create table and insert…select, whenever possible. The select into/bulkcopy database option is turned on by default in tempdb to enable this behavior.
select into operations are faster because they are only minimally logged. Only the allocation of data pages is tracked, not the actual changes for each data row. Each data insert in an insert…select query is fully logged, resulting in more overhead.
Transaction logging performed by ASE Server cannot be turned off, to ensure the recoverability of all transactions performed on ASE Server. Any ASE statement or set of statements that modifies data is a transaction and is logged. You can, however, limit the amount of logging performed for some specific operations, such as bulk copying data into a database using bulk copy (bcp) in the fast mode, performing a select/into query, or truncating the log.
These minimally logged operations cause the transaction log to get out of sync with the data in a database, which makes the transaction log useless for media recovery.
Once a non-logged operation has been performed, the transaction log cannot be dumped to a device, but it can still be truncated. You must do a dump database to create a new point of synchronization between the database and the transaction log to allow the log to be dumped to device.
REORG REBUILD is a minimally logged operation. That means the physical object changes are logged (OAM; AllocUnits; AllocPages) but not the data changes. The command is already very slow, if data changes were logged, it would be even slower. The log space requirement is very small, you definitely do not need more log space.
The following DML operations are either nonlogged or minimally logged:
- TRUNCATE TABLE
- SELECT INTO
The TRUNCATE TABLE statement cannot be used on tables that are referenced by a FOREIGN KEY constraint, unless the FOREIGN KEY constraint is self-referencing. The TRUNCATE TABLE statement cannot be used on a table that is a part of an indexed view. You cannot use the TRUNCATE TABLE statement on tables that are published by transactional or merge replication. The TRUNCATE TABLE statement will not activate triggers, as triggers rely on transaction logs
The TRUNCATE TABLE statement uses much less transaction log space. The DELETE statement logs information about every row that is affected in the transaction log. When deleting from a table with millions of rows, this is both time- and disk-space-consuming.
The DELETE statement holds a lock on each individual row it is deleting. The TRUNCATE TABLE statement locks only the table and each data page. This offers better performance, as locking is one of the most time-consuming SQL Server activities.
The DELETE statement will leave empty data pages for the table and its indexes. If you wish to shrink the database by deleting data in a large table, the DELETE statement may prove counterproductive. The TRUNCATE TABLE statement, on the other hand, is guaranteed to.