I have some computed columns in a table and need to know if I should set Is Persisted to true. What are the advantages? Are there any disadvantages? What does 'Is Persisted' mean?
我在表中有一些计算列,需要知道我是否应该将Is Persisted设置为true。有什么好处?有什么缺点吗? “坚持”意味着什么?
2 个解决方案
#1
"Persisted" means "stored physically" in this context.
“持久”意味着在这种情况下“物理存储”。
It means that the computed value is computed once on insert (and on updates) and stored on disc, so it does not have to be computed again on every select.
这意味着计算值在插入(和更新)上计算一次并存储在光盘上,因此不必在每次选择时再次计算。
Persisted also causes a performance penalty on insert and updates, since the column must be computed, but will increase the performance on subsequent select queries.
持久化还会导致插入和更新时的性能损失,因为必须计算列,但会提高后续选择查询的性能。
So, it depends on your usage pattern, which approach to follow: if you update infrequently, but query a lot, you should set persisted = true.
因此,它取决于您的使用模式,遵循哪种方法:如果您不经常更新,但经常查询,则应设置persisted = true。
If you update frequently, or if you do not care about retrieval performance, you should consider setting persisted = false
如果您经常更新,或者您不关心检索性能,则应考虑设置persisted = false
#2
One more thing not mentioned in other answers: a computed column must be PERSISTED to be usable from FOREIGN KEYs.
在其他答案中没有提到的另一件事:计算列必须是PERSISTED才能从FOREIGN KEYs中使用。
#1
"Persisted" means "stored physically" in this context.
“持久”意味着在这种情况下“物理存储”。
It means that the computed value is computed once on insert (and on updates) and stored on disc, so it does not have to be computed again on every select.
这意味着计算值在插入(和更新)上计算一次并存储在光盘上,因此不必在每次选择时再次计算。
Persisted also causes a performance penalty on insert and updates, since the column must be computed, but will increase the performance on subsequent select queries.
持久化还会导致插入和更新时的性能损失,因为必须计算列,但会提高后续选择查询的性能。
So, it depends on your usage pattern, which approach to follow: if you update infrequently, but query a lot, you should set persisted = true.
因此,它取决于您的使用模式,遵循哪种方法:如果您不经常更新,但经常查询,则应设置persisted = true。
If you update frequently, or if you do not care about retrieval performance, you should consider setting persisted = false
如果您经常更新,或者您不关心检索性能,则应考虑设置persisted = false
#2
One more thing not mentioned in other answers: a computed column must be PERSISTED to be usable from FOREIGN KEYs.
在其他答案中没有提到的另一件事:计算列必须是PERSISTED才能从FOREIGN KEYs中使用。