patternsqlMinor
Is there a way to disable logging / backups for view indexes?
Viewed 0 times
loggingbackupsdisablewayviewindexesforthere
Problem
We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but in our case it is very worth it).
My understanding is that changes to view indexes must be logged / backed up just like changes to any other table/index. However, it seems to me that such logging is redundant since SQL could easily rebuild the view index from the view definition in the event of a crash.
Is there any way to achieve something like this with SQL Server?
My understanding is that changes to view indexes must be logged / backed up just like changes to any other table/index. However, it seems to me that such logging is redundant since SQL could easily rebuild the view index from the view definition in the event of a crash.
Is there any way to achieve something like this with SQL Server?
Solution
nope
There has been a long-standing request for SQL Server to support unlogged objects. It is available in varying forms in other database systems, both for tables and for materialized views.
However, as of this writing, you can't choose to mark an object of any variety as unlogged in SQL Server. While it may be logically redundant, it's unclear from your question if it's causing a problem or if you just don't like it and would prefer different behavior.
If you're experiencing performance issues with indexed view maintenance, you may want to ask a more direct question about improving that rather than looking for a change in current product behavior.
For your concerns around recovery (whether from crashes or reboots), you could take advantage of Accelerated Database Recovery in SQL Server 2019 and up.
There has been a long-standing request for SQL Server to support unlogged objects. It is available in varying forms in other database systems, both for tables and for materialized views.
However, as of this writing, you can't choose to mark an object of any variety as unlogged in SQL Server. While it may be logically redundant, it's unclear from your question if it's causing a problem or if you just don't like it and would prefer different behavior.
If you're experiencing performance issues with indexed view maintenance, you may want to ask a more direct question about improving that rather than looking for a change in current product behavior.
For your concerns around recovery (whether from crashes or reboots), you could take advantage of Accelerated Database Recovery in SQL Server 2019 and up.
Context
StackExchange Database Administrators Q#325559, answer score: 7
Revisions (0)
No revisions yet.