Is it possible to create a temporary table in a View and drop it after select?
2022/6/21 23:21:48
本文主要是介绍Is it possible to create a temporary table in a View and drop it after select?,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Is it possible to create a temporary table in a View and drop it after select?
问题
I need to alter one view and I want to introduce 2 temporary table before the SELECT.
Is this possible? And how can I do it?
ALTER VIEW myView AS SELECT * INTO #temporary1 SELECT * INTO #temporary2 SELECT * FROM #temporary1 UNION ALL SELECT * FROM #temporary1 DROP TABLE #temporary1 DROP TABLE #temporary2
When I attempt this it complains that ALTER VIEW must be the only statement in the batch.
How can I achieve this?
这玩意,应该直接在存储过程写
回答1
No, a view consists of a single SELECT
statement. You cannot create or drop tables in a view.
Maybe a common table expression (CTE) can solve your problem. CTEs are temporary result sets that are defined within the execution scope of a single statement and they can be used in views.
Example (taken from here) - you can think of the SalesBySalesPerson
CTE as a temporary table:
CREATE VIEW vSalesStaffQuickStats AS WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate) AS ( SELECT SalesPersonID, COUNT(*), MAX(OrderDate) FROM Sales.SalesOrderHeader GROUP BY SalesPersonID ) SELECT E.EmployeeID, EmployeeOrders = OS.NumberOfOrders, EmployeeLastOrderDate = OS.MostRecentOrderDate, E.ManagerID, ManagerOrders = OM.NumberOfOrders, ManagerLastOrderDate = OM.MostRecentOrderDate FROM HumanResources.Employee AS E INNER JOIN SalesBySalesPerson AS OS ON E.EmployeeID = OS.SalesPersonID LEFT JOIN SalesBySalesPerson AS OM ON E.ManagerID = OM.SalesPersonID GO
Performance considerations
Which are more performant, CTE or temporary tables?
Adding an INDEX to a CTE
回答1
I have had the same requirement. Indexes can not be added to a CTE. However, in the CTE select adding an ORDER BY clause on the joined fields reduced the execution time from 20 minutes or more to under 10 seconds.
(You need to also ADD SELECT TOP 100 PERCENT to allow an ORDER BY in a CTE select.)
[edit to add paraphrased quote from a comment below]:
If you have DISTINCT in the CTE then TOP 100 PERCENT doesn't work. This cheater method is always available: without needing TOP at all in the select, alter the ORDER BY statement to read:
ORDER BY [Blah] OFFSET 0 ROWS
回答2
No.
A CTE is a temporary, "inline" view - you cannot add an index to such a construct.
If you need an index, create a regular view with the SELECT of your CTE, and make it an indexed view (by adding a clustered index to the view). You'll need to obey a set of rules outlined here: Creating an Indexed View.
这篇关于Is it possible to create a temporary table in a View and drop it after select?的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升