Hi,
While creating an indexed view with the command :
create unique clustered index idx_atrid on account_transactions (policy)
there is a check constraint on the policy column of the tables used in this view.
The following error is encountered
Cannot index the view 'test.dbo.account_transactions'. It contains one or more disallowed constructs.
Can anyone help?
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
All,
I have a huge problem to solve.
1) Need to create an Indexed View that joins multiple tables.I know how to create a regular View, but How do I create an Indexed View ?
2)Need be able to replicate the Indexed View [above] across to another server B into a table.
I have to get this done and seriously don't know where to start.
Do HELP me out .Thank You.
I'm not able to create indexed views,
which are clustered-indexed on only 1st field.
I can't even INCLUDED other fields.
I need the entire view to exist as a physical table. (for performance)
Please let me know the work around.
Thanks..
Hello,
I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:
(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))
it always return '0'.
Here is a very cut down version of the view, only selects the uid! from a single table
IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL
DROP VIEW GetMessageQueueDetails ;
GO
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW GetMessageQueueDetails
WITH SCHEMABINDING
AS
SELECT Uid
FROM dbo.MyTable
GO
I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :
"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."
Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?
Or am I doing something else wrong?
Can anybody offer any help?
Thanks
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?
View 3 Replies View RelatedHi! This is my first post and I really need help with Partitioned View. I'm using Sql Server 2000 and I created a partitioned view using 6 tables and now a need to create the table '7' and alter the view. But when i'm trying to insert new data i'm receiving the message: :eek:
"Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition
contains a disallowed construct."
My code is:
drop VIEW tb_sld_cob_pap
GO
CREATE TABLE dbo.tb_sld_cob_pap_7 (
cod_operacao int NOT NULL ,
cod_contrato int NOT NULL ,
sequencial_duplicata int NOT NULL ,
data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
liqex_dia_nom_outros float NULL ,
liqex_dia_moe_outros float NULL,
constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
)
GO
CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
GO
CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
GO
ALTER TABLE dbo.tb_sld_cob_pap_6
DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
GO
ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))
GO
create VIEW tb_sld_cob_pap
as
select * from tb_sld_cob_pap_1
union all
select * from tb_sld_cob_pap_2
union all
select * from tb_sld_cob_pap_3
union all
select * from tb_sld_cob_pap_4
union all
select * from tb_sld_cob_pap_5
union all
select * from tb_sld_cob_pap_6
union all
select * from tb_sld_cob_pap_7
My table tb_sld_cob_pap_6 does NOT have data with ([data_sld_pap] >= '20060201').
I'm using this script in other database and I don't have this problem.
Thank you...
USE Northwind
GO
CREATE TABLE myTable99_1 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
CREATE TABLE myTable99_2 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
CREATE TABLE myTable99_3 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))
CREATE INDEX myTable99_1_IX ON MyTable99_1
(Account, Ledger)
CREATE INDEX myTable99_2_IX ON MyTable99_2
(Account, Ledger)
CREATE INDEX myTable99_3_IX ON MyTable99_3
(Account, Ledger)
GO
CREATE VIEW myView99
AS
SELECT Account
, Ledger
, PostDate
FROM myTable99_1
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_2
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_3
GO
SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
GO
DROP VIEW myView99
DROP TABLE myTable99_1, myTable99_2, myTable99_3
GO
OK, so I thought I knew this, but I'm looking for parallelism...not only am I no getting it, I'm getting an Index scan....is it becuse I didn't put any data in the table? I thought it would stil show my index seek with parallelism
What up, homey?
This post concerns updating across a partitioned view, and not unlike others about this subject I am getting this error:
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'dbII.dbo.MyTable' is not updatable because a partitioning column was not found.
I am aware of the rules for defining a partitioning column, but interpreting them may have beaten me. So perhaps I haven't abided by all the rules. How to spot which one(s) from the view and table definitions? I suspect the CHECK constraint does not allow the ASCII function, but I can't see how to avoid using it given SYSCODE entries in one table are like "[A-Z]%" and in the other are like "[0-9]%".
Otherwise, I suspect it is because one of the tables has, by legacy, a text column and the view is casting it to varchar(MAX). I also suspect it is because there's a second column with a unique index. These aren't mentioned in the rules (are they?).
Here's the view definition:
SELECT SYSCODE, COL2, CAST(COMMENTS AS varchar(MAX)) AS COMMENTS
FROM dbo.MYTABLE
UNION ALL
SELECT SYSCODE, COL2, COMMENTS
FROM OTHERDATABASE.dbo.MYTABLE AS MYTABLE_1
And here are the table definitions:
-- Table in the database where view is defined
CREATE TABLE [dbo].[MYTABLE](
[SYSCODE] [char](12) NOT NULL,
[Code]....
Hi all,
I am designing 3 p:artitioned views for 3 tables. Those tables grow up in 1.5 millions of rows per month (each one), so I decided to partition those tables monthly. The issue is that if I want to create the views with more than 256 months (256 tables) SQL Server says: 'Server: Msg 106, Level 15, State 1, Procedure Jugadas, Line 258Too many table names in the query. The maximum allowable is 256.'
Is there any workaround for this?
Another solution maybe?
PD1: I've tested with less than 256 tables and it works fine, I can update and query the tables (except for a couple of querys where I've got to join 2 or more of the involucred views in which case I got a similar error saying about a 260 table limit).
I have a table that I'm trying to scale out into a partitioned view. It's about 30 million rows. It's a workflow table and I have a taskID in the table. Originally the table was partitioned on this column but performance still wasn't what I wanted it to be, so we figured out how we could partition on a bit flag of IsOpen.
Question #1) Anyone know a best practice for creating apartitioned views on multi-columns?
What I'd like to try to do to lower the complexity of the original partitioned view is to create a view of partitioned views. Is this even possible (This is Q#2, BTW).
Hi All,
I have over 100 views in a database. How can I check which view we used indexed view?
Thanks.
Hi All,
I have over 100 views in a database. I created non-clustered index on views. Now I need to check out which views have index.
How can I check which views we created as indexed views?
Thank you very much.
Hi
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??
please help :confused: :mad: :eek:
Hi,
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?
Cheers
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.
why so?"
what does it mean ? Why it is necessary to create/put it ? How do you use it in SQL 2005 or 2000 ?
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?
Thanks
I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
Hello :-)My question is: If I query a partitioned view, but don't know the valuesin the "where x in(<expression>)" clause, i.e.: select * from viewAwhere intVal in(select intVal from tbl1) . Compared to: select * fromviewA where intVal in(5,6).Of course "intVal" is partitioning column.Will this result in an optimized query that searches only the relevanttables?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedUsing SQL Server 2005. Defined partitioned view with computed column. Computed column was a constant varchar. Ran a SELECT. According to Query Execution Plan, SQL did recognize the computed column as the partitioning column and used it to optimize the query.
However MSDN says a computed column cannot be used as the partitioning column.
Could someone from MS clarify?
Schema below.
The execution plan shows that this query is correctly optimized to check only the underlying Employee_2008 table.
select * from Employee where ReportingYear = '2008'
This query is not optimized and checks both Employee_2008 and Employee_2007:
declare @ry varchar(4)
set @ry = '2008'
select * from Employee where ReportingYear = @ry
How can I get second query to be optimized correctly?
Schema:
CREATE TABLE [dbo].[Employee_2007](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Employee_2008](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE VIEW [dbo].[Employee]
AS
SELECT
'2007' ReportingYear,
EmployeeID,
Name
FROM Employee_2007
UNION ALL
SELECT
'2008' ReportingYear,
EmployeeID,
Name
FROM Employee_2008
Hello,
please enlighten me regarding an issue with partitioned view... There are 3 tables in my DB of a similar structure:
CREATE TABLE Table1 (value1 varchar(1))
CREATE TABLE Table2 (value1 varchar(1))
CREATE TABLE Table3 (value1 varchar(1))
INSERT INTO Table1 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table2 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table3 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
As sometimes we need to access all data from these tables, a view has been created:
CREATE VIEW AllData AS
SELECT value1, '1' as table_id from Table1
UNION ALL
SELECT value1, '2' as table_id from Table2
UNION ALL
SELECT value1, '3' as table_id from Table3
The problem is that while running a query like
SELECT * from AllData WHERE value1 = 'a' and table_id = '3'
I see a table scan being performed on all 3 tables, not just table3 - i.e optimisation engine doesn't care for my table_id computed column and for that fact that required data is located ONLY in Table3.
Is there any way to force optimiser to consider this column andrrebuild a plan? If not - how can I rebuild a view (I can't modify tables) to achieve that? Maybe create an index for a view?
Thanks in advance. RTFM and search don't seem to clarify this for me...
I am setting up 3 Linked Servers (SERVER_A, SERVER_B and SERVER_C) in an isolated local network. They are all running SQL Server 2005 Developer Edition, all on XP SP2. On each server, I have a distributed partitioned view named WAREHOUSE_ALL that basically is the UNION of all WAREHOUSE tables.
I am having trouble in running write (INSERT, UPDATE or DELETE) queries on the distributed partitioned view. The error returned was (run from SERVER_B)
OLE DB provider "SQLNCLI" for linked server "SERVER_A" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 7The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVER_A" was unable to begin a distributed transaction.
However, executing a read (SELECT) query ran smoothly without error.
I have done all the steps required as described in the article at http://support.microsoft.com/?kbid=873160 . Note that the only difference between the situation and our situation is the provider (SQLOLEDB and SQLNCLI), which I guess does not important. Unfortunately, the error still comes out.
After reading heaps of other article, I suspected that there is something wrong with MSDTC. As far as I know, all the settings for MSDTC were set accordingly. Then, I ran DTCPing - http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en and the error returned was
DTCping log file: C:Documents and SettingsAdministratorDesktoplSERVER_B2496RPC server is ready
Please Start Partner DTCping before pinging
++++++++++++Validating Remote Computer Name++++++++++++
Please refer to following log file for details:
C:Documents and SettingsAdministratorDesktoplSERVER_B2496.log
Invoking RPC method on SERVER_C
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
And here is the log file:
Platform:Windows XP
IP Configure Information
Host Name . . . . . . . . . : SERVER_B
DNS Servers . . . . . . . . : 129.78.99.2
Node Type . . . . . . . . . :
NetBIOS Scope ID. . . . . . :
IP Routing Enabled. . . . . : no
WINS Proxy Enabled. . . . . : no
NetBIOS Resolution Uses DNS : no
Ethernet adapter {4404F3CB-F4B7-4990-912C-E69721C885B1}:
Description . . . . . . . . : 3Com EtherLink XL 10/100 PCI TX NIC (3C905B-TX) #2 - Packet Scheduler Miniport
Physical Address. . . . . . : 00-01-02-85-B8-A9
DHCP Enabled. . . . . . . . : no
IP Address. . . . . . . . . : 172.19.102.35
Subnet Mask . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . : 172.19.102.250
DHCP Server . . . . . . . . : 255.255.255.255
Primary WINS Server . . . . : 0.0.0.0
Secondary WINS Server . . . : 0.0.0.0
Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
++++++++++++lmhosts.sam++++++++++++
++++++++++++hosts ++++++++++++
127.0.0.1 localhost
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SERVER_B
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
10-05, 17:10:54.769-->Start DTC connection test
Name Resolution:
SERVER_C-->172.19.102.36-->SERVER_C
10-05, 17:11:09.781-->Start RPC test (SERVER_B-->SERVER_C)
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
I guess it could be due to port problem, which I have already opened in the Windows Firewall. There is one article which is confusing me -> Update to automatically open port 135 in Windows Firewall when a TCP or a UDP RPC server registers with the endpoint mapper at http://support.microsoft.com/kb/838191 (This article shows automatic opening of port 135!)
Please help me. Thanks.
Greetings once again my SQL friends,
I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :
Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation
How to solve this problem?
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
Thanks for your assistance.
Tom
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.
Thanks,
Tom
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.
View 1 Replies View RelatedI 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,
COUNT_BIG(*) AS COUNT
FROM
JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO
INNER JOIN
JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT
ON
VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY
AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER
AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER
AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER
INNER JOIN
JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL
ON
VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY
AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER
AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER
AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER
AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER
AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER
AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'
AND VEW_F_INVC_DTL.GIFT_CARD = 'N'
INNER JOIN
JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER
ON
VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER
AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1
INNER JOIN
JJWHSE.TEW_D_DT TEW_D_DT
ON
VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY
INNER JOIN
JJWHSE.TEW_D_LOC TEW_D_LOC
ON
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY
WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'
GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,
TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC
GO
CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )
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 problem while I try to insert data into a partioned view I am
getting the following error.
Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.
Any thoughts
USE pubs
CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO
ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies View RelatedWe have a situation where queries against a partitioned view ignore a suitable index and perform a table scan (against 200+MB of data), where the same query on the underlying table(s) results in a 4 page index seek. I can€™t find any mention of the situation, so I€™m trying a post here.
We€™re running SQL Server 2005 Enterprise edition sp2 on Windows 2003 Enterprise Edition sp1 on a two node cluster, and it also occurs on a stand-alone development box with Developer edition. We have four tables, named Options#0, Options#1, Options#2, and Options#3. All are almost identical (script generated by SSMS and edited down a bit):
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Options#0](
[ControlID] [tinyint] NOT NULL CONSTRAINT [DF_Options#0__ControlID] DEFAULT ((0)),
[ModelCode] [char](8) NOT NULL,
[EquipmentID] [int] NOT NULL,
[AdjustmentContextID] [int] NOT NULL,
[EquipmentCode] [char](2) NOT NULL,
[EquipmentTypeCode] [char](1) NOT NULL,
[Description] [varchar](50) NOT NULL,
[DisplayOrder] [smallint] NOT NULL,
[IsStandard] [bit] NOT NULL,
[Priority] [tinyint] NOT NULL,
[Status] [bit] NOT NULL,
[Adjustment] [int] NOT NULL,
CONSTRAINT [PK_Options#0] PRIMARY KEY CLUSTERED
(
[ModelCode] ASC,
[EquipmentID] ASC,
[AdjustmentContextID] ASC,
[ControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Options#0] WITH CHECK ADD CONSTRAINT [CK_Options#0__ControlID] CHECK (([ControlID]=(0)))
ALTER TABLE [dbo].[Options#0] CHECK CONSTRAINT [CK_Options#0__ControlID]
The only differences between the tables are in the names and in the value defaulted to and CHECKed, which matches the table name (to support the partitioned view, of course).
We receive and load data ever week and every two month, and use an unlikely algorithm to load and manage its availability by running an ATLER on the view (to maintain the access rights defined for the hosting environment). Scripted out via SSMS, the view looks like:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[Options] AS select * from Options#1 union all select * from Options#3
The problem is that when we issue a query like
SELECT count(*)
from Options
where ControlID = 1
and ModelCode = '2004NIC9'
The resulting query (as checked via the query plan and SET STATISTICS IO on) will get €œpartitioned€?, running against the proper table, but it will ignore the query, perform a table scan, and churn through 200+MB of data. A Similar query run against the underlying table
SELECT count(*)
from Options#1
where ControlID = 1
and ModelCode = '2004NIC9'
(with or without the ControlID = 1 clause) will perform a Clustered Index Seek and read maybe 4 pages.
Analyzing the execution plan shows that the table query work like you€™d think, but for the query against the view we get a Clustered Index Scan, with predicate:
[DBName].[dbo].[Options#1].[ControlID]=(1) AND CONVERT_IMPLICIT(char(8),[ DBName].[dbo].[Options#1].[ModelCode],0)=€™2004NIC9€™
I get the same results when explicitly listing all columns in the view. The code page on the view and tables is the same (as determined by checking properties via SSMS).
Why is the table data column being implicitly converted to the data type that it already is? Why does this occur when working with the partitioned view but not with the actual table? Can this behavior be controlled or modified without losing the (incredibly useful) data loading management benefits of the partitioned view? I€™m guessing (and hoping) it€™s some subtle quirk or mis-setting, please set me on the right path!
Philip Kelley