Index on a View

Question

Index on a View

sudhanva

Ten Centuries

Points: 1343
More actions
August 21, 2009 at 9:10 am

#134385

Hi,
Background
I have two tables with varchar(max) column, and those tables contains lakhs of rows. My search query uses those tables. LIKE consumes lot of time, so i have used full text Index[/url] on those tables. To further increase the performance, i thought of having the query in a VIEW, and create an index on it.
And then creating a Full Text Index on the varchar(max) columns.
----------------------------------------------------------------------------------------
Query
-- create a new view with schemabinding
create view dbo.AIMDesc
with schemabinding
as
SELECT ROW_NUMBER() (ORDER BY dbo.Activity_Instance_MasterDetails.Activity_Inst_Id) AS 'Row Number',
dbo.Activity_Instance_Names.AID_Description AS JournalName,
dbo.Activity_Instance_Description.AID_Description AS Abstract
FROM
dbo.Activity_Instance_MasterDetails
INNER JOIN dbo.Column_Master ON dbo.Column_Master.Activity_id = dbo.Activity_Instance_MasterDetails.Activity_Id AND dbo.Column_Master.TxtSearch = 1
INNER JOIN dbo.Activity_Instance_Description ON dbo.Activity_Instance_MasterDetails.Activity_Inst_Id = dbo.Activity_Instance_Description.Activity_Inst_Id AND
dbo.Activity_Instance_Description.Column_Id = dbo.Column_Master.Column_id
INNER JOIN dbo.Activity_Instance_Names ON dbo.Activity_Instance_MasterDetails.Activity_Inst_Id = dbo.Activity_Instance_Names.Activity_Inst_Id
create unique clustered index AIMDesc_id on dbo.AIMDesc ([Row Number])
Problem
I have read that View requires unique column[/url], for creating a Index.
When i execute the above query im getting the following error.
Cannot create index on view "KOL_Reliance_Login_R.dbo.AIMDesc" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view.
Help me.
Thanks in advance.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

As the error says, an indexed view cannot contain a windowing function, ie the row_number column is not allowed. You need to pull a unique column in from one of the base tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudhanva Ten Centuries Points: 1343 More actions · Answer 2

Hi,

Thanks for the reply.

I cant pull out the unique row, since the data contains repeated rows.

I need something like rownumber(), to get a unique column on the VIEW.

I hope there is some solution with you experts?

Thanks & Regards,

Sudhanva.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Is there absolutely no column or set of columns in the underlying tables that are unique?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudhanva Ten Centuries Points: 1343 More actions · Answer 4

No hopes 🙁

Suppose, i have a composite key in the VIEW, but can i have a composite for creating FULL TEXT INDEX. No, i guess. I think, FTIndex requires a clustered index.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

If there's no single unique column in any of the base tables, and full text requires that there is one, then you're going to have to find another way that doesn't involve indexed views, maybe put the full text onto the base tables.

Can you add an identity column to one of the base tables?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sudhanva Ten Centuries Points: 1343 More actions · Answer 6

Yeah, currently i have Full Text Index on base tables.

But querying those tables everytime is taking time. So thought of having VIEW.

Any other work around?

Thanks & Regards,

Sudhanva