.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Indexed View

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hellol Guys:

There is a table with 100 million rows in my DB. Thus, I create an indexed view as below

Create view dbo.try

with schemabinding


select a.id, a.name, a.desc from dbo.a 

where a.codeType='G' and planner in (4567,899)

Create unique clustered index IDX on dbo.try(id)  

 Then When I run the SQL statement as below

select * from try

when I look at the execution plan, it seems to still fetch the data from the indexes created in the underlting table <a>, Instead , as I thought it should fetch data from the materialize

View Complete Post

More Related Resource Links

SQL Server Table OR Indexed View

Hi     I am having a problem between chosing VIEW and TABLE from a SQL server database   PROBLEM Suppose the data table is name ACTION (of size 30M) with  schema ACTIONID/ ACTIONNAME/USERID/ ACTIONDATE. The Table is updated once everyday with 20K new rows For my purpose I only want a query on ACTION rows which are as recent as 1 week I want queries to run faster selecting from a smaller dataset(as there are joins as well)        SOLUTION A(Indexed) Instead of querying the 30M database everytime; I create a view that will store data from current date to 7 days earlier I set the view to refresh ONLY once a day and NOT everytime there is an insert update delete on the ACTION table That way my queries are faster (as from a smaller dataset) BUT my view doesnt refresh 20k times a day making the performance a disastrous one       Question : Can a VIEW be actually configured to REFRESH only periodically and NOT when table updates?         SOLUTION B(Indexed View) I create a View which stores all the ACTION table rows between 9/16/2010 and 9/9/2010 (today and a week, hardcoded) When new 20K entries are inserted; the view isnt refreshed even once as all the dates ate outside range(despite the ACTION table is updated) A trigger is attached to view to run a script everyday The trig

Error Creating Indexed View with APPLY


Hi All,

I got a requirement to create a view with current months qty, value and same month last year qty, value

Im facing a issue when im trying to create a Index on a view which created using table-valued function. following is a script I used to create the view followed by script to create index on that view.



	SELECT dbo.Table2.PeriodYear,
		  SUM(dbo.Table1.Qty) CurrentMonthQty,
		  SUM(dbo.Table1.Val) CurrentMonthVal
	FROM dbo.Table1, dbo.Table2, dbo.Table3
	WHERE dbo.Table1.DateCol = dbo.Table2.DateCol
	AND dbo.Table1.ProductCol = dbo.Table3.ProductCol

Creating indexed view from the table which has no index?



Can we create indexed view from the base table which has no index?

This is because i don't want any indexes in my base table but for better query performance i want in view..

Please suggest..

Thanks in advance,


Can I prevent all queries from using an indexed view?

Is there a global setting to prevent all queries from executing against an indexed view (unless specified by name)?

Replicate non indexed view to a table - custom sync object/


What is the best way to replicate data from a view containing a LEFT OUTER JOIN (so cannot be indexed) to a physical fact table on the subscriber in as real time a fashion as possible?

I have heard of the use of custom sync objects etc.. can anyone suggest the best way forward and any references?

Is CDC a candidate for this type of task?

Thanks in advance

Truly Understanding View State

This article provides a detailed understanding of the ViewState.

GridView Parent Child View with JQuery Effects

I was just checked how to add some more cool effects to the GridView control using JQuery. In this post I will display parent-child data in the GridView control with some help of the wonderful JQuery library.

Let's first populate the GridView control.

Model View Controller (MVC) for ASP.NET

Model View Controller (MVC) architectures, immediately wonder how the MVC pattern can be applied in an ASP.NET world. This article will demonstrate how the MVC pattern can be applied to an ASP.NET web application and still maintain the basic Microsoft supplied architecture.

jQuery grid view plugin

