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 ?

Answer 1

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.