Index on View

Hi

I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.

CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,

FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))

I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]

but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.

I think it is because of the MAX statement but don't know of any other way to do it. :confused:Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?

Have you tried creating a non-unique index on the column?|||Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.

I have tried creating a non-unique one but get the following error :
Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.|||Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?|||A nonclustered gives me the following error:
Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.

My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?

Index on 4 columns does not return results in expected order

We have a Full Text Index on 4 columns and it doesn’t return the results in
the order we think it should in 2000 or 2005. In 2005 I created a persisted
calculated column of these 4 columns and created the Full Text Index on it
and I returned the results in the order I expected them. In my mind both of
these methods should return the results in the same order. If not then I
don’t see how anyone could use Full Text Indexes on multiple columns.
I'm I doing something wrong or is this just the way it is?
Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:

> We have a Full Text Index on 4 columns and it doesnt return the results
> in the order we think it should in 2000 or 2005. In 2005 I created a
> persisted calculated column of these 4 columns and created the Full Text
> Index on it and I returned the results in the order I expected them. In
> my mind both of these methods should return the results in the same order.
> If not then I dont see how anyone could use Full Text Indexes on multiple
> columns.
> I'm I doing something wrong or is this just the way it is?
>
Are you using the RANK value to sort them? If you have no ORDER BY clause in
your query, the row ordering is always indeterminate. If you use an ORDER BY
clause you should always get them in the same order (unless use RANK to
order them and the RANK calculations are different in 2000 and 2005, Hilary
would probably be able to tell you if this is the case).
Dan
|||I am ordering by Rank. I was trying to ask the question in a generic way but
maybe it would be better to see the code.
I’ve done some testing with FT Search and I’m not sure if it works like I
think it should. In my mind these two Indexes should return the same results
for the table below:
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(cFirstName,cLastName,cTitle,vcEmailAddr)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
CREATE FULLTEXT INDEX ON dbo.wrkTestFTIndex
(ccFullName)
KEY INDEX PK_wrkTestFTIndex
ON [TestFCCatalog]
The FT Index on ccFullName the persisted calculated column seems to return
better results than the other one (example below: Gary Walker is returned
with the highest ranking). Am I doing something wrong querying the other
index?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wrkTestFTIndex](
[intFaclNbr] [int] NOT NULL,
[cFirstName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cLastName] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cTitle] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vcEmailAddr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCidentity] [int] IDENTITY(1,1) NOT NULL,
[ccFullName] AS ((((((rtrim(isnull([cFirstname],''))+'
')+rtrim(isnull([cLastName],'')))+' ')+rtrim(isnull([cTitle],'')))+'
')+rtrim(isnull([vcEmailAddr],''))) PERSISTED,
CONSTRAINT [PK_wrkTestFTIndex] PRIMARY KEY CLUSTERED
( [FCidentity] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
CREATE FULLTEXT CATALOG [TestFCCatalog]
ON FILEGROUP [FTIndex]
IN PATH 'd:\sql2000ftindex'
AS DEFAULT
Select Rank,fc.* from wrkTestFTIndex fc inner join
FREETEXTTABLE(wrkTestFTIndex, *,'Gary Walker') AS KEY_TBL
ON fc.FCidentity = KEY_TBL.[KEY]
order by Rank DESC
"Daniel Crichton" wrote:

> Kenny wrote on Thu, 8 Feb 2007 09:14:04 -0800:
>
> Are you using the RANK value to sort them? If you have no ORDER BY clause in
> your query, the row ordering is always indeterminate. If you use an ORDER BY
> clause you should always get them in the same order (unless use RANK to
> order them and the RANK calculations are different in 2000 and 2005, Hilary
> would probably be able to tell you if this is the case).
> Dan
>
>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks Richard
No idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>

index error?

Hi,
We had a query return the index entry error below.
Could not find the index entry for RID '36020100000200000100100007410400' in
index page (1:2223497), index ID 0, database 'dpSUM001'..
I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
planned to run a rebuild index on the table later that night to fix the
problem. Later in the afternoon I ran the query again and this time it
returned data and didn't have the index entry error. I ran the DBCC
CHECKTABLE and it reported no errors.
Does anyone know what fixed the index errors? The database has the auto
update/create stats and torn page detection turned on. Could one of these
option fixed the errors?
Thanks RichardNo idea. Thats some thing new and good for you. But from your side make sure
that you execute DBCC CHECKDB regularly and ensure that database
is in good shape. One more thing is talk to your hardware (DISK) vendor and
do some checks in DISK side during non peak time.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:789E3859-17AA-464A-B4FF-C30FDD6C3522@.microsoft.com...
> Hi,
> We had a query return the index entry error below.
> Could not find the index entry for RID '36020100000200000100100007410400'
> in
> index page (1:2223497), index ID 0, database 'dpSUM001'..
> I ran the DBCC CHECKTABLE ('bill_heading') and it reported 2 errors. I had
> planned to run a rebuild index on the table later that night to fix the
> problem. Later in the afternoon I ran the query again and this time it
> returned data and didn't have the index entry error. I ran the DBCC
> CHECKTABLE and it reported no errors.
> Does anyone know what fixed the index errors? The database has the auto
> update/create stats and torn page detection turned on. Could one of these
> option fixed the errors?
> Thanks Richard
>