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

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

Creating indexed view from the table which has no index?

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server


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,


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 a clustered index on a partioned table - need help understanding effect of filegroup placem


We have a large table ~40 Million rows and about 35 columns.  Things are starting to slow down and i am looking at partioning.  The table has some historical data and most of it can be ignored except for the current year, so I wanted to partion the data on the year column.  So I created a filegroup and ndf/ldf files for each year (ie: 2005,2006,2007, etc...)

The primary key is clustered on an identity column in this table.  When I went to recreate the table I got a warning message that the clustered index would be created on the primary file group as I specified, so i decided to do some research and found this article:


This leads me to believe that creating a clustered idex on the primary file group will prevent true partionting.  Is this true?  If so where should I store my clustered index as this seems to defeat the purpose of partioning then.

Also I was reading something that the clustered index should contain the key column that the data is partioned on, so it should be something like (PriKeyCol, theYear) ? as opposed to just the (PriKeyCol)

Thanks for any help,

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

Cutting Edge: Creating a Multi-table DataGrid in ASP.NET


If you bind a multi-table DataSet to a DataGrid, only the first table is recognized. Here Dino Esposito writes a custom solution the the multi-table problem.

Dino Esposito

MSDN Magazine August 2003

Change grid view from table to other HTML layout



You'll have to excuse my ignorance, I'm coming at this from a front-end perspective, and don't really know how to deal with data views in ASP.NET.

Basically, I'm trying to change the layout of some repeated data from a straight table, to something more design-heavy.  It's the same data, just re-organised slightly.  The current ASP.NET code looks like this:

                    <asp:GridView ID="GVMoments" runat="server" AllowPaging="True" AllowSorting="True"
                        AutoGenerateColumns="False" PageSize="20" RowHeaderColumn="FirstName"
                        SkinID="GroupSkin" DataKeyNames="moment_id" 
                        <PagerSettings Mode="NumericFirstLast" PageButtonCount="20" Position="TopAndBottom" />
                        <RowStyle />
                                <asp:BoundField DataField="moment_id" HeaderText="moment_id" ReadOnly="True" SortExpression="moment_id"
                                    Visible="False" In

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

cannot create an index on a view

Hi All, Can I create an index on the following view? Create view myview with schemabidning as select id, date, value from tablea a right join (select id,min (date) as mindate, value from tablea ) b on a.id = b.id and a.date = b.mindate GO CREATE UNIQUE CLUSTERED INDEX IDX_1 ON MYVIEW(ID)     Right now i am getting an error.   Cannot create index on view "myview" because it references derived table "B" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view. shamen

SP2010- Creating an Index server which will host all the Service applications

Guys, Current Environment : 1 Sql server 2008 R2 and 1 SharePoint 2010 Server OS : windows server 2008 all 64 bit (goes without saynig) For better performance i want to seperate out Service application ( expecially Search Service application) to another windows server while keeping the existing one as a WFE hosting CA as well. From the little documentation i found i am creating an Index server(with out CA on it) 2 questions here : 1. Is this the best way to optimize my farm? 2. Is there any online documentation or any knd of link that woudl describe how to set up the index server. thanks in advance. Sameer  

Help creating HTML table from dataTable when every nth dataTable row = 1 html table row... C#

Hello, Im trying to create an html table from data that is in a dataTable but Im having trouble coming up with the right logic. Every 3 rows in the dataTable needs to be 1 row in the html table.  Ive tried using foreach through the datatable with a modulus to determin every third dataTable row but i still run into snags because i am building the table using objects rather than using concatenated strings..(example TableRow tr = new TableRow  etc...). I cant add the cells to the rows when the row is only created in an if statement etc. Can someone post a good example in c# of how they would achieve this. The amount of data in the dataTable could change from time to time so the code cant rely on a pre determined size, unless you just need the total rows and columns, that can be dtermined when the dataTable is filled from the sqlDataReader. <table><tr><td>dataTable row 1 values</td><td>ataTable row 2 values</td><td>dataTable row 3 values</td></tr> <tr><td>dataTable row 4 values</td><td>dataTable row 5 values</td><td>dataTable row 6 values</td></tr>   Any help and example would be great. Ive been able to do similar tables in other apps but thats only when I use concatenated text strings to build the html table. Im trying to stay away from that this time

Model not found when creating a View

Trying to create a strongly typed view but the model "that is a class" is not listed in the "View data class" dropdown?   any thoughts what to do? 

customize list view table header css file

i need to make table header for my list to be red and on hover to get specific  image so all my site have the same appearence also i need this to reflect to the list view webpart thanks

how can i force to use index on table

I have go through execution plan,it shows me a table scan(10%) on a table who has the index column.then also it show table scan so how can i force it to use the indexe column from the table???   Thank's Digambar
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