hi frends.
i have such a great problem.i want to use union in indexed
view and i must have to use it. because i cant make a one table
because in those two tables there are 2,000,000 records entered
daily.So please give me suggestion. can also give me alternate of
using UNION in indexed view.and i want to use idexed view only because
i will do searching afterwards.
Regards,
Mr.Mirza
Software Engineer.
Hello,
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
I 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).
Hi,
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
Dear 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.
is there any work around this?
View 1 Replies View RelatedI 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
How 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!
I 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?
Lalitha 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?"
Hi, 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??
Hi 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.
Hello folks!
I'm looking for a good article about Indexed Views.
Cheers!
Rafael
Hi 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
This 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 RelatedDo 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 RelatedWith 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 RelatedI 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 RelatedI 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.
Hi,
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
Hi, 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,
Hi,
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?
Hi, 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 RelatedIn 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?
I 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?
when 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 RelatedFor a Union All of Views where both views look at the same openrowset file I get
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
This probably occurs because the first view keeps the file open.
Any workarounds on this?
I have a union view that unions 36 tables which have a date constraint set on the datetime field, the data for which is is same for all records within the respective tables. The purpose of this is to allow users to go straight to the month that they require. This works fine with raw sql queries using hardcoded dates , the query plan shows that only the required tables are accessed. However, if i create a lookup table (this is for business objects), join the union view date field to date field and use a between criteria, it ignores the constraint and scans all 36 tables in the view. Changing the between to = shows that the constaint is used. Does anyone have any ideas here , i seem to have tried everything
Mark
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
Hi folks,I'm having issues trying to create a union using two Views I created.View 1:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, ExDescription.Surname + '. ' + ExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM MONDAS.Reaseheath.dbo.ExDescription AS ExDescription INNER JOIN dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id ON ExDescription.AccountCode = dbo.Development.Related_Staff_Id COLLATE Latin1_General_CI_AI View 2:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, dbo.NonStaffExDescription.Surname + ', ' + dbo.NonStaffExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id INNER JOIN dbo.NonStaffExDescription ON dbo.Development.Related_Staff_Id = dbo.NonStaffExDescription.AccountCode In the first view I am linking to another server, which is why I have had to change the collation, but the second view all the tables are in the same database hence the collation is the same.But when I'm trying to do a UNION between the two I get another collation error, but can't figure out how to change the collation like I did in the first view.Heres the UNION query:SELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView1UNIONSELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView2 Can anyone help?Thanks
View 3 Replies View RelatedI 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?
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z