Hi folks, I'm going to do an indexed view, based on three tables with inner and outer joins between them (SQL Server 2005). I will run all kind of queries against this view. So, I wonder what is the best way to choose which index to be clustered. What are the criteria or is there any tools to help me around. (Sorry if my question is d...
Hi, Say I have a very long table (~35 million rows) called TimeCard with only 5 columns (tableID, CompanyID, UserID, ProjectID, DailyHoursWorked, entryDate). This is a pretty straight forward table that records employees' worked hours per day per project per company. I now need to generate a report to find out the employees' total wo...
Does MySQL have an equalavent to SQL Servers "indexed view" functionality? http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query What I'm specifically looking for is a way for MySQL to create a "view" that will return results faster than simply performing the underline view's query/sql. ...
I have a schema that includes tables like the following (pseudo schema): TABLE ItemCollection { ItemCollectionId ...etc... } TABLE Item { ItemId, ItemCollectionId, ContributorId } I need to aggregate the number of distinct contributors per ItemCollectionId. This is possible with a query like: SELECT ItemCollectionI...
OK, I'm confused about sql server indexed views(using 2008) I've got an indexed view called AssignmentDetail when I look at the execution plan for select * from AssignmentDetail it shows the execution plan of all the underlying indexes of all the other tables that the indexed view is supposed to abstract away. I would think that t...
I know Oracle offers several refreshmode options for their materialized views (on demand, on commit, periodically). Does Microsoft SQLServer offer the same functions for their indexed views? If not, how can I else use indexed views on SQLServer if my purpose is to export data on a daily+ on-demand basis, and want to avoid performance ov...
Does anyone have experience of when SQL Servr 2008 R2 is able to automatically match indexed view (also known as materialized views) that contain joins to a query? for example the view select dbo.Orders.Date, dbo.OrderDetails.ProductID from dbo.OrderDetails join dbo.Orders on dbo.OrderDetails.OrderID = dbo.Orders.ID cannot be automat...
My Requirement: I have this straight forward requirement of listing names of people in alphabetical order in a Indexed table view with index titles being the starting letter of alphabets (additionally a search icon at the top and # to display misc values which start with a number and other special characters). What I have done so far: 1...
Here's my configuration: I have a re-runnable batch script that I use to update my database. Inside of that batch script, I have code that says the following: If Table 'A' doesn't exist, then create Table 'A' and insert rows into it. Later on in that batch script, I create an schemabound indexed view on that table. And if you didn't...
I have an indexed view that I need to specify the noexpand hint for in order for it to perform reasonably. Unfortunately as seen with regard to modifying the Linq to SQL generated T-SQL query from the NOLOCK hint it appears that there is no easy way to take advantage of these hints directly or is there? My thought is that it would make ...
Hi folks, Is it possible to create an indexed view which returns the following results :- ID | Location Name | Aliases for that Location 1 | Some Location | Alias 1, Alias 2, Alias 3 2 | Another Location | NULL 3 | Yet Another Location | NULL 4 | Last location | An Alias My table structure is Location Table LocationId INTEGER Nam...
I want to use the Full Text Search feature of Microsoft SQL Server. If I have a table Client which refers other tables like City, Country, Department, etc. is it better to create a separate table that would hold de-normalized data, which would then be full text indexed, or is it better to create a dummy value in every foreign table (for...
Hi there, I have a table of address data in my SQL server database. This table is not normalized so it contain many addresses the are repeated. Each unique address can be identified by an Id field (these ids repeat often in the table). So i created a view on the table to extract all the unique addresses, using Select Distinct(AddressId...
I'm interested in the most elegant way of creating a solution using all T-SQL that will allow me to target any view that is currently in the database using the WITH SCHEMABINDING option and dropping it then recreating it. We need to assume here that we don't have access to the original DDL scripts so the Views must be recreated only from...
Hi folks, If i create an Indexed View (in Sql Server 2008), does this mean i copy all required the data from the source tables into a separate new table? Or are only some tiny pointers/indexes saved, to represent this view? ...
I know in SQL Server you can create indexes on a view, then the view saves the data from the underlying table. Then you can query the view. But, why I need to use view instead of table? ...