如何在 SQL Server 中设置简单的计算字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1274582/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I set up a simple calculated field in SQL Server?
提问by JosephStyons
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
What is the best way to do this?
做这个的最好方式是什么？
I'm using SQL Server 2005.
我正在使用 SQL Server 2005。
回答by HLGEM
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.
如果您有大量记录，这将保留一个计算列，并且在选择中的性能可能比视图或 UDF 中的计算更好（一旦列的初始创建发生，这可能会非常缓慢，并且可能应该在低使用时间发生）。它会减慢插入和更新的速度。通常我发现缓慢的插入或更新比延迟选择更能被用户容忍，除非你遇到锁定问题。
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.
执行此操作的最佳方法将在很大程度上取决于您的使用情况以及您需要什么样的性能。如果您没有很多记录，或者如果计算列不会被频繁调用，您可能不需要持久化列，但如果您经常运行包含当年所有记录或其他大量数据集的报告数据，您可能会发现持久计算列更适合您。与任何此类任务一样，了解最适合您的情况的唯一方法是进行测试。
回答by p.campbell
This is a great candidate for a View.
这是一个很好的视图候选者。
CREATE VIEW vwACCOUNT_TABLE
AS
SELECT MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT AS ACCT_NUMBER
FROM ACCOUNT_TABLE
GO
--now select from the View
SELECT ACCT_NUMBER FROM vwACCOUNT_TABLE
回答by Remus Rusanu
ALTER TABLE ACCOUNT_TABLE ADD ACCT_NUMBER AS MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT;
the column is not persisted in the table, will be recreated on-the-cly each time you reference it. You can achieve the same result by using a view. If you use filtering predicates or ordering on the computed column and want to add an index on it see Creating Indexes on Computed Columns.
该列不会保留在表中，每次引用它时都会立即重新创建。您可以通过使用视图获得相同的结果。如果您在计算列上使用过滤谓词或排序并希望在其上添加索引，请参阅在计算列上创建索引。
回答by DavidStein
Well, you could create a view of the ACCOUNT_TABLE and query that. Or I believe you could create a user defined function which would accomplish the same thing.
好吧，您可以创建 ACCOUNT_TABLE 的视图并进行查询。或者我相信你可以创建一个用户定义的函数来完成同样的事情。
