No. If you are always deleting all rows from the table, you could consider the use of the TRUNCATE TABLE command instead of DELETE, and you could use the LOAD TABLE command instead of INSERT to add data into the table. TRUNCATE TABLE and LOAD TABLE (by default) do not log individual rows to the transaction log, as can be seen below. Reg BEGIN TRANSACTION go --SQL-1005-0000009723 truncate table "dba"."admin1" go --COMMIT-1005-0000009764-2022-12-19 14:57:10.960751 COMMIT WORK go --BEGIN TRANSACTION-1005-0000009843 BEGIN TRANSACTION go --BEGIN LOAD TABLE-1005-0000009846: load into table "dba"."admin1" using file 'a1.dat' --SQL-1005-0000009900 begin set temporary option "date_order" = 'YMD'; set temporary option "nearest_century" = '50'; set temporary option "time_zone_adjustment" = '-300'; load into table "dba"."admin1" using file 'a1.dat' encoding 'windows-1252'; set temporary option "date_order" = ; set temporary option "nearest_century" = ; set temporary option "time_zone_adjustment" = ; end go --END LOAD TABLE-1005-0000010289 --COMMIT-1005-0000010292-2022-12-19 14:57:31.617576 COMMIT WORK go --CHECKPOINT-0000-0000010303-2022-12-19 14:57:41.781593 answered 19 Dec '22, 15:03 Reg Domaratzki Awesome, thank you very much!
(19 Dec '22, 15:56)
mrtd
1
Just to clarify: LOAD TABLE allows for logging of individual rows or file contents via its options, it just does only log the file name by default when using "FROM filename...".
(20 Dec '22, 01:51)
Volker Barth
1
If I remember correctly, TRUNCATE TABLE initiates a COMMIT which may interfere with other processes.
(20 Dec '22, 06:13)
TimCH
Replies hidden
Just another clarification: TRUNCATE TABLE does only omit individual DELETE statements in the log when doing a "fast truncate". If if cannot do a "fast truncate", it pretty much behaves like a DELETE statement without a WHERE clause AFAIK w.r.t. the log. (AFAIK, there are still other differences to a real DELETE regarding locking and the firing of triggers...) See the docs for the "gory details"...
(21 Dec '22, 03:30)
Volker Barth
|
Has anyone tried using a GLOBAL TEMPORARY TABLE with NOT TRANSACTIONAL clause for this? From the description in the docs I'd expect it to do something very similar... answered 20 Dec '22, 05:40 tedfroehlich 3
Yup. I use GLOBAL TEMPORARY TABLE with NOT TRANSACTIONAL a lot.
You can also use DELACRE LOCAL TEMPORARY TABLE in stored procedures with NOT TRANSACTIONAL.
(20 Dec '22, 17:19)
TimCH
2
Yes, me too. Very useful with large temporary data sets where recovery is unimportant.
(20 Dec '22, 19:08)
Justin Willey
2
...with the SHARE BY ALL clause, if it is menat to replace a base table, probably :) FWIW, for ETL needs, we do use LOAD TABLE into local temporary tables with NOT TRANSACTIONAL very often, then usually comparing the "freshly imported data" in these temporary tables with that of according permanent tables, then merging only the changes into the latter ones, thereby reducing both the amount of data modifications and of log growth.
(21 Dec '22, 03:08)
Volker Barth
Replies hidden
|