jqGridView is new, client-rich, XML-based, ajax grid plugin for jQuery library. jqGridView provides professional solution for representing and editing tabular data on the web. Carefully designed, with powerful script API, this editable DHTML grid is easy configurable with XML, and shows convincing results working with large amounts of data. jqGridView allows easy implementation of nice looking(managed through css). jqGridView is not platform-depending plug-in, it can be used with different web -programming platforms like: ASP .NET/ASP, PHP, JAVA, CGI scripts etc. jqGridView has hight bowser compatibility.

jQuery grid view plugin

jqGridView is new, client-rich, XML-based, ajax grid plugin for jQuery library. jqGridView provides professional solution for representing and editing tabular data on the web. Carefully designed, with powerful script API, this editable DHTML grid is easy configurable with XML, and shows convincing results working with large amounts of data. jqGridView allows easy implementation of nice looking(managed through css

view state vs request parameters


If  the view state is the data entered into the form fields then they are supposed to be available in the request parameters or request body. Then why would we need view state for?


Form view in ASP.NET


hi all...!
i have a database and i create a form with button edit,delete, New Registration..using Formview in asp.net..
everything was working good...

And i write SQL statement to get New ID number into dropdownlist/TextBox when i click button "New Registration".

i can get new(next) ID number after read database
it's also working good

Now i want change it to get ID which was deleted from table and replace the ID with new record...what i need is when i click "New Registration" i want get deleted "ID" first and then go to new record ID...

my code is here:-it working for get next ID after read database.

FROM Table1
AS boon WHERE PANG &gt;300000

partial view, 2 controllers



This is my first visit to the forums, I hope someone here can help me


I'm writing a small webapplication for creating repair tickets and adding comments, really basic

now I'm writing this in MVC2

So I have 2 controllers, 1 HomeController that does everything related to the tickets (listing tickets, creating new ones, editing) and 1 controller for the comments

the edit view for the homecontroller contains a partial view for /comment/create

so /home/edit/ticketnumber can edit the title and status of the ticket on the left side of the screen, the right side of the screen contains the partial view for adding comments

this is done like this:

Html.Renderaction("Create", "Comment");

but if I look at the generated html code when running the app, both save buttons go to /home/edit/ticketnumber

so the comment is never saved.


I hope this makes sense and that someone can help me solve this

WebDev.WebServer.exe has encounted a problem and needs to close when trying to view page in browser


Hi, whenever I load an aspx page in vwde2008, I get the message in the subject of this post.  After I click Ok on that, I get a dialog box, Unable to Connect to the ASP.Net development center.  What can I do to troubleshoot?

I am using Windows XP SP3, vwde2008 with SP1, on a HP desktop.  Also using Office 2003 if that is relevant at all.

Thanks, Justin

View State Encryption


Hi All,

Why is it that I see the same hash value generated when I use different algorithms for viewstate encryption. I have added below lines to the web.config file

<pages viewstateEncryptionMode="Always"

Custom Control Design view error



I have created a custom control from scratch and it works fine as in you can build the project that uses it and it works fine at runtime. Problem is when you go to design view the control shows an error in the place of where the control should be rendered.

Error: '<SomeValue>' Could not be set to '<SomeProperty>'

This shows up on all my custom set properties. These properties are created as basic as possible. I can give the properties values in Source view and run the app just fine. I can even add a Onclick event. If I don't set any custom properties the control will render fine in Design view. It's only when I set a value to a custom property.

Property Code Example:

public string Text
                string ret = "";
                object obj = ViewState["Text"];
                if (obj != null)
                    ret = obj.ToString();
                return ret;
                ViewState["Text"] = value;

I've even removed the Category and Description tags with no difference.

I don't know if what I said makes sens, but I hope it

Crystal Report Asking for Database Authentication each time when I view Page.


Whenever I open my Crystal Report page, I am taken to the Database Authentication page where I am asked for

User name
Data Name

each time.

Is there a way to avoid this and I save these authentication in my page once.

I am using VS2005, C#.

Any Help will be appriciated.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend