I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
I 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?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I am trying toe create an indexed view but cannot seem to get it right.
CREATE VIEW dbo.D_Policy_View with schemabinding AS SELECT Policy_ID, Environment_Code, CoB, Sub_CoB, Policy_No, Version_No FROM dbo.D_Policy WHERE (Policy_ID IN (SELECT MAX(Policy_ID) FROM dbo.d_Policy GROUP BY Environment_Code, COB, Policy_No, SUB_COB))
I have read on BoL that MAX is not allowed but don't know of any other way to get the latest record??
I have a problem trying to create an indexed view on SQL Server 2000. There are multiple databases, one of which stores system wide data. I would like to create an indexed view on the system wide data for each of the site databases.
Is it possible to create an indexed view on data in another database?
snehalata writes "i create view as follows CREATE VIEW Data WITH SCHEMABINDING AS SELECT A.PartitionID,FundID,ReportDate,ForeignTaxWithheld,DomDividendIncome,RGainShortTerm,RGainLongTerm,NewIssueRGainShortTerm, NewIssueRGainLongTerm,ChgUnrealizedGain,ReplaceTax,TotIncomeBefFee,TotalIncome,EndingNetCapital,EndingRedemptionUnits, BeginRedemptionAmount,EndingRedemptionAmount,EndingUnits,InterestOverseas,ExpenseOverseas,OrdIncome, ReallocationExpense,BeginRedemptionFee,EndingRedFee,BeginGrossCapital,EndingGrossCapital,GPFees,FixedExpense,MergerCost, SellingCommission,GrossRoR,NAV,GAV,GPMgmtFee,IMMgmtFee,GPIncentivefee,IMIncentivefee,NetRoR,MonthCounter, BegUnits,BegAddUnits,BegAddAmount,EndAddAmount,GrossRealizedGain,BrokerCommission, NetRealizedGain,OperatingExpense,OffsellExp,OrgExp,USObligationIncome,FixIncomeIntrIncome, CapitalGain,SellingFee,SellingMgmtFeeMidQtr,SyndicateCost,BeginNetCapital,DomesticDividendExp,FixedIncomeIntrExp FROMdbo.vPart1A LEFT JOIN dbo.vPart2B ON A.PartitionID= B.PartitionID
then if i creat index as follows
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF
create unique clustered index ind1 on Data(FundID,ReportDate) it gives me following error Cannot index the view 'MonthliesTest2.4.dbo.Data'. It contains one or more disallowed constructs.
I created an indexed view in SQL 2000, and I expected to see the index created on the view referenced in the execution plan when I query the view. Instead, I see the index for the base table referenced in the execution plan. Why?
There are 6,000,000+ records in the base table, and the view only references 256 of these rows.
Here is some of the DDL if you need it:
CREATE TABLE [alarm_t] ( [ct_dtm] [datetime] NOT NULL , [dst_flg] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [stn_nm] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [alarm_txt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [utc_dtm] [datetime] NOT NULL , [create_utc_dtm] [datetime] NOT NULL ) ON [PRIMARY] GO
CREATE CLUSTERED INDEX [alarm_idx2] ON [dbo].[alarm_t]([ct_dtm], [stn_nm], [dst_flg]) ON [PRIMARY] GO
create view dbo.alarm_Mapbd_v with schemabinding as SELECT [ct_dtm], [dst_flg], [stn_nm], [alarm_txt], [utc_dtm], [create_utc_dtm] FROM [dbo].[alarm_t] WHERE[stn_nm]= 'Mapbd' GO
create unique clustered index alarm_Mapbd_idx1 on dbo.alarm_Mapbd_v ( stn_nm, ct_dtm, dst_flg ) go
update statistics alarm_t go update statistics alarm_Mapbd_v go
The following 2 queries have the exact same execution plan, both showing a cost of 50%. I expected to see the index created on the view referenced in the execution plan for the first query. Is the index created on the view being used?
selectstn_nm, ct_dtm, dst_flg fromalarm_Mapbd_v go SELECT [ct_dtm], [dst_flg], [stn_nm], [alarm_txt], [utc_dtm], [create_utc_dtm] FROM [dbo].[alarm_t] WHERE[stn_nm]= 'Mapbd' go
I have created a unique clustered index on a view. The view does a GROUP BY on 3 of the columns and uses the COUNT_BIG aggregate function. I used the following SET commands before creating the view and the index:
SET ARITHABORTON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLSON SET ANSI_PADDINGON SET ANSI_WARNINGSON SET NUMERIC_ROUNDABORTOFF
I can insert and delete rows from the base table, and the indexed view is updated fine.
However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:
Executed as user: NT AUTHORITYSYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Why am I getting this error?
The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.
I was looking to improve the performance of an ASP.NET application bycreating the an indexed view that could be used instead of some of theroot tables.What I didn't realize is that it would affect any future conenctionsto the root tables.This of course crashed the application on any type of insert, update,or delte from the root tables.Funny thing is when I removed the indexed view -- it didn't help.I tried flipping the offending options to the opposite of way theywere set on the database and the errors wouldn't go away.I ended up restoring the database from before my mess up to fix it.I was hoping that the SQL experts out there might shed some light onmy problem before I try again.
I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!
Here is the Error I am given
"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3
Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."
Here is my code..
CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING
AS
SELECT TEW_D_DT.DT_KEY AS DATE_KEY,
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,
TEW_D_LOC.LOC_DESC AS LOC_DESC ,
TEW_D_LOC.RGN_DESC AS REGION_DESC,
TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,
ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,
I am trying to create an indexed view with the following code, however it fails with the following error :
An index cannot be created on the view 'TST_SCH' because the select list of the view contains a non-aggregate expression.
- When I remove the case statement it works fine, what can I do to make this work, this is just part of the calculation, I have many such case statements that I will need to use. The column is NON-NUllable in the database - I have verified all columnproperty values in the select statement they are all indexable, the view, when created appears non-indexable, I have been able to create the view WITH SCHEMABINDING, the error occurs when I try to create an index on Column1(CBSA_CODE). I am using SQL Server 2000 enterprise edition (SP4).
Please help.
Thanks,
select DIM_IND_VEH_REG_CUST.CBSA_CODE as CBSA_CODE, DIM_IND_VEH_REG_CUST.CBSA_NAME as CBSA_NAME, DIM_DLR.DLR_NBR AS DLR_NBR, DIM_DLR.PRIM_DLR_NAME as PRIM_DLR_NAME, --DIM_IND_VEH_REG_CUST.SALES_DISTRICT_NBR AS Sales_District_Nbr, DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP AS Mkt_Shr_Ind_Prod_Grp, DIM_IND_VEH_PROD.IDC_VEH_MFGR_DESC AS Veh_Manufacturer_Desc
,(CASE WHEN DIM_DATE.YR = 2006 and DIM_DATE.MTH_NBR = 8 THEN SUM(FACT_IND_VEH_RTL_TXN.UNIT_SOLD_CNT) ELSE 0 END) AS CYCP_ALL_Unit_Cnt
,count_big(*) COUNT_BI
from dbo.DIM_IND_VEH_REG_CUST(NOLOCK), dbo.DIM_IND_VEH_PROD (NOLOCK), dbo.FACT_IND_VEH_RTL_TXN (NOLOCK), dbo.DIM_DATE (NOLOCK), dbo.DIM_DLR(NOLOCK) where DIM_DATE.YR = 2006--(select YEAR(InfoWhseTxnDate) from dbo.audObject a, dbo.audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN')--@YEAR OR DIM_DATE.YR = 2005--(select YEAR(InfoWhseTxnDate) from dbo.audObject a, dbo.audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN') --(@YEAR-1)
-- and DIM_DATE.MTH_NBR = (select month(InfoWhseTxnDate) from (select InfoWhseTxnDate from audObject a, audLoadTxnCurr b where a.ObjectID = b.ObjectID and a.ObjectName = 'FACT_IND_VEH_RTL_TXN') txn_month) and DIM_IND_VEH_REG_CUST.CBSA_NAME <> '' and DIM_DLR.PRIM_DLR_NAME <> '' and DIM_IND_VEH_REG_CUST.CBSA_CODE not in ('0' ,'00000') AND DIM_IND_VEH_REG_CUST.CBSA_CODE IS NOT NULL AND IDC_VEH_MFGR_DESC = 'Kawasaki' and DIM_DLR.DLR_KEY = FACT_IND_VEH_RTL_TXN.DLR_KEY and DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP <> 'UTILITY VEHICLE' and DIM_IND_VEH_PROD.IND_VEH_PROD_KEY = FACT_IND_VEH_RTL_TXN.IND_VEH_PROD_KEY and DIM_IND_VEH_REG_CUST.IND_VEH_CUST_KEY = FACT_IND_VEH_RTL_TXN.IND_VEH_CUST_KEY and DIM_DATE.DATE_KEY = FACT_IND_VEH_RTL_TXN.DATE_KEY
group by DIM_IND_VEH_REG_CUST.CBSA_CODE, DIM_IND_VEH_REG_CUST.CBSA_NAME, DIM_DLR.DLR_NBR,DIM_DATE.YR,DIM_DATE.MTH_NBR, DIM_DLR.PRIM_DLR_NAME, DIM_IND_VEH_PROD.MKT_SHR_IND_PROD_GRP, DIM_IND_VEH_PROD.IDC_VEH_MFGR_DESC
I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.If anyone has experienced this please let me know.Cheers
Just wonder how many of you are using or had used indexed view in SQL 2000? Please, share any details/info.
We are currently using in and having problems as we are getting Errors 644, 'Couldn't find index...' every time we try to update the underlying tables.
CREATE VIEW VW_Emplacamentos WITH SCHEMABINDING AS SELECTA.CD_Linha -- This is the unique clustered field A.DT_Emplacamento, A.NR_CNPJ, A.CD_Municipio, A.VL_Potencia, A.VL_CapacidadeCarga, A.NR_QtdPax, A.NR_AnoFabricacao, A.NR_Chassi, A.SG_UF, A.CD_MarcaModelo, A.CD_Procedencia, A.CD_Combustivel, B.NM_Municipio, C.NM_Combustivel, D.NM_MarcaModelo, D.CD_Segmento, D.CD_Fabricante, E.NM_Segmento, F.NM_SubSegmento, G.NM_Fabricante
FROM dbo.TB_Emplacamentos AS A INNER JOIN dbo.TB_Municipio AS B ON A.CD_Municipio = B.CD_Municipio INNER JOIN dbo.TB_Combustivel AS C ON A.CD_Combustivel = C.CD_Combustivel INNER JOIN dbo.TB_ModeloVeiculo AS D ON A.CD_MarcaModelo = D.CD_MarcaModelo INNER JOIN dbo.TB_SegmentoVeiculo AS E ON D.CD_Segmento= E.CD_Segmento INNER JOIN dbo.TB_SubSegmentoVeiculo AS F ON D.CD_SubSegmento = F.CD_SubSegmento INNER JOIN dbo.TB_Fabricante AS G ON D.CD_Fabricante = G.CD_Fabricante
The following query is not getting perfomance. I don't what's going on.
SELECT COUNT(*) FROM VW_Emplacamentos WHERE DT_Emplacamento BETWEEN '20040301' AND '20040316' AND NM_Fabricante = 'VW'
I've created an compost index on the following sequece DT_Emplacamento,NM_Fabricante,NM_Segmento
I have a view that takes 14 mins to return 3000 rows. Â At some points in the execution plan, there are over 4 million rows. Â The query for the View has a Distinct, a couple columns are populated by functions, there is a cross join, there is a join on a table with no keys or index, joins on non-key varchar columns and so forth. Â It looks a bit like this:
SELECT DISTINCT TOP 100 PERCENT N.Id, N.CountyName, N.OperationName, N_LO.Name, N_LO.LastName,N_LO.Address1, N_TO.Name, N_TO.LastName, dbo.fn_Sections(N.Shape, s.Points) AS Sections
[code]...
The DISTINCT is in there to get rid of the extra Bob rows, because Bob has many sizes. Â I need to keep the multiple sizes on Bob's row, but I don't want multiple rows of Bob. Â Is there a more efficient way to do that other than using DISTINCT?
Second, word on the street is that functions are best avoided if you are going for speed. Â I don't see a way to use "FOR XML PATH" outside the function, because I can't get it to work on just one column. Â So I am looking for a way to produce the multiple values on one row.
I have an indexed view with schema binding and text catalog. So far, so good. Now I have the need to know if the rows in this view have matching records in another table. Ouch.
The relation is 1 to n, where n can be 0 or more. For example:
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2 Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
Hello All, I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic.
I recently added several indexed views to a high traffic table (high volume of both inserts and selects) because I needed to have some complicated unique constraints involving columns that allow NULL.
While I didn't notice performance problems at first, it appears to be that the CPU on the SQL Server is getting pegged when more than 10 or 15 simultaneous inserts are happening on the table. This is a quad proc 3Ghz Xeon, so the fact the CPU is hitting 90%+ while doing 15 inserts a second doesn't make sense to me.
Very quickly the sproc that is being repeatedly called by some middle tier components is taking 30+ seconds to execute, eventually causing timeouts. This sproc is very simple. It does a few quick select statements (that take well under 100ms), and then does an insert into the table in question. That's it.
The only thing I can think of is that the overhead of 3 separate indexed views on the table, each of which contains a significant subset of the total rows in the table (550,000+ rows), is causing SQL Server to get swamped trying to keep the indexes up to date.
Does this seem like a possibility? I'm planning on temporarily removing those indexed views to see how it performs without them, although this is dangerous because it creates the potential for invalid data.
select episode_key as episode_key, ee.object_key, ee.encounter_begin_dt , cp.procedure from dbo.encounters e
join (
select episode_key as minepisode, min(isnull(e.object_key,e.Object_key_History) ) as object_key, min(e.encounter_begin_dt)as encounter_begin_dt
from dbo.encounters e
join dbo.CD_Procedure CP on CP.Procedure_CD = e.procedure_cd where cp.procedure > 0
group by e.episode_key
)ee on e.object_key = ee.object_key
join dbo.cd_procedure cp on cp.procedure_cd = e.procedure_cd
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON vwLookAdmissionRecord (episode_key)
but i keep getting an error:
annot create index on view "dbo.vwLookAdmissionRecord" because it references derived table "ee" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.
Hello, I am trying to add a View to an existing publication and it the subscribers (all devices with SQL CE) don't get the View after replication. I have deleted and recreated the publication, and only the tables will appear on the device, not the view.
Also, I want the data from the view to be dynamic, filtered by using the Host_Name() function/value. Will this work for a View?
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5. I have published Tables, Views and SPs as articles. One of the views has more than 300 columns. So when i try to replicate it, I'm encountering the
following error Message.
"Error 20068: The article cannot be created on table because it has more than 255 columns."
When a view can be created with more than 255 cokumns, why the problem arises when we replicate
prerequisites: MSSQL 2005 64bit as publisher (transactional replication) MSSQL 2000 32 bit as subscriber (pull, read-only)
All objects are set to replicate except foreign keys and defaults. There is a linked server DW on both the publisher and the subscriber that both can access (sa and user logins mapped) The snapshot generation and loading is fine.
but then I issue this statement at the publisher:
ALTER VIEW [dbo].[v_Test] as select * from DW.DW_DB.dbo.v_Test
but it fails to replicate at the subscriber with the message: Category:COMMANDSource: Failed CommandNumber: Message: ALTER VIEW [dbo].[v_Test] asselect * from DW.DW_DB.dbo.v_TestCategoryQLSERVERSource: SubscriberNumber: 7399Message: OLE DB provider 'SQLOLEDB' reported an error.
I tried the command on the Subcriber via Query Analyzer and it works. What gives?