我应该设计一个SQL Server数据库来依靠UNION还是避免呢？ [英] Should I Design a SQL Server database to rely on UNION or avoid it?

问题描述

采取以下SQL查询：

SELECT     Account, Amount AS Deposit, 0.00 AS Withdrawal, Date
FROM       Deposits
WHERE      Account = @Account
UNION
SELECT     Account, 0 AS Expr1, Amount, Date
FROM       Withdrawals
WHERE      Account = @Account
ORDER BY   Date Desc

相反：

SELECT    Account, TransactionType, Amount, Date
FROM      Transactions
WHERE     Account = @Account
ORDER BY  Date Desc

在第一个查询中存储和取款存储在不同的表格中，并且每当需要一起查看时，例如在显示银行对帐单时，联合在一起。第二个查询具有存储在一个表中的所有事务，存入和取消，并且事务类型通过TransactionType列进行区分。产生的查询结果不完全相同，但假设客户端对于最终输出而感到满意。

In the first query has deposits and withdrawals stored in different tables and whenever they need to be seen together, such as when showing a bank statement, they're UNIONed together. The second query has all transactions, deposits and withdrawals, stored in one table and the transaction type is differentiated via the TransactionType column. The query results prodcued are not exactly the same but let's say the client is happy with either as the final output.

是否有任何性能原因，为什么要使用第一个模式比第二个模式更好还是更差？

Is there any performance reason why setting up the database using the first schema is better or worse than the second schema?

编辑：为了清楚起见，我想指出，我提出这个问题的原因要确定是否有一个性能有利于保持较小的表格，并在必要时使用一个UNION表，我将使用WHERE子句来查找不同类型的数据。我用上面的例子来更好地表达我的问题。欢迎有关其他原因更好的建议，但请尽量回答此问题。

Just for the sake of clarity, I'd like to point out that the reason I was asking this question is to find out whether there's a performance benefit in keeping tables smaller and using a UNION when necessary over having one large table where I would use WHERE clause to find different types of data. I used the above example to better express my question. The advice on which schema is better for other reasons is welcomed, but please try to also answer the question as well.

推荐答案

老实说，我不认为表现应该是你的主要关注 - 在你给出的例子中，表现不可能有显着差异。

To be honest, I don't think performance should be your primary concern - and in the example you give, performance is unlikely to be measurably different.

然而，想象你想要计算任何时间点的账户余额。如果你有一个交易表，你可以在单个查询中实现这一点 -

However, imagine you want to calculate the balance of the account at any point in time. If you have a "transactions" table, you can achieve this in a single query -

select sum(amount) from transactions

如果你分成两个表，你需要执行两个查询;广泛来说，我希望这可以查询单个表格的时间只需要两倍，即使该表格与其他表格相同的记录数量相同。

If you split into two tables, you need to execute two queries; broadly speaking, I would expect this to take twice as long as querying a single table, even if that table has the same number of records as the two other tables put together.

我认为你应该把重点放在代表业务领域的最好的地方 - 如果你看一个老式的分类帐，我想你会发现信用和借记都输入到同一列。您的业​​务利益相关者更有可能以交易 - 积极或消极的方式思考，而不是对存款和提款有单独的概念。

I think you should concentrate on what represents the business domain best - if you look at an old fashioned ledger, I think you'll find both credits and debits are entered in the same column. Your business stakeholders are more likely to think in terms of "transaction" - positive or negative - than in terms of having separate concepts for deposits and withdrawals.

这篇关于我应该设计一个SQL Server数据库来依靠UNION还是避免呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！