I am issues with using indexed views. I have a product catalog and a stored proc reads the data in the rows according to various filters on the data. At the same time, the data needs to remain in XML because of a CMS application that runs on top of it. The data in this table has low DML activity, updates/inserts only happen when we get a new item in or need to add or remove quantity.

In my code below, which is somewhat redacted for brevity and privacy, I the promote the meta_detail XML into various persisted columns for fast searching. I wanted to use an indexed view because of extensive joins (original has 4 joins or meta data tables), and being able to compute data from promoted columns and source columns.

My issue is that when I read from the view I create, it does not seem to be materialized because it does not take the persisted values and save them, it seems to be loading for a good 6 minutes, where as if I select from the table with no calculations, I get the results back in .05 seconds for 3000 rows.

What should I be doing differently?

As a stop gap, I have resorted to dumping the table into another "buffer" table every day at a certain low activity time, and pushing updates to the financial detail quantity/price every time to BOTH tables to keep them in synch. The search procedure uses the buffer table. Of course working with one table is better than two....so? Also with that said, I had to create a mechanism for the front-end to know the results are being updated for those 4am shoppers, so they could check back in a couple of minutes.


    -- function
    CREATE FUNCTION dbo.fn_compute_inventory_describe(@item int, @meta xml) RETURNS varchar(MAX) WITH SCHEMABINDING 
    DECLARE @name varchar(MAX) = ''
    DECLARE @brand varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/brand/value)[1]', 'varchar(MAX)'), 'None'),''), 
            @sub varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/sub_brand/value)[1]', 'varchar(MAX)'), 'None'),''), 
            @product varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/product/value)[1]', 'varchar(MAX)'), 'None'),''),
            @description varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/categorization/search/description/value)[1]', 'varchar(MAX)'), 'None'),'No Details Available')

    IF (@item = 1)
        SELECT  @name = REPLACE(@brand + ' ' + @sub + ' ' + @product, '  ', ' ')    
    ELSE IF (@item = 2)
        SELECT  @name = REPLACE(@brand + ' ' + @sub + ' ' + @product, '  ', ' ')
        IF (LEN(@name) > 75) SELECT @name = REPLACE(@sub + ' ' + @product, '  ', ' ')
        IF (LEN(@name) > 75) SELECT @name = REPLACE(@product, '  ', ' ')            
    ELSE IF (@item = 3)
        SELECT  @name = REPLACE(@description, '  ', ' ')            
    RETURN @name
    -- table
    CREATE TABLE [dbo].[tbl_products]
        [uiid]  [int] NOT NULL IDENTITY(1,1),
        [ukey]  [varchar](11) NULL,
        [meta_type] [int] NOT NULL DEFAULT 17,
        [pname] AS ([dbo].[fn_compute_inventory_describe](1,meta_detail)) PERSISTED,
        [publish] AS ([dbo].[fn_compute_inventory_describe](2,meta_detail)) PERSISTED,
        [pdescription] AS ([dbo].[fn_compute_inventory_describe](3,meta_detail)) PERSISTED, 
        [cOnHand] AS ([dbo].[fn_compute_inventory_quantity](1, meta_detail)) PERSISTED,         
        [cDistressed] AS ([dbo].[fn_compute_inventory_quantity](5, meta_detail)) PERSISTED,
        [cOpenBox] AS ([dbo].[fn_compute_inventory_quantity](6, meta_detail)) PERSISTED,
        [meta_detail] [xml] (DOCUMENT [dbo].[xmls_inventory_metadata]) NOT NULL DEFAULT 
        N'<?xml version="1.0"?>
        </metadata>', -- parts redacted for breavity/source control
        CONSTRAINT pk_inventory_base PRIMARY KEY (uiid),
        CONSTRAINT fk_inventory_base_type FOREIGN KEY (meta_type) REFERENCES dbo.tbl_types(refid),  
    -- view
        SELECT  uiid AS pid, ukey AS pkey, pname, pdescription,
                t.refid AS typeid, t.shortname AS typeurl, 
                t.display AS typedisplay,
                cOnHand, cDistressed, cOpenBox,
                (cOnHand) AS cQTY9,
                (cDistressed + cOpenBox) AS cQTY0
        FROM    dbo.tbl_products p INNER JOIN
                dbo.tbl_types t ON p.meta_type = t.refid
    -- index
    CREATE UNIQUE CLUSTERED INDEX un_products_view ON [dbo].[vw_products] (pid) WITH (IGNORE_DUP_KEY = ON)
  • It should be pretty easy to see what object you are querying from. What does the output from SET STATISTICS IO ON say? I see you creating a function, but I don't see the function being used anywhere in the view, is it?
    – mrdenny
    Aug 14, 2012 at 17:19
  • It is used in the computed columns in tbl_products table
    – Alex Erwin
    Aug 15, 2012 at 8:49
  • Ah, there it is. What's the output from Statistics IO say? What's the wait type reported when you from the query the first time?
    – mrdenny
    Aug 15, 2012 at 22:27
  • What SQL Edition are you using?
    – Magier
    Aug 19, 2015 at 15:49

1 Answer 1


If you are using SQL Standard Edition, you have to use the WITH (NOEXPAND) table hint. If you are using Enterprise Edition, you might want to use that hint anyway, to get statistics on the view, and to prevent the optimizer choosing not to use it (see the second link below).

You may also want to assess the benefits of adding other nonclustered indexes to your view.

Read more:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.