Need MAX() equivalent to create indexed view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need MAX() equivalent to create indexed view

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-06-26 : 10:48:45

I have an issue where our customer service reps routinely look up a customer's information on our OLTP system. I want to create an indexed view.

The issue is that when a customer logs on to our website, his information is never updated. Instead, a new row is inserted in one of the customer information tables. For example, if a customer updates his address, a new row is inserted in the Address table. The same for his email. Once the new row is updated, the CustProfile table, which is simply a table containing foreign keys to the customer information, has a new row inserted with the most current customer information table row id's. For example, if the customer has never updated his data, the CustProfile table would contain something like this:

CustProfileId____CustId___CustFname____CustLname____Addr_____Email
____1____________1_________1____________1_________1_________1

Where CustId is the customer's account number and CustProfileId is the primary key on the CustProfile table.

If the customer were to go in and update his address, the customer's data would then look like this:

CustProfileId____CustId___CustFname____CustLname____Addr_____Email
____1____________1_________1____________1_________1_________1
____2____________1_________1____________1_________2_________1

The numbers wouldn't be consecutive with real data, of course. My problem is I want to pull all of the customer's information, as identified by the most current row of the CustProfile table, into an indexed view in order to have it available for the customer service reps as quickly as possible. The problem I am running into is that an indexed view cannot use the MAX() aggregate function in its definition, so I am not able to simply create a view of the CustProfile table with only the maximum CustProfileId's as grouped by CustId:

CREATE VIEW [elaapp_ds].[vwMaxCustProfile]
WITH SCHEMABINDING
AS
SELECT MAX([cp].[custprofileId]) AS custProfileId
,[cp].[custId]
FROM [elaapp].[CustProfile] AS cp
GROUP BY [cp].[custId]
GO

I can't use it to create the main indexed view, either - the one where I gather the actual information:

CREATE VIEW [elaapp_ds].[vwGetAcctSearchDTO]
WITH SCHEMABINDING
AS
SELECT
[cp].[custProfileId] AS [CustProfileId]
,[c].[CustId] AS [CustId]
,[c].[userid] AS [UserId]
,[ba].[bankAcctNum] AS [BankAccountNumber]
,[ba].[bankRtgNum] AS [BankRoutingNumber]
,[c].[firstNm] AS [FirstName]
,[c].[lastNm] AS [LastName]
,[a].[state] AS [State]
,[e].[emailAddr] AS [Email]
,[a].[zipcode] AS [Zip]
,[c].[ssn] AS [SSN]
,[c].[dob] AS [BirthDate]
,[c].[creationDt] AS [DateCreated]
FROM [elaapp_ds].[vwMaxCustProfile] AS [maxcp]
JOIN [elaapp].[CustProfile] AS cp
ON [maxcp].[custProfileId] = [cp].[custProfileId]
JOIN [elaapp].[Cust] AS [c]
ON [c].[custId] = [cp].[custId]
JOIN [elaapp].[Email] AS [e]
ON [cp].[emailId] = [e].[emailId]
JOIN [elaapp].[Addr] AS [a]
ON [cp].[currAddrId] = [a].[addrId]
JOIN [elaapp].[BankAcct] AS [ba]
ON [cp].[bankAcctId] = [ba].[bankAcctId]
GO
CREATE UNIQUE CLUSTERED INDEX [ix_elaapp_ds_vwGetAcctSearchDTO_CustProfileId]
ON [elaapp_ds].[vwGetAcctSearchDTO]
([CustProfileId],[CustId])

When I do the above, I get the following error:
Msg 1937, Level 16, State 1, Line 1
Cannot create index on view 'RoadRunner.elaapp_ds.vwGetAcctSearchDTO' because it references another view 'elaapp_ds.vwMaxCustProfile'. Consider expanding referenced view's definition by hand in indexed view definition.

So, since I can't use MAX(), and I can't reference another view, does anyone have a clever query that simulates the MAX() function?

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-26 : 11:31:06

top 1 order by maybe?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-06-26 : 12:15:50

quote:
Originally posted by DonAtWork

top 1 order by maybe?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Tried it. You have to use ROW_NUMBER() OVER (PARTITION BY) to get all of the rows you need, at which point it blows up and tells me it cannot create an index on the view because it references a derived table.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources