How to Create an indexed view in SQL Server

Question

I'm trying to build an indexed view but returning a duplicate key error, any advice on what to do? I can't use the original table's primary key as the view is being grouped.

Many thanks,

Jonathan

USE [IHG_MST]
GO    

SET NUMERIC_ROUNDABORT OFF
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE VIEW [dbo].[bvw_Grouped_Delphi_optimised] 
WITH SCHEMABINDING
AS
  SELECT       
   count_big(*) as tmp,
   SUM(isnull(id, 0)) as ID, 
   Holidex_Code, Export_Date, Account, PostAs, 
   StatusText, BookedByID, BookedByFullName, 
   EventStartDate, PropertyPID, PropertyName, 
   MtgClassName, BookingMarketSegment, 
   SUM(isnull(TotalFunctionRevenue, 0)) AS TotalFunctionRevenue, 
   SUM(isnull(FoodRevenue, 0)) AS FoodRevenue, 
   SUM(isnull(BevRevenue, 0)) AS BevRevenue, 
   SUM(isnull(RentalRevenue, 0)) AS RentalRevenue, 
   SUM(isnull(ResourceRevenue, 0)) AS ResourceRevenue, 
   SUM(isnull(AgreedRooms, 0)) AS AgreedRooms, 
   SUM(isnull(TotalRevenue, 0)) AS TotalRevenue, 
   SUM(isnull(CurrentRooms, 0)) AS CurrentRooms, 
   SUM(isnull(ExpectedAttendance,0)) AS ExpectedAttendance, 
   SUM(isnull(TotalGuestroomRevenue,0)) AS TotalGuestroomRevenue, 
   CreatedDate, LeadSource, LostReason, EventType, 
   FunctionRoomName, ReportGrouping, BookingID, ExtractDate, 
   BookingAbbrev, Uploaded_By, Uploaded_On
FROM
   dbo.MST_Delphi_Bookings
WHERE        
   (Holidex_Code IS NOT NULL) 
   AND (ReportGrouping <> 'Booking') 
   AND (LostReason <> 'Operator Entry Error') 
   AND (LostReason <> 'test call')
GROUP BY 
   Holidex_Code, Export_Date, Account, PostAs, StatusText, BookedByID, 
   BookedByFullName, EventStartDate, PropertyPID, PropertyName, MtgClassName, 
   BookingMarketSegment, CreatedDate, LeadSource, LostReason, EventType, 
   FunctionRoomName, ReportGrouping, BookingID, ExtractDate, BookingAbbrev, 
   Uploaded_By, Uploaded_On
GO


CREATE UNIQUE CLUSTERED INDEX IX_Delphi_Holidex_Code 
ON [dbo].[bvw_Grouped_Delphi_optimised](Holidex_Code, Export_Date)
GO

Error:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.bvw_Grouped_Delphi_optimised' and the index name 'IX_Delphi_Holidex_Code'. The duplicate key value is (ASDKE, 2014-03-24).

@usr:

CREATE UNIQUE CLUSTERED INDEX IX_Delphi_Holidex_Code 
ON [dbo].[bvw_Grouped_Delphi_optimised](Holidex_Code, Export_Date, Account, PostAs, 
   StatusText, BookedByID, BookedByFullName, 
   EventStartDate, PropertyPID, PropertyName, 
   MtgClassName, BookingMarketSegment, 
   SUM(isnull(TotalFunctionRevenue, 0)) AS TotalFunctionRevenue, 
   SUM(isnull(FoodRevenue, 0)) AS FoodRevenue, 
   SUM(isnull(BevRevenue, 0)) AS BevRevenue, 
   SUM(isnull(RentalRevenue, 0)) AS RentalRevenue, 
   SUM(isnull(ResourceRevenue, 0)) AS ResourceRevenue, 
   SUM(isnull(AgreedRooms, 0)) AS AgreedRooms, 
   SUM(isnull(TotalRevenue, 0)) AS TotalRevenue, 
   SUM(isnull(CurrentRooms, 0)) AS CurrentRooms, 
   SUM(isnull(ExpectedAttendance,0)) AS ExpectedAttendance, 
   SUM(isnull(TotalGuestroomRevenue,0)) AS TotalGuestroomRevenue, 
   CreatedDate, LeadSource, LostReason, EventType, 
   FunctionRoomName, ReportGrouping, BookingID, ExtractDate, 
   BookingAbbrev, Uploaded_By, Uploaded_On)
GO

Unique key can be created only when no duplicate records exists for these two columns (Holidex_Code and Export_Date). — Bhasyakarulu Kottakota, Jul 24 '14 at 19:01
Yes, Holidex_Code and Export_Dates are not unique, hence duplicated. But same as all any other fields because I'm grouping the table... I might have done it wrong as being the first time building indexed view, what is the standard way of creating grouped indexed view? — user3840039, Jul 24 '14 at 19:03

score 3 · Accepted Answer · answered Jul 24 '14 at 19:07

3

You must create a unique clustered index. The docs clearly state that there is no way around it.

If your data is not unique on any key this is usually a data-quality issue, a conceptual mistake an outright bug. I therefore advise you to reevaluate this design.

If you insist on doing it like this you must invent/concoct some unique key. Fortunately, all queries with a group-by have such a key: the grouping columns. Add those columns to the view and create the index on them.

This is logically the primary key of your view. Grouping on something results in that "something" being unique in the output.

answered Jul 24 '14 at 19:07

usr

168,620
35
240
369

HI usr, thanks for reply, do you mean adding all grouping columns into the index? In that case will there be any side effect on performance? Thanks. – user3840039 Jul 24 '14 at 19:14
There will be no effect on performance because there is no alternative. Ask more specifically. – usr Jul 24 '14 at 19:16
My apologies as I don't know enough to raise quality questions. But is this what I'll need to do to get the uniqueness for index? I asked about side effect as I heard too many index will increase search time. Please see edited question for updated code. Thanks. – user3840039 Jul 24 '14 at 19:25
Well, your goal is to create an index on the view so you have to live with at least one index to reach your goal. You seem to be interested in `Holidex_Code, Export_Date` being the leading columns. You can do that and it will make the index seekable on those columns. If that is the only access pattern you need then a single index is enough and a single index is optimal. – usr Jul 24 '14 at 19:29
Knowing that single index is optimal and not being able to find such an index due to grouping, my question is does including everything grouping column in my clustered index make sense? Thanks. I think I'm knowing a bit more now. – user3840039 Jul 24 '14 at 19:39
1

Yes, that makes sense because it is the only possible way. – usr Jul 24 '14 at 19:46
1

You can have multiple indexes on an indexed view. So if the unique primary key index doesn't give what you need, you can always create additional non-unique indexes. Please note that if you have queries or other views that reference this indexed view, you should use the NOEXPAND hint to gain maximum efficiency from your defined indexes. – pmbAustin Jul 24 '14 at 21:25
I'm inlcuding all columns that I used in group in to create unique records, but then getting error of: max limit for index key column list is 16, and if I cut it down it gives other: Column 'Account' in the table '(the view)' is of a type that is invalid for use as a key column – user3840039 Jul 25 '14 at 13:03
My key question is still that I'm quite sure it's a standard for people to group a large datatable using view, and when they do, what's the proper procedure to prevent long execution time? If by view, what's the proper way of doing it? Hope someone can address these questions in particular. Thanks! – user3840039 Jul 25 '14 at 13:05
"long execution time" for what query? The index build? Queries on the view? DML on the base table? You are really unclear here. Maybe you should look into how indexed views are implemented. Inspect all relevant execution plans. – usr Jul 25 '14 at 14:01
Regarding those errors: Yes, if you have more than 16 columns as keys you need another workaround: Concatenate some columns into one. That's awful of course but it is the only way. If you do not have a candidate key in that view you can't use indexed views. I don't know how to make this point any clearer. – usr Jul 25 '14 at 14:02

How to Create an indexed view in SQL Server

1 Answers1