Indexed Views Can Not Have Self-join !!!
Feb 10, 2006is there any work around this?
View 1 Replies
ADVERTISEMENT
Indexed Views
Jan 28, 2005Hello,
I'm currently performance tuning a table with 100 million rows in it
(about 18 GB of data) and would like to know if -
1. Is the table too large to be performance tuned. Is it better to just
redesign the schema ?
2. Can using techniques as indexed views really help me with tuning such a table.
3. How long would it take to create a clustered, non clustered index on
a varchar column (for instance) on a table with 100 million rows ?
(i know this is a function of hardware as well - let's assume i'm using
afairly maxed out DL 360 - i.e. dual processor with 4 GB of memory)
Thank you very much
Alan
Indexed Views
Mar 9, 2005I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.
Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.
Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).
Indexed Views
Apr 3, 2008Hi,
I am trying to create an indexed view, but because I am using a MAX function, I get the error
Cannot create index on view "dbo.View" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.
Am totally stuck on how I can replace the MAX function.
Any help would be appreciated.
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
CREATE VIEW [dbo].[View]
WITH SCHEMABINDING
AS
SELECT TOP 100 PERCENT MAX(js_id) AS job_event, job_id
FROM dbo.JobEvent
GROUP BY job_id
ORDER BY job_event
GO
CREATE UNIQUE CLUSTERED INDEX IX_VMaxJobEvent ON View (job_id)
Thanks
Regarding Indexed Views
Aug 6, 2007Dear experts,
I've been working for an ERP solutions, company, as a DBA....
we have around 1200 tables as wellas 650 views.....
we are not using clustered index on views.....
using the clustered index will boost the performance? and the ERP is web based application. so that modifications will be done on a regular basis....
is it good thing to implement clustered indexes on these views....
please guide me in this regard
thank you verymuch
Vinod
Even you learn 1%, Learn it with 100% confidence.
Indexed Views Tbl Requirements
Jan 17, 2003I had some issues yesterday with the fact that some of the tables I had indexed views for did not have a unique/clustered index. The tables had unique indexes and clustered indexes but not a unique/clustered index. What I was seeing were rows that should have been in the view, not showing up in a regular select but they would showup in a with noexpand hint.
To fix the problem I created a unique/clustered index on each of the underlying table but cannot find that requirement anywhere, is this a requirement and if so can someone tell me where to find it.
Thanks
Defrag Indexed Views?
Jul 7, 2004How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!
Indexed Views Error
Oct 17, 2001I have an indexed view with a clustered index on my database........when I try to run and update statement agaisnt the table that is referenced in the view, I get one of the following errors:
Server: Msg 3624, Level 20, State 1, Line 1
Location: q:SPHINXNTDBMSstorengdrsinclude
ecord.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 52
Process ID: 414
Connection Broken
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 52
Process ID: 414
Connection Broken
any ideas?
Indexed Views In Sql 2000
Mar 16, 2006Lalitha writes "Can I use DML statements against indexed views?
If yes how it works internally, means will the pages gets locked during update and inserts and when the base tables get reflected of these modified data?"
Indexed Views Without Subquery
Mar 19, 2008Hi, I'm having a problem creating an indexed view of a productprice table
CREATE TABLE [dbo].[ProductPrice](
[ProductPriceGUID] [uniqueidentifier] ROWGUIDCOL,
[ProductGUID] [uniqueidentifier],
[Price] [decimal](7, 2),
[IsSRP] [bit],
[EffectiveDate] [datetime]
)
Each ProductGUID can and should have multiple records in the ProductPrice table. I want to create an indexed view that will group by ProductGUID that will show either the most recent custom price if a (isSRP = 'False') record exists or the most recent srp price if there is only (isSRP = 'True'), then a column stating whether the price column is taken from a (isSRP = 'True') record, and followed by the most recent srp price (isSRP = 'True').
SELECT
[PP].ProductGUID AS [ProductGUID],
CASE
WHEN MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) = 0
THEN MAX(CASE [PP].IsSRP WHEN 'True' THEN 0 ELSE [PP].Price END)
ELSE MAX([PP].Price)
END AS [Price],
MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) AS [PriceIsSRP],
MAX(CASE [PP].IsSRP WHEN 'True' THEN [PP].Price ELSE 0 END) AS [SRP]
FROM dbo.ProductPrice AS [PP]
-- Eliminate all but the most recent custom price, and msrp price
WHERE ([PP].IsSRP = 'True' AND [PP].EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'True'))
OR ([PP].IsSRP = 'False' AND EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'False'))
GROUP BY [PP].ProductGUID
This query works, but I can't create an indexed view on any query with a subquery. So, If I had some way of limiting the query to return only the most recent IsSRP = 'True' and the most recent IsSRP = 'False' (if one exists) without using a subquery then this would be aces.
Oh, and every ProductGUID has at least one IsSRP = 'True' record.
Any ideas??
Use Of Union In Indexed Views
Feb 5, 2007hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.
View 1 Replies View RelatedIndexed Views And Context
Apr 30, 2007Hi guys ,
we had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.
Now the question is can I create indexed views?
If yes how can I implement .Because data depends on context is there any way to create global indexed views.
In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.
Looking For A Good Article About Indexed Views.
Apr 29, 2004Hello folks!
I'm looking for a good article about Indexed Views.
Cheers!
Rafael
SOS - Table Partitions Or Indexed Views.
Feb 26, 2008Hi Experts,
We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).
Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.
Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.
1. Will SQL server table paritioning help in this scenario ?
Or
2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?
Any help would be greatly appreciated.
Thanks in advance,
Hariarul
Updating Indexed Views - Who Pays?
Jul 23, 2005This from a SQL Server manual:"Complex queries, however, such as those in decision support systems,can reference large numbers of rows in base tables and aggregate largeamounts of information into relatively concise aggregates (such as sumsor averages). SQL Server 2000 supports creating a clustered index on aview that implements such a complexquery. When the CREATE INDEX statement is executed, the result set ofthe view SELECT is stored permanently in the database. Future SQLstatements that reference the view will have substantially betterresponse times. Modifications to the base data are automaticallyreflected in the view."My question arises from the last sentence. At what point are the viewsupdated with the new data? If I am running a transaction that updatessome dependent tables, is there a performance impact while the indexedviews are updated?Jess Askin.
View 3 Replies View RelatedVery Serious Bug With Materialized/Indexed Views And SQL Server
Jul 20, 2005Do not trust values returned by materialized views under SQL Serverwithout frequently checking underlying tables!!!I already posted this message under microsoft.public.sqlserver.serverand I'm amazed nobody from Microsoft answered about this problem. Byinserting lots of data into our two main tables for about 30 minutes,we can fail our materialized view that performs a count_big on thosetwo tables.Executing (after of course having stopped inserting rows in our twotables)[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView option(expand views)[/color]DOES NOT RETURN the same value than:[color=blue]> SELECT SUM(field1+field2+field3) FROM MatView with (noexpand)[/color]The second call - using the materialized view - returns a smallernumber (as if counts were lost during our bulk insert)As our data has to be accurate, we cannot use Materialized viewsanymore. This problem does not occur when the amount of data insertedis smaller. Rebuilding the clustered index on the view fixes theproblem; do we have to constantly be rebuilding the index to keep theview synchronize !?!!?!Is there a way to tell that our view is not synchronized? Justcomparing values returned by our view does not work for us as data isconstantly been inserted.System: SQL server 2000 SP3 Enterprise EditionVincent LIDOU
View 6 Replies View RelatedIndexed Views Vs Temp Tables
Jul 20, 2005With my understanding of indexed views and according to books I read"indexed views" are supposed to perform much better than "temp tables"(temp table having primary key and indexed view with clustered indexon the same keys).But when I tried in my system I am getting opposite results. WithIndexed Views it takes 3 times more time.Any body has any reasons for that? Or my understanding was wrong?thanksRaghu Avirneni
View 2 Replies View RelatedIndexed Views - Group By Redundancy
Jul 20, 2005I have a table that I want to have a precalulcate length on a character fieldand group and sum up. Thought I could do this by creating a view with a groupby clause that includes the sum function. Unfortunately, the compilercomplains with:A clustered index cannot be created on the view 'MyView' because the indexkey includes columns which are not in the GROUP BY clause.Wish I could verbalize the problem a little better, but the following pareddown example should serve as a demonstration:SET ANSI_WARNINGS ONSET ANSI_PADDING ONSET ANSI_NULLS ONSET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET NUMERIC_ROUNDABORT OFFGOCREATE TABLE myTable(myID INT NOT NULL,RecNum INT NOT NULL,TestString VARCHAR(80) NOT NULL)GOINSERT INTO myTable VALUES(1, 1, 'a')INSERT INTO myTable VALUES(1, 2, 'ab')INSERT INTO myTable VALUES(2, 2, 'abc')GOCREATE VIEW dbo.MyView WITH SCHEMABINDING ASSELECTmyID = myID,slen = SUM(LEN(TestString)),recn = COUNT_BIG(*)FROM dbo.myTableGROUP BY myIDGOCREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)-- A clustered index cannot be created on the view 'MyView' because-- the index key includes columns which are not in the GROUP BY clause.GODROP VIEW MyViewGODROP TABLE myTableGOThanks,Chris Rathman
View 3 Replies View RelatedSQL 2012 :: Locking Behavior On Indexed Views
Jul 13, 2014I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,
[code]....
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.
SQL Server Destination Adapter + Indexed Views
Apr 22, 2007Hi,
I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:
1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.
2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.
The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.
I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating?
Thanks a lot
Mark
Best Practice For Indexed Views And Aggregates Tables
Apr 10, 2007Hi, all experts here,
Thank you very much for your kind attention.
I am having some questions on indexed views and aggregate tables.
My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?
I am looking forward to hearing from you.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
Creating Indexed Views For Full Text Search
Nov 30, 2007Hi,
I have problem that Im sure others must have had before so I am looking for advice on the best way to solve it. I have a table of text information tbl_base which is related to another table containing tags, which can contain several rows for each row in base.
I want to create an indexed view of the data for full text search. I would like to select all the tags related to a particular row in the tbl_base together into a string and join it to the end of the tbl_base table so they can be indexed along with the tbl_base data for full text indexing. Ive tried several methods but I am never able to make an index on my view because it say I cant use COALESCE, or Cursors.
Sample data here :
tbl_base
id | Text
------------------------
1 | BLah blah
2 | Dum de dum
3 | HAr HAr
tbl_base_tags
tagID | base_id | TagText
--------------------------
1 | 1 | first
2 | 1 | second
3 | 1 | third
4 | 2 | fourth
The view I want to end up with will look like this :
vw_tables
base_id | Text | Tags
-------------------------------------
1 | Blah blah | first second third
2 | dum de dum | fourth
How can I achieve this? Do I have to index all the tables seperately?
Problem Creating Indexed Views With Linked Servers
Jul 20, 2005Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!
View 1 Replies View RelatedSQL Tools :: Indexed Views In Object Explorer Without Index Node
Jul 15, 2014In the SQL Server 2014 Management Studio's object explorer indexed views with schemabinding don't have an index node. Thus, you can create and drop Indexes for views only via T-SQL. The SQL Server Management Studio 2012 still shows the index node.
Is there some Management Studio setting in version 12.0.2000.8 which I am missing?
SQL Server 2008 :: Create Indexed Views On User Defined Functions?
Jul 14, 2015I am creating mateialized view but it is failing with error that it can't be schema bound.
The query I am working to create materialized view are having joins with different tables and function.
Is it possible to create Indexed views on user defined functions?
SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding
Apr 1, 2015when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way
View 1 Replies View RelatedTransact SQL :: Returning Non Indexed Records From Sorted Table - Speed Same As Indexed?
Dec 1, 2015I have created a table from another table where I specified that one of the fields, an number field, is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.
Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?
In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?
Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?
Using Join With Views
Jul 6, 2000I have created 2 views for the same table and I was wonder if it was possible to use a join across these 2 views:
View 1:
CREATE VIEW Hits_per_day
AS
select CONVERT(char(12), TIMESTAMP, 2) AS Day, usergroup, count(*) as Hits
from newslog
where newsevent = "APPSTART"
group by CONVERT(char(12), TIMESTAMP, 2), usergroup
View 2:
CREATE VIEW daily_hits AS
select CONVERT(char(12), TIMESTAMP, 2) AS Day, count(*) as DailyHits
from newslog
where newsevent = "APPSTART"
group by CONVERT(char(12), TIMESTAMP, 2)
I would like to use these 2 views to produce the following result set or close enough:
Date usergrouphits DailyHits
07/02/00 DPRS10 10
08/02/00 DPRS12 12
09/02/00 DPRS12
09/02/00 Members1 13
10/02/00 DPRS15
10/02/00 Members 1 16
11/02/00 DPRS9 9
12/02/00 DPRS2 2
13/02/00 DPRS4
13/02/00 Members1
13/02/00 Senators2 7
I can use a query against view 1 to sum the dailyhits but this is recorded on another row:
select "Date" = (Substring(day,7,2) + '/' + SUBSTRING(day,4,2) + '/' + SUBSTRING(day,1,2)) , usergroup, hits from hits_per_day order by Substring(day,4,2), SUBSTRING(day,7,2) compute sum(hits) by Substring(day,4,2), SUBSTRING(day,7,2)
Results:
Dateusergrouphits
07/02/00DPRS10
sum
===========
10
Dateusergrouphits
08/02/00DPRS12
sum
===========
12
Dateusergrouphits
09/02/00DPRS12
09/02/00Members1
sum
===========
13
Is there a nicer way to display the results with the total of hits for each day on the same line as the other details.
Thank you
Are Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedLarge Views Vs Multiple Small Views
Sep 6, 2007Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedCan Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Apr 30, 2008Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
Recompiling Views That Reference Other Views
Jun 28, 2007Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Quicky : Views Of Views Of Views
Feb 22, 2007Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.