How to get a Query Result in a view rather than a execution of a Query?

Question

I want to create a View with a lenghty query like :

CREATE VIEW doubleV
AS 
SELECT * FROM X1 as R
WHERE [Ext] IN 
(
    SELECT [Ext] FROM X1 as RR
    WHERE RR.cliID=R.cliID 
    AND Soc='j'
    GROUP BY [Ext]  
    HAVING COUNT(*) > 1
)

But when i Select all elements from this VIEW, Sql server just execute the query inside the view, which takes much time.

Is it possible to execute the query at the VIEW CREATION ?

Does anything would allow me to make a temporary table with a query result ?

Is it possible to execute the query at the VIEW CREATION ? Ans: NO — knkarthick24, Jul 07 '15 at 08:19
@knkarthick24 If a view can't handle that, Does anything exists that would make it possible ? — Fortune, Jul 07 '15 at 08:20
@Kaf My query has to detect duplicate on the [Ext] column. Several queries have to be done after this one, that's why i need kind of temporary table at first to make some test queries. — Fortune, Jul 07 '15 at 08:22
Your Query is taking much time. Is that only reason behind the VIEW which you created? — knkarthick24, Jul 07 '15 at 08:23
I think your query can be optimized if you explain expected output. — Kaf, Jul 07 '15 at 08:24

score 0 · Answer 1 · edited May 23 '17 at 12:14

You can use

SELECT … INTO NewTableName
FROM …

to create a table with the results of a query (the table will lack any keys, and will be updatable).

You could also create an "Indexed View": which is a copy of the results of the view, generally intended to be updated from time to time (eg. an expensive query is run every few minutes to balance freshness of the results with cost of getting them). This is often called a "Materialised View".

Pholochtairze · Accepted Answer · 2015-07-07T08:36:17.483

See here :

The result set of a standard view is not stored permanently in the database. Each time a query references the view, Microsoft® SQL Server™ 2000 dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view.

So you need to create an INDEXED VIEW, in order to do this, you can read this.

The code you would need is the following (maybe some typos) :

CREATE VIEW doubleV
AS 
SELECT * FROM X1 as R
WHERE [Ext] IN 
(
    SELECT [Ext] FROM X1 as RR
    WHERE RR.cliID=R.cliID 
    AND Soc='j'
    GROUP BY [Ext]  
    HAVING COUNT(*) > 1
)
GO
CREATE UNIQUE CLUSTERED INDEX doubleVInd ON doubleV (field_you_want)

Where field_you_want is the field you index on, most probably an ID.

How to get a Query Result in a view rather than a execution of a Query?

2 Answers2