Clustered Index On Indexed View From ADO.NET
Oct 26, 2005
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
View 1 Replies
ADVERTISEMENT
Oct 9, 2015
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?
View 1 Replies
View Related
May 30, 2008
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..
View 1 Replies
View Related
Jul 23, 2005
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 Related
Mar 16, 2001
I have a view named select id,name,state from customer where state ='va'
can I create clustered index on a view (name) if so please provide me with the sql statement.
Thanks
Al
View 2 Replies
View Related
Aug 8, 2015
I have a view that joins a dozen tables with a million rows added per year by an application. I want to materialize it. The view is always filtered by date first on reports, then there are a few key transaction keys, but then many other fields required to make each row unique. I don't want to add these columns since they are large, many, not used for sorting or filtering, and may not define uniqueness in a future application design. I need a uniqueifier that is application agnostic. I prefer a bigint. So to store the materialized view ideally for reporting, I want to add the following clustered index to materialize the view:
CREATE unique CLUSTERED INDEX idx1
ON [dbo].[myview](myDate, key1, key2, key3, id bigint identity(1,1) NOT NULL)
And I get this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bigint'.
Can I do what I want? If so, how?
View 1 Replies
View Related
Nov 14, 2006
the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
View 15 Replies
View Related
Apr 21, 2007
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
View 1 Replies
View Related
Sep 4, 2015
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies
View Related
Aug 28, 2015
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
View 20 Replies
View Related
Jul 19, 2013
I have created two tables. table one has the following fields,
Id -> unique clustered index.
table two has the following fields,
Tid -> unique clustered index
Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
View 5 Replies
View Related
Jan 4, 2008
I have large table with 10million records. I would like to create clustered or non-clustered index.
What is the quick way to create? I have tried once and it took more than 10 min.
please help.
View 1 Replies
View Related
Sep 8, 2006
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.
If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.
So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?
Thanks
View 3 Replies
View Related
Oct 15, 2007
Hi everybody!
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
Thanks for suggestions!
Ralf
View 3 Replies
View Related
Dec 5, 2007
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
View 3 Replies
View Related
Nov 1, 2007
I have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)
Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.
I want to know whether this will slow down,insert and update on the <table1>?
View 2 Replies
View Related
Jul 3, 2006
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
View 4 Replies
View Related
Mar 5, 2015
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
View 1 Replies
View Related
Jun 18, 2008
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance.
Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
Thank you
Goldmember
View 2 Replies
View Related
Jan 10, 2002
Hi All,
I have over 100 views in a database. How can I check which view we used indexed view?
Thanks.
View 2 Replies
View Related
Jan 11, 2002
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.
View 2 Replies
View Related
Oct 19, 2004
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:
View 4 Replies
View Related
Mar 9, 2004
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
View 1 Replies
View Related
Jan 11, 2007
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?"
View 1 Replies
View Related
Mar 11, 2008
what does it mean ? Why it is necessary to create/put it ? How do you use it in SQL 2005 or 2000 ?
View 5 Replies
View Related
Aug 1, 2006
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
View 1 Replies
View Related
Dec 11, 2006
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
View 1 Replies
View Related
Oct 13, 2007
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 Related
Nov 15, 2006
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,
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 )
View 1 Replies
View Related
Sep 12, 2006
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
View 2 Replies
View Related
Nov 19, 2004
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.
View 2 Replies
View Related
Mar 17, 2004
I have an Indexed View with followed syntax:
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
But it still slow.
I really apreciate comments.
View 1 Replies
View Related
Jul 17, 2015
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.
View 12 Replies
View Related
