Archive for July, 2010

Refeshing Views

Running on a recently updated to SQL Server 2005 cluster, we were noticing some performance issues with one stored procedure in particular. We initially blamed the issue on optimizer changes, but were looking a fix that did not involve and object change.

Someone came up with the idea (not me) to refresh the view. I am aware that a view that contains the dreaded “SELECT *” may not contain new table columns until the view is refreshed, but I have never actually run into this issue in real life. In our situation the view definition had not changed, nor had the underlying objects, but refreshing the view resolved the performance issues.

So on that note i wrote a quick little cursor up refresh all views in a database. I hope someone finds this useful.

/*******************************************************************************
Name:			Refresh Views
Description:	Declare our variables and cursor. The cursor looks at and 
                schema bound views since the are not able to be refreshed.
Dependencies:	
Testing:		
********************************************************************************
Author - Date - Change Description
--------------------------------------------------------------------------------
Ron Carpenter - 07/01/2010 - Initial Version
*******************************************************************************/
DECLARE @View varchar(255) ;

DECLARE ViewCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY
    FOR SELECT
            [Name]
        FROM
            sys.objects o
        WHERE
            Type_Desc = 'VIEW' AND
            NOT EXISTS (SELECT
                            1
                        FROM
                            sys.sql_dependencies d
                        WHERE
                            o.object_id = d.object_id AND
                            d.class = 1)

OPEN ViewCursor ;

FETCH NEXT FROM ViewCursor INTO @View ;
WHILE @@FETCH_STATUS = 0 
    BEGIN
        EXECUTE sp_refreshview @View ;

        FETCH NEXT FROM ViewCursor INTO @View ;
    END ;

CLOSE ViewCursor ;
DEALLOCATE ViewCursor ;
Advertisements