如何在 SQL Server 中设置简单的计算字段? [英] How can I set up a simple calculated field in SQL Server?

问题描述

我有一个包含多个帐户字段的表格，如下所示:

I have a table with several account fields like this:

MAIN_ACCT
GROUP_ACCT
SUB_ACCT

我经常需要像这样组合它们:

I often need to combine them like this:

SELECT MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT
FROM ACCOUNT_TABLE

我想要一个自动执行此操作的计算字段，所以我只能说:

I'd like a calculated field that automatically does this, so I can just say:

SELECT ACCT_NUMBER FROM ACCOUNT_TABLE

最好的方法是什么?

我使用的是 SQL Server 2005.

I'm using SQL Server 2005.

推荐答案

ALTER TABLE ACCOUNT_TABLE 
ADD ACCT_NUMBER AS MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT PERSISTED

如果您有大量记录(一旦列的初始创建已经发生，这可能会非常缓慢，并且可能应该在使用次数少).它会减慢插入和更新的速度.通常我发现用户更容易容忍缓慢的插入或更新，而不是选择延迟，除非你遇到锁定问题.

This will persist a calculated column and may perform better in selects than a calculation in view or UDF if you have a large number of records (once the intial creation of the column has happened which can be painfully slow and should probably happen during low usage times). It will slow down inserts and updates. Usually I find a slow insert or update is tolerated better by users than a delay in a select unless you get locking issues.

执行此操作的最佳方法在很大程度上取决于您的使用情况和您需要的性能类型.如果您没有很多记录，或者如果计算列不会被频繁调用，您可能不需要持久化列，但如果您经常运行包含当年所有记录或其他大量数据集的报告数据，您可能会发现持久计算列更适合您.与任何此类任务一样，了解最适合您的情况的唯一方法是进行测试.

The best method for doing this will depend a great deal on your usage and what kind of performance you need. If you don't have a lot of records or if the computed column won't be called that frequently, you may not want a persisted column, but if you are frequently running reports with all the records for the year or other large sets of data, you may find the persisted calculated column works better for you. As with any task of this nature, the only way to know what works best in your situation is to test.

这篇关于如何在 SQL Server 中设置简单的计算字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！