April 12, 2011 at 12:19 pm
I have a view inner joined 3 tables with no aggregate in it. The view is used for search by lastname and/or firstname in my application. I'm thinking to create an indexed view to improve searching performance (The size of the view is 600,000 records). However, the lastname, firstname, and combination are not unique so I know I cannot create clustered index). Do I gain any performance by creating non-clustered indexed view? If I do, how do I create the index for the view (The index should be lastname, firstname and/or combined name)?
I'm a ASP.NET developer and am not very familiar with SQL Server. Please correct me if I'm wrong. Thanks!
April 12, 2011 at 1:13 pm
Hi,
if you want to create an indexed view you must create the view with the option SCHEMABINDING which ensure that the underlying table schema will not change.
CREATE VIEW v_MyViewName
WITH SCHEMABINDING
AS
<YOUR SQL CODE>
But your SQL Code mustn't contain following things and can only contain base tables not views:
- TOP
- LOB columns
- DISTINCT
- MIN, MAX, COUNT, STDEV, VARIANCE, AVG
- SUM on a nullable expression
- OUTER joins, Subqueries, or self-joins
- A derived table
- UNION
- ORDER BY
- COMPUTE, COMPUTE BY
- The ROWSET function
- Full-text predicates (CONTAINS, FREETEXT)
You can't create a non-clustered index before you haven't create an clustered index, so create a clustered index with the column combination that is most often used and/or is not changed so often. Afterwards you can create non-clustered indexes with other combinations.
But keep in mind that this indexed view will slow down your insert, update and delete statements of the underlying tables.
Greetings
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 12, 2011 at 1:16 pm
a clustered index does not have to be unique. it just stores the data in the order of the index for the fastest retrieval of the data.
you are confusing a default primary key and a clustered index, where usually the two are the same.
so you can still create a clustered index on a group of columns that are not unique.
Lowell
April 12, 2011 at 1:20 pm
One other note, you can't create a nonclustered materialized view. The very construct is always a cluster. No other way to do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 12, 2011 at 2:52 pm
Thanks!
If I create a non-clustered index for my view, will it improve the performance? To create index, do I create a index of combination of lastname and firstname or 2 indeices (one is firstname the other is firstname)? Which way is better? In my application, the user may type in lastname, firstname, or both to do wildcard search. Thanks again!
April 12, 2011 at 3:00 pm
Sorry I forgot to mention. Before my post, I tried to create a clustered index. I got the following error:
Cannot create nonunique clustered index on view 'dbo.v_Search' because only unique clustered indexes are allowed.
That's why I think I cannot create a clustered index on a non-unique field like last name for a view.
Thanks!
April 12, 2011 at 3:22 pm
Lowell (4/12/2011)
a clustered index does not have to be unique. it just stores the data in the order of the index for the fastest retrieval of the data.
However the first index on a view does have to be a unique clustered index. After that, non-unique, nonclustered indexes can be created
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply