如何在 SQL Server 中设置简单的计算字段？

发布于 2024-08-02 00:53:57 字数 349 浏览 5 评论 0原文

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

MAIN_ACCT
GROUP_ACCT
SUB_ACCT

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

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

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

SELECT ACCT_NUMBER FROM ACCOUNT_TABLE

执行此操作的最佳方法是什么？

我正在使用 SQL Server 2005。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尐偏执 2024-08-09 00:53:57

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

这将保留计算列，并且如果您有大量记录，则在选择中可能会比视图或 UDF 中的计算表现更好（一旦初始创建列，这可能会非常慢，并且可能会在低使用时间发生））。它会减慢插入和更新速度。通常我发现用户对缓慢的插入或更新的容忍度比选择中的延迟更好，除非遇到锁定问题。

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

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.

回复收藏 0 原文

葮薆情 2024-08-09 00:53:57

这是视图的一个很好的候选者。

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

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

回复收藏 0 原文

单身狗的梦 2024-08-09 00:53:57

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

该列不会保留在表中，每次引用它时都会立即重新创建。您可以使用视图获得相同的结果。如果您在计算列上使用过滤谓词或排序并希望在其上添加索引，请参阅在计算列上创建索引。

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.

回复收藏 0 原文