Materialized view -- sql server programming

Thomas
3/11/2004 11:41:14 PM

I'm trying to understand materialized view and how it can improve
performance.

I have the impression that the result of the expression:

RTRIM(LTRIM(UPPER(REPLACE(REPLACE(REPLACE([Name],SPACE(1),''),'.',''),CHAR(3
9),''))))

is stored in the view, and when searching on the view, the expression will
not be calculated - instead the result will be used.

Here is an example:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

-- Create the view
CREATE VIEW dbo.Names WITH SCHEMABINDING
AS
SELECT ID, CorporationID,
RTRIM(LTRIM(UPPER(REPLACE(REPLACE(REPLACE([Name],SPACE(1),''),'.',''),CHAR(3
9),'')))) AS CorpName
FROM dbo.Corporations

-- Create the materialized view clustered index - I think this is an
requirement - but I don't really need it
CREATE UNIQUE CLUSTERED INDEX IX_ID
ON Names (ID)

-- Create the materialized view index that is being searched for
CREATE INDEX IX_CorpName
ON Names (CorpName)

-- Is this using the Materialized view - or is
RTRIM(LTRIM(UPPER(REPLACE(REPLACE(REPLACE([Name],SPACE(1),''),'.',''),CHAR(3
9),'')))) run for every row in Corporations table?
SELECT *
FROM Names
WHERE CorpName = 'THEFACTORYCOMPANY'

Thomas

Steve Kass
3/11/2004 11:58:48 PM

Thomas,

The index will be used for searching, and the materialized CorpName
column will be retrieved, not recalculated, if you use the NOEXPAND hint
(possibly not needed in Enterprise edition, but it shouldn't hurt to
include it):

SELECT *
FROM Names with (noexpand)
WHERE CorpName = 'THEFACTORYCOMPANY'

You can see this by looking at the estimated execution plan in Query
Analyzer.

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Materialized view