Exclude table from transaction log

[#1]

Is there any way to exclude a specific table from the transaction log?

The table in question is frequently being deleted/inserted with a massive amount of data. Every time this occurs,the log grows by about 5GB.

Thanks for your inputs!

[former_SQLA_member1694901]

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...

[timcheshire]

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.
I use these a lot to manipulate data without hitting the transaction log. You can create indexes on them too which improves performance on joins.

[justin_willey]

Yes, me too. Very useful with large temporary data sets where recovery is unimportant.

[VolkerBarth]

...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.

[timcheshire]

Exactly what we do.