May 6, 2008 at 5:58 am
I want to create a clustered Index on a view referencing Customer table.
My view is like this.
CREATE VIEW Vw_Name
WITH SCHEMABINDING
as
SELECT customerID from dbo.customer where valid = 'Y'
and date in (select max(date) from dbo.customer)
After creating the view I tried to create a Index on this view.
CREATE UNIQUE CLUSTERED INDEX Unq_customer_id ON
Vw_name (customer_id)
Instead I am getting an error:
Cannot index the view 'Customer.dbo.Vw_name'. It contains one or more disallowed constructs.
What may be the issue?
May 6, 2008 at 6:37 am
Per Microsoft you can not have a subquery in the from clause.
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
May 6, 2008 at 6:49 am
I got to know that It cannot be indexed, so how can I re-write the view so that I can create a Indexed view?
I have customerID for every day but I have to select the customerID who are valid for the latest date in customer table.
May 6, 2008 at 7:00 am
Try turning the subquery into a derived table and joining to it.
John
May 6, 2008 at 7:15 am
John Mitchell (5/6/2008)
Try turning the subquery into a derived table and joining to it.John
Derived tables are not allowed in indexed views either.
😎
May 6, 2008 at 7:45 am
Is an indexed view really the best choice here? Since you only want data for the latest date that could be accomplished through a function and indexing the table.
May 6, 2008 at 7:55 am
The reason why I want create a view is that my application has around
35 procedures where in each of these procedures I have used the query.
Select t1.column1,t2.column2 from table1 t1 inner join table2 t2
ON t1.id = t2.id where t1.customerid [not | in] (
SELECT customerID from dbo.customer where valid = 'Y'
and date in (select max(date) from dbo.customer))
So I want to substitute this with the view and can replace this in almost all the procedures.
May 6, 2008 at 8:04 am
Not sure if this is totally correct, but this is the view I'd start with.
create view Vw_name
as
select
cust.customerID
from
dbo.customer cust
inner join (select
cs.customerID,
max(cs.date) as maxdate
from
dbo.customer cs
group by
cs.customerID) dt
on (cust.customerID = dt.customerID)
where
cust.valid = 'Y'
Also, it may be worth the extra time to look at those queries and see if they could be rewritten to be more performant.
😎
May 6, 2008 at 8:24 am
I understand your reason for wanting to reuse the view. I think that if the underlying table is indexed you wouldn't need to also index the view. I'm unsure of the performance increase you would realize even if the indexed view were possible.
MHO
ST
May 4, 2009 at 4:32 am
Hi, I couldn't able to create clustered unique index on view. The view using lots of derived table and self join.
Could you please help me out how to restructure view to create an index.
below is the query..
CREATE VIEW dbo.DataStore
AS
SELECT D.DS_ID AS DatastoreID, D.PHYSCL_NM AS TechnicalDataStoreName, PL.PLTFRM_NM AS Platform, I.PLTFRM_VRSN AS PlatformVersion,
I.INSTNC_ID AS Instance, I.INSTNC_NM AS InstanceName, E.ENVRMNT_ACRNYM AS DeliveryVersion, DV.DVC_NM AS Machine,
DV.MAIL_CD AS [Machine Mailcode], DV.EXP_DT AS [Machine Expiry],
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS M
WHERE (DS_ID = D.DS_ID)) AS [Total Objects],
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS N
WHERE (DS_ID = D.DS_ID) AND (DS_OBJ_TYP_ID = 64)) AS Tables,
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS O
WHERE (DS_ID = D.DS_ID) AND (DS_OBJ_TYP_ID = 72)) AS Columns,
(SELECT COUNT(P.DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS P INNER JOIN
dbo.DS_OBJ_TYP AS U ON P.DS_OBJ_TYP_ID = U.DS_OBJ_TYP_ID
WHERE (P.DS_ID = D.DS_ID) AND (U.DS_OBJ_TYP_DESC = 'INDEX')) AS Indexes,
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS Q
WHERE (DS_ID = D.DS_ID) AND (DS_OBJ_TYP_ID = 70)) AS Views,
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS R
WHERE (DS_ID = D.DS_ID) AND (DS_OBJ_TYP_ID = 47)) AS [Materialized Views],
(SELECT COUNT(DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS S
WHERE (DS_ID = D.DS_ID) AND (DS_OBJ_TYP_ID = 69)) AS [Triggers ],
(SELECT COUNT(T.DS_OBJ_ID) AS Expr1
FROM dbo.DS_OBJ AS T INNER JOIN
dbo.DS_OBJ_TYP AS V ON T.DS_OBJ_TYP_ID = V.DS_OBJ_TYP_ID
WHERE (T.DS_ID = D.DS_ID) AND (V.DS_OBJ_TYP_DESC 'INDEX') AND (T.DS_OBJ_TYP_ID NOT IN (64, 72, 70, 47, 69))) AS [Other Objects],
A.AIT_NUM AS [Asset Tracked App Owner AIT], CAST(A.AIT_NUM AS varchar(10))
+ ' - ' + A.APP_SYS_SHRT_NM + ' - ' + A.APP_SYS_NM AS [Asset Tracked App Owner Name],
A.AIT_APP_MNGR_FIRST_NM + A.AIT_APP_MNGR_LAST_NM AS [Asset Tracked App Owner Mgmt Contact],
A.APP_MGR_NETID AS [Asset Tracked App Owner Mgmt NBID],
A.AIT_APP_TCHNCL_CNTCT_FIRST_NM + A.AIT_APP_TCHNCL_CNTCT_LAST_NM AS [Asset Tracked App Owner Tech Contact],
A.AIT_APP_TCHNCL_MGR_NB_ID AS [Asset Tracked App Owner Tech NBID], A.AIT_10_DOT_HRCHY AS [Asset Tracked App Owner Ten Dot],
A.AIT_10_DOT_HRCHY_DSC AS [Asset Tracked App Owner Ten Dot Org Name], A.AIT_3_DOT_HRCHY AS [Asset Tracked App Owner Three Dot],
A.AIT_3_DOT_HRCHY_DSC AS [Asset Tracked App Owner Three Dot Org Name], A.AIT_2_DOT_HRCHY AS [Asset Tracked App Owner Two Dot],
A.AIT_2_DOT_HRCHY_DSC AS [Asset Tracked App Owner Two Dot Org Name], CASE WHEN PRMRY_DS_IND 'P' THEN NULL
ELSE A.AIT_NUM END AS [Primary App AIT], CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE (CAST(A.AIT_NUM AS varchar(10))
+ ' - ' + A.APP_SYS_SHRT_NM + ' - ' + A.APP_SYS_NM) END AS [Primary App Name], CASE WHEN PRMRY_DS_IND 'P' THEN NULL
ELSE AST.APP_SYS_STATUS_DESC END AS [Primary App Status], CASE WHEN PRMRY_DS_IND 'P' THEN NULL
ELSE A.AIT_APP_MNGR_FIRST_NM + A.AIT_APP_MNGR_LAST_NM END AS [Primary AIT Mgmt Contact],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.APP_MGR_NETID END AS [Primary AIT Mgmt Contact NBID],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL
ELSE A.AIT_APP_TCHNCL_CNTCT_FIRST_NM + A.AIT_APP_TCHNCL_CNTCT_LAST_NM END AS [Primary AIT Tech Contact],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_APP_TCHNCL_MGR_NB_ID END AS [Primary AIT Tech NBID],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_10_DOT_HRCHY END AS [Primary AIT Ten Dot],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_10_DOT_HRCHY_DSC END AS [Primary AIT Ten Dot Org Name],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_3_DOT_HRCHY END AS [Primary AIT Three Dot],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_3_DOT_HRCHY_DSC END AS [Primary AIT Three Dot Org Name],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_2_DOT_HRCHY END AS [Primary AIT Two Dot],
CASE WHEN PRMRY_DS_IND 'P' THEN NULL ELSE A.AIT_2_DOT_HRCHY_DSC END AS [Primary AIT Two Dot Org Name],
HMR.DOMAIN AS Domain
FROM dbo.APP_SYS AS A INNER JOIN
dbo.APP_SYS_STATUS AS AST ON A.APP_SYS_STATUS_ID = AST.APP_SYS_STATUS_ID INNER JOIN
dbo.MD_RSRC AS MR1 ON A.APP_SYS_ID = MR1.APP_SYS_ID INNER JOIN
dbo.MD_RSRC_ASSOC AS MA ON MR1.MD_RSRC_ID = MA.MD_RSRC_ID INNER JOIN
dbo.MD_RSRC AS MR2 ON MA.ASSOC_MD_RSRC_ID = MR2.MD_RSRC_ID INNER JOIN
dbo.DS AS D ON MR2.DS_ID = D.DS_ID INNER JOIN
dbo.INSTNC AS I ON D.DB_INSTNC_ID = I.DB_INSTNC_ID INNER JOIN
dbo.MD_RSRC AS MR3 ON I.DB_INSTNC_ID = MR3.DB_INSTNC_ID INNER JOIN
dbo.DVC_MD_RSRC AS MDR ON MR3.MD_RSRC_ID = MDR.MD_RSRC_ID INNER JOIN
dbo.DVC AS DV ON MDR.DVC_ID = DV.DVC_ID INNER JOIN
dbo.DP_PLATFORM AS PL ON PL.PLTFRM_ID = I.PLTFRM_ID INNER JOIN
dbo.DVC_ENVRMNT AS DE ON DV.DVC_ID = DE.DVC_ID INNER JOIN
dbo.ENVRMNT AS E ON DE.ENVRMNT_ID = E.ENVRMNT_ID
INNER JOIN dbo.HRCHY_MSTR HMR
ON A.AIT_3_DOT_HRCHY = HMR.AIT_3_DOT_HRCHY
WHERE (DE.SYS_SRC_ID IS NULL) OR
(DE.DVC_ID IS NULL) OR
(DE.SYS_SRC_ID =
(SELECT MIN(SYS_SRC_ID) AS MIN_ID
FROM dbo.DVC_ENVRMNT
WHERE (DVC_ID = DE.DVC_ID)))
May 4, 2009 at 1:33 pm
The indexing of a view asside, there are several other options that you might want to investigate:
1. Add a IsCurrent flag to your table.
2. Just create the view and foget about indexing it, SQL will still use the index on the underlying tables if it can.
3. Change you database so that you have a Customer table and a CustomerHistory. That way the Customer table is just the Current record and the history table is the Current and Historical records.
May 4, 2009 at 2:37 pm
Balaji (5/4/2009)
Hi, I couldn't able to create clustered unique index on view. The view using lots of derived table and self join.Could you please help me out how to restructure view to create an index.
below is the query...
I have reformatted the view to make it more readable. You have quite a few subqueries in your SELECT that really could be converted to a inner join in the FROM clause, as well as the one in the WHERE clause as well. This could, with a bit of effort be converted to a GROUP BY query that you may not need to use an INDEXED VIEW.
I don't have time right now, but I may this evening. What would help is the DDL for the tables, some sample data (in an easily consumable format), and the expected results based on the sample data.
If you need assistance with creating the above info, please read the first article I have referenced below in my signature block.
Also, I will need you to verify what version of SQL Server you are running (2000/2005/2008?).
May 7, 2009 at 12:03 am
Hi Lynn,
We are using sql server 2005 version.
Please find the attached query and sample data
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply