View with NOEXPAND stop expandation

I have a large datab开发者_高级运维ase and for one of the table I want to create a noexpand view, that will filter some of the records from the table - from 10 000 000 records must show only 100 000 but the execution of the creation for the view last more than 10 minutes and the view is not created.

How could I make the view to be populated in background of SQL or make the script execute on more than 10 minutes?

The SQL Server is located on other machine, and I have only admin access for SQL not on machine to modify machine.config.

---

If I understood you correctly, you don't have enough access rights to go onto that remote machine with SQL Server and set up the views/indexes you need? But you do have enough rights to execute the create view dynamically from your code?

If you would want to filter out those 100k rows from 10M of them, I'd suggest you create an indexed view, containing precisely selected columns, only the ones that you need, and filter out all the rows that you might not need.

For example, you want to work only with dates in this year and you have 10 years of data in the database.

You would create a view, but with WITH SCHEMABINDING option, which would allow you to add a clustered index on that view.

After you have that, you will be free to call your new view (with possibly the WITH (NOEXPAND) option in SELECT) and that will give you the search through only the 100k rows that you asked for.

Now, since you can't access the SQL Manager at that server directly, just write your create view and create index SQL and execute it from inside your code. You would do that once and it would stick at your server permanently.

Hopefully I've got your setup well, otherwise I may be totally missing the point. :) But still, if you'd want to filter out unwanted data, the indexed view would be the way to go